In the end of my function, I have the statement:
RETURN @Result
What I want to do is something like this:
IF (@Result = '')
BEGIN
@Result = 'Unknown'
END
RETURN @Result
The above does not work though.
views:
605answers:
4
+2
A:
IF (@Result = '')
BEGIN
SELECT @Result = 'Unknown'
END
RETURN @Result
Notice that the way you do assignment in T-SQL is the SELECT statement. You can also use the SET statement although that is discouraged.
Justice
2009-05-14 23:12:24
really? Why is SET discouraged?
Christian Hagelid
2009-05-14 23:13:51
SET is the command for setting options (SET ANSI_QUOTES ON, SET IDENTITY_INSERT MyTable OFF). SELECT is the command for all queries, and queries are more than capable of assigning values to variables (if there is only one row). You could use SET @myvar = (SELECT TOP 1 MyCol FROM MyTable), but what's the point when you could also do SELECT TOP 1 @myvar = MyCol, @myvar2 = MyCol2, @myvar3 = MyCol3 FROM MyTable.
Justice
2009-05-14 23:23:49
It tends to be a religious thing, but for something like this where you're setting a local variable, both SELECT and SET are used and acceptable.Justice, can you come up with an article that would back up the "discouraged" claim?I generally use SET when it involves just one variable and SELECT when I'm setting multiple variables in a single command.
Rob Garrison
2009-05-14 23:41:36
+2
A:
change this line
@Result = 'Unknown'
to
set @Result = 'Unknown'
Christian Hagelid
2009-05-14 23:13:00
+1
A:
I think you need to check if @result is NULL, because NULL is not the same as ''
IF (ISNULL(@Result, '') = '')
BEGIN
SET @Result = 'Unknown'
END
RETURN @Result
Jon Erickson
2009-05-14 23:25:34