Post reply

SELECT returns NULL in SP

  • July 9, 2002 at 9:16 am

    #79399

    I wrote a number of stored procedures in SQL 6.5 where I was selecting a single value from a table and updating a variable directly. First I set the variable to a value that could never be in the table.

    SELECT @var = 'ZZZ'

    Next I run the query to populate the variable. (Note: I only do this when I am retrieving one value or an aggregate value, such as a sum.)

    SELECT @var = field_value

    FROM table_name

    WHERE (some criteria)

    After I ran this query, I checked the value of @var. If it was still 'ZZZ', I know my query did not return a value.

    I am now in the process of moving a system to SQL 2000 and I was testing one of my stored procedures with this code and it failed. Now, when my query doesn't return a value, the value of @var is NULL.

    Does anyone know if this is a change MS made or am I doing this wrong anyway? I have this type of code in many places, so if I this is a change, or I am doing it wrong, I need to make changes to many of my SPs.

    Thanks in advance for all help.

    ----------------------------------------------------------

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

  • July 9, 2002 at 9:29 am

    #432260

    Ran this on SQL 2000

    declare @i char(3)

    select @i = 'ZZZ'

    select @i

    select @i = rack

    from racks

    where 1 = 0

    select @i

    I still get 'ZZZ' both selects. I suspect your data has changed. Can you run the select and see if you get an empty result set or a NULL?

    Steve Jones

    sjones@sqlservercentral.com

    http://qa.sqlservercentral.com/columnists/sjones

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 9, 2002 at 9:32 am

    #432261

    I saw the results you originally described i.e.

    declare @S varchar(100)

    select @S = 'zzz'

    select @S = [name] from sysobjects where 4=5

    select @S

    ----------------------------------------------------------------------------------------------------

    zzz

    (1 row(s) affected)

    This is on SQL2K standard edition (SP1).

    Maybe you could use the system variable @@rowcount to see if a select statement returns any results.

    Regards,

    Andy Jones

    .

  • July 9, 2002 at 9:41 am

    #432267

    OK, based on what both of you, Steve and Andy, said, I went back and ran some simple queries like you ran and I got the same results you got. This made me take another look at my query and there is one thing I overlooked. The query I was running was not looking for a single value, it was trying to return an aggregate value, such as:

    SELECT sum(volume)

    FROM table_name

    WHERE 1=0

    My apologies for not making that clear when I mentioned this but I overlooked it as a possibility when trying to figure out the problem. I just needed some fresh eyes.

    Anyway, I now see that when returning one value, the value of my variable will not change if a record is not found because the query returns an empty recordset. If I am trying to return the value of an aggregate function, I will get a NULL value returned.

    I will also look into using the @@ROWCOUNT option as well.

    Thanks for all your help.

    ----------------------------------------------------------

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

Viewing 4 posts - 1 through 3 (of 3 total)

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