I came across a problem today whereby a stored procedure didn't behave as expected. I suppose when I think about it, it behaved as it should have but my expectation was lacking the thought mentioned before.
There's two different ways of assigning a variable to a value stored in a table, one is to use a subquery in a "set" statement and the other is a simple select, e.g.
Set Statement
set @MyVar = (select field from table where otherfield=@othervar)
Select Statement
select @MyVar = field from table where otherfield=@othervar
Both ways will yield the same result as long as there is a result in the table, the difference is when there are no records in the table that you are selecting from. The first example will yield a null whereas the second won't assign a value to the variable at all. If the variable was null to start with then the result will look the same but if @MyVar held a value other than null before then it will still have that previous value.
Something to think about if assigning variables this way within a loop.
No comments:
Post a Comment