Unanswered: insert a default value if null parameter
I want a sql query for a stored procedure that take 2 input paramters (image,and varbinary) and tests each of them to be null or not,
in case of being null I want to insert Default value for the column in DB table.
You'll probably find the IsNull function is what you're after - it tests if a value is null and if so replaces it with the value you specifiy. Check this link for more info on how to use it: SQL Tutorial: IsNull | Databases Made Easy
thanks for reply,there's a small problem ,I'll explain them in points in brief:
1-I'm working on a dynamic sql query generator that check table schema at runtime, (so I don't know how many columns in advance in the selected table or their types,or thier default values ,only at Runtime this inormation is fectched)
2-then sql queries are produced in SQL script file (.sql)
3-when called generated code ,not all fields are null ,some may be and some may not be.
I have tried some scripts but the produced code has some problem in varbinary and Image types , how to put the generated queryr into text file (.sql)
I hope I made a clear description of problem
OK there are are few problems here:
1. with the isnull you don't need to covert the field your testing. If it's NULL it's NULL
2. You've mixed up your data types. 'Default' is a char / varchar and you're trying to insert it into what looks like binary / integer / datetime fields.
As an example if you're looking to check if the datetime value is null you could use the function like this:
This would replace NULL values of @c_dateTime with the 01/01/2009.
I'll add extra script to get default value for each column first if the equivalent passed parameter is null ,something like that:
create table #temp
insert into #temp SELECT column_name,column_default
where table_Name ='table_1'
declare @t nvarchar(4000)
set @t=(select column_def from #temp where column_name='<my COLUMN name>')
select @t -- just for visualization
if @v =null --@v here is the passed parameter for stored procedure
set @v = convert ( <MY COLUMN Type>,@t)
select @v -- just for visualization
drop table #temp
it works for most cases (even in image) ,but when <MY COLUMN Type> is varbinary I notice some strange thing
when @t is 0x0343 ,@v has different value 0x28 !!!!
why is this mismatch in converted value in varbinary?