masoudk1990 (7/5/2013)
Thank you very much.I changed my Stored Procedure to this:
DECLARE @MyString NVARCHAR(3);
SELECT CASE
WHEN 1 = 1
THEN 'One is equal to one'
END
SELECT @MyString = 'foo'
SELECT @MyString
Now I have this two lovely outputs:
'One is equal to one'
'foo'
I thought SELECT returns values and SET populating the variable with the values.
For example:
SET @MyString = 'foo'
Both SET and SELECT can be used to assign values to variables. SET only works with one variable at a time. SELECT can set the values of multiple variables all with the same SELECT. In fact, it's an optimization for functions and procs that are frequently hit because the SELECT assignment of multiple variables is actually a wee bit faster than using SET for multiple single variables.
You can do all sorts of "tricks" with variables and column aliasing for returns...
DECLARE @MyString NVARCHAR(3),
@OtherString NVARCHAR(100)
;
SELECT @OtherString = CASE
WHEN 1 = 1
THEN 'One is equal to one'
END,
@MyString = 'foo'
;
SELECT SomeColumn1 = @MyString,
SomeColumn2 = @OtherString
;
By the way, what you've posted isn't a stored procedure. It's just a script.
--Jeff Moden
Change is inevitable... Change for the better is not.