December 8, 2008 at 4:35 pm
Let's say I have a table, named MyTable:
RecordID int NOT NULL IDENTITY(1,1),
Field1 varchar(50) NOT NULL DEFAULT 'foo'
I'd like to have a proc that accepts @Field1 varchar(50) NULL as input, and does the following:
INSERT INTO MyTable (Field1)
VALUES (CASE WHEN @Field1 IS NULL THEN DEFAULT ELSE @Field1 END)
The purpose of which is to allow the @Field1 parameter to be an optional parameter - if passed, Field1 in the inserted record is set to whatever is passed; and if not passed, Field1 in the inserted record is set to the default constraint value for Field1 (in this example, 'foo').
However, the above SQL will not work - the compiler apparently doesn't like the CASE statement when combined with the 'DEFAULT' keyword. I can run the following SQL just fine:
INSERT INTO MyTable (Field1)
VALUES DEFAULT
But of course then I couldn't use the @Field1 input parameter to make my proc more flexible.
Any thoughts?
December 8, 2008 at 4:51 pm
December 8, 2008 at 5:12 pm
Thanks for the quick reply, Seth! Unfortunately I wasn't as thorough as I should have been in my original post. Your suggestion is exactly what I originally had in mind, but what I want to avoid is to have the default values 'hard coded,' if you will, in the proc itself, and instead rely on retrieving the defaults from the constraints themselves (in my working environment there are a number of people who may go in and modify the default constraints, but will not be diligent enough to also modify any references to the old constraint values in the stored procs, functions, etc.). Makes for a thorny problem for me! Hope there is a way to do this.
December 8, 2008 at 5:24 pm
If the situation is really as simple as your example, you can always use an IF instead. (It's probably not, but I'll try the easy way out first).
IF @Field1 IS NULL
INSERT INTO MyTable(Field1)
VALUES DEFAULT
ELSE
INSERT INTO MyTable(Field1)
VALUES(@Field1)
December 8, 2008 at 5:29 pm
LOL...right you are...my example is indeed a bit oversimplified...there are probably about 10 to 20 fields in this one table that I need to apply this "technique" to (hopefully in a single SQL statement).
December 8, 2008 at 6:31 pm
suggestion: insert a row of default values and then update that row with the passed values.
create table #test ( k int identity(1,1), a int default 22, b varchar(99) default 'missing',
primary key (k) )
go
create proc uspTest( @a int, @b-2 varchar(99) )
as
begin
declare @k int
insert #test default values
set @k = scope_identity()
update #test set a = isnull(@a,a), b = isnull(@b,b)
where k = @k
end
go
exec uspTest 10, 'green'
exec uspTest null, 'red'
exec uspTest 20, null
exec uspTest null, null
select * from #test
go
drop procedure uspTest
go
December 8, 2008 at 10:06 pm
Thanks! I had a feeling that that would be the way it had to be done, but it's nice to hear it from someone with a lot more experience than me! Thanks again to all for the help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply