July 9, 2002 at 9:16 am
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.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
July 9, 2002 at 9:29 am
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
July 9, 2002 at 9:32 am
I saw the results you originally described i.e.
declare @s-2 varchar(100)
select @s-2 = 'zzz'
select @s-2 = [name] from sysobjects where 4=5
select @s-2
----------------------------------------------------------------------------------------------------
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
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.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply