Post reply

Problem with DEFAULT in #TempTable

  • March 30, 2005 at 11:46 am

    #163874

    I have the following code. 

    DECLARE @time datetime

    SELECT @time = GETDATE()

    SELECT 12345 AS award_id, 0.95 AS sumpro, NULL AS srt1, NULL AS srt1nm, NULL AS srt2, NULL AS str2nm, NULL AS srt3, NULL AS srt3nm, @time AS time_run

    INTO #out

    CREATE TABLE #tb_rpt( award_id int,

     sumpro decimal(5,1),

     srt1 varchar(10) DEFAULT '',

     srt1nm varchar(10) DEFAULT '',

     srt2 varchar(10) DEFAULT '',

     srt2nm varchar(10) DEFAULT '',

     srt3 varchar(10) DEFAULT '',

     srt3nm varchar(10) DEFAULT '',

     time_run datetime)

    INSERT INTO #tb_rpt( award_id, sumpro, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run)

    SELECT award_id, sumpro, srt1, srt1nm,  ' ' AS srt2, ' ' AS srt2nm, '  ' AS srt3, '  ' AS srt3nm, @time AS time_run

    FROM #out

    SELECT * FROM #tb_rpt

    DROP TABLE #tb_rpt

    DROP TABLE #out

     

    When I run it, srt1 and srt1nm return as NULL, even though there is a DEFAULT specified for the #tb_rpt table.   

    Any ideas? 

    I wasn't born stupid - I had to study.

  • March 30, 2005 at 11:51 am

    #549613

    The default is only used when you don't specify a value.

  • March 30, 2005 at 12:07 pm

    #549615

    THANKS!  I guess since NULL is nothing (hence NULL cannot equal NULL), we assumed the DEFAULT would take precedence.  But, NULL is a legitimate "value" in SQL Server even if you don't want it... 

    I wasn't born stupid - I had to study.

  • March 30, 2005 at 12:13 pm

    #549617

    Exactly.

  • March 30, 2005 at 2:24 pm

    #549670

    In addition IF you don't want NULL to be added Why not...

    CREATE TABLE #TEMP

    (FieldX VARCHAR(10) NOT NULL DEFAULT(''))

    This way the program will scream if it gets a NULL

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • March 31, 2005 at 7:43 am

    #549819

    Hi

    In your example, you are forcing srt1 and srt1nm to be NULL

    SELECT 12345 AS award_id, 0.95 AS sumpro, NULL AS srt1, NULL AS srt1nm, NULL AS srt2, NULL AS str2nm, NULL AS srt3, NULL AS srt3nm, @time AS time_run

    INTO #out

    But if you don't force it and NULL values can come as valid values from Time_run, then you could avoid it, by using the ISNULL function

    INSERT INTO #tb_rpt( award_id, sumpro, srt1, srt1nm, srt2, srt2nm, srt3, srt3nm, time_run)

    SELECT award_id, sumpro, ISNULL(srt1, ''), ISNULL(srt1nm,''),  ' ' AS srt2, ' ' AS srt2nm, '  ' AS srt3, '  ' AS srt3nm, @time AS time_run

    FROM #out

    Then, you avoid null values replacing them with, in this example, '' or you can use the value that you need. Also you avoid that program screams. This ISNULL function is often used to make comparisons, because NULL = NULL is not always true, then you can compare ISNULL( value, 0) = 0. Rememnber that the replace value  has to be same type that the original value

    ( ISNULL( original_value, replace_value) )

     

    Salu2

    Nicolas Donadio

    SW Developer 

     

  • March 31, 2005 at 10:22 am

    #549884

    Thanks all!  This was an unusual circumstance in some of the standard code where I am contracted.  I'm a knucklehead for not seeing that directly - oil well, too close to the problem I guess... 

    We have ISNULL in a common SP called to deal with this issue...

    I wasn't born stupid - I had to study.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply