Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How Stored Procedure determine to return a value?


How Stored Procedure determine to return a value?

Author
Message
masoudk1990
masoudk1990
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 299
This is my Stored Procedure:


DECLARE @MyString NVARCHAR(3);

SELECT @MyString = 'foo'

SELECT CASE
WHEN 1 = 1
THEN 'One is equal to one'
END



If I execute this Stored Procedure I have this output:
'One is equal to one'


Now I change my Stored Procedure to this:


DECLARE @MyString NVARCHAR(3);

SELECT CASE
WHEN 1 = 1
THEN 'One is equal to one'
END

SELECT @MyString = 'foo'



Now if I execute my Stored Procedure once again I have this output:
'One is equal to one'


Why? I expected this time I should have 'foo' as output.
Because the last SELECT statement is 'foo'

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Nick_UK
Nick_UK
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 1181
Hi,
The statement

SELECT @MyString = 'foo'

is populating the variable with the value 'foo' to return this value you would then need to select the variable


SELECT @mystring


BOL reference: http://msdn.microsoft.com/en-us/library/ms187953(v=SQL.105).aspx
masoudk1990
masoudk1990
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 299
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'



___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45133 Visits: 39923
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
masoudk1990
masoudk1990
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 299
Thanks Jeff Moden, I'm your fan in this forum.

By the way, what you've posted isn't a stored procedure. It's just a script


You wrote this Stored Procedure in one of your articles:


SELECT @MyCount = @@ROWCOUNT

SELECT CASE
WHEN COUNT(hi.RowNum) + 1 = @MyCount
THEN 'Account Running Total Calculations are correct'
ELSE 'There are some errors in the Account Running Totals'
END



I was confused why first SELECT statement don't show @MyCount in output.
Because I have a programming background, and I thought SELECT is equal to RETURN.

In other programming languages if you have this code:

int x;
Return x = 10;



Of course Return don't populating x with 10 Wink

But now I understand it isn't a stored procedure. It's just a script.

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search