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.
Add a comment
|
6 Answers
SET @Result = 'Unknown'
;)
Comments
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.
3 Comments
Christian Hagelid
really? Why is SET discouraged?
yfeldblum
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.
Rob Garrison
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.
change this line
@Result = 'Unknown'
to
set @Result = 'Unknown'
Comments
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
Comments
IF (@Result = '')
BEGIN
SET @Result = 'Unknown'
END
RETURN @Result
