Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    8

    Unanswered: insert a default value if null parameter

    hi
    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.

    thanks

  2. #2
    Join Date
    Jun 2009
    Posts
    26
    Hi Waleed,
    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

  3. #3
    Join Date
    Jun 2009
    Posts
    8
    hi
    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
    thanks

  4. #4
    Join Date
    Jun 2009
    Posts
    8
    ok ,here the the code

    ----------------------------
    CREATE PROCEDURE [dbo].[sp_Table_1_Insert_WithDefaultValues]
    @c_varBin varbinary (50) = null ,
    @c_varBin_max image = null ,
    @c_smallInt smallint = null ,
    @CXml text = null ,
    @CImage image = null ,
    @CMoney float = null ,
    @c_tinyInt tinyint = null ,
    @CVarchar varchar (50) = null ,
    @CVarcharMax text = null ,
    @c_nvarChar varchar (50) = null ,
    @CNvarcharMax text = null ,
    @c_dateTime datetime = null ,
    @ErrorCode int OUTPUT

    AS

    SET NOCOUNT ON

    -- INSERT a new row in the table


    INSERT INTO [dbo].[Table_1]( [c_varBin],[c_varBin_max],[c_smallInt],[c_xml],[c_image],[c_money],[c_tinyInt],[c_varchar],[c_varchar_max],[c_nvarChar],[c_nvarchar_max],[c_dateTime] )
    VALUES ( isnull(convert(varchar(max),@c_varBin),'Default') ,isnull(convert(varchar(max),@c_varBin_max),'Defau lt') ,isnull(convert(varchar(max),@c_smallInt),'Default ') ,isnull(convert(varchar(max),@CXml),'Default') ,isnull(convert(image,@CImage),'DEFAULT') ,isnull(convert(varchar(max),@CMoney),'Default') ,isnull(convert(varchar(max),@c_tinyInt),'Default' ) ,isnull(convert(varchar(max),@CVarchar),'Default') ,isnull(convert(varchar(max),@CVarcharMax),'Defaul t') ,isnull(convert(varchar(max),@c_nvarChar),'Default ') ,isnull(convert(varchar(max),@CNvarcharMax),'Defau lt') ,isnull(convert(varchar(max),@c_dateTime),'Default ') ) ;


    -- Get the Error Code for the statment just executed
    SET @ErrorCode = @@ERROR


    GO
    ------------------------------------
    the lines of image ,and varbinary insertion make a problem

  5. #5
    Join Date
    Jun 2009
    Posts
    26
    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:
    isnull(@c_dateTime,'2009-01-01')
    This would replace NULL values of @c_dateTime with the 01/01/2009.

  6. #6
    Join Date
    Jun 2009
    Posts
    8
    hi
    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
    (
    column_name sysname,
    column_def nvarchar(4000)
    )

    insert into #temp SELECT column_name,column_default

    FROM INFORMATION_SCHEMA.COLUMNS
    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
    begin
    set @v = convert ( <MY COLUMN Type>,@t)
    end

    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?
    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •