Return

Question

Post reply

Return

Carlo Romagnano

SSC-Insane

Points: 22596
More actions
July 16, 2013 at 8:56 pm

#280179

Comments posted to this topic are about the item Return
July 17, 2013 at 5:07 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 1

Learn new thing. Thanks carlo 🙂

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Hi,

as per the explanation- The stmt "return (select 5)" gives error because in a batch RETURN with integer_expression is not admitted (note: PARENTHESIS cast the SELECT to an integer_expression for RETURN).

Create PROCEDURE checkstate @param varchar(11)

AS

IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE BusinessEntityID = @param) = 'WA'

RETURN 1

ELSE

RETURN 2 ;

GO

declare @returnstatus int

EXEC @returnstatus = checkstate 291

select @returnstatus

But we use integer expression with RETURN in a stored procedure and it works...

I am confused that will integer expression works only in stored procedure and not in sql statements :blink:

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Very interesting question, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

DevilsChest Mr or Mrs. 500 Points: 516 More actions · Answer 4

kapil_kk (7/16/2013)
But we use integer expression with RETURN in a stored procedure and it works...
I am confused that will integer expression works only in stored procedure and not in sql statements :blink:

You are not the only one confused by the statement :unsure:

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

DevilsChest (7/17/2013)
kapil_kk (7/16/2013)
But we use integer expression with RETURN in a stored procedure and it works...
I am confused that will integer expression works only in stored procedure and not in sql statements :blink:
You are not the only one confused by the statement :unsure:

OK, if you dont have any confusion then plz then give the answer to my queries

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Carlo Romagnano SSC-Insane Points: 22596 More actions · Answer 6

kapil_kk (7/16/2013)
Hi,
as per the explanation- The stmt "return (select 5)" gives error because in a batch RETURN with integer_expression is not admitted (note: PARENTHESIS cast the SELECT to an integer_expression for RETURN).
Create PROCEDURE checkstate @param varchar(11)
AS
IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE BusinessEntityID = @param) = 'WA'
RETURN 1
ELSE
RETURN 2 ;
GO
declare @returnstatus int
EXEC @returnstatus = checkstate 291
select @returnstatus
But we use integer expression with RETURN in a stored procedure and it works...
I am confused that will integer expression works only in stored procedure and not in sql statements :blink:

It's easy: in a BATCH, you can use RETURN, but without a return value. In functions and procs you can use RETURN with an integer value!

Do you know the difference between BATCH, FUNCTS and PROCS? 🙂

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

Carlo Romagnano (7/17/2013)
kapil_kk (7/16/2013)
Hi,
as per the explanation- The stmt "return (select 5)" gives error because in a batch RETURN with integer_expression is not admitted (note: PARENTHESIS cast the SELECT to an integer_expression for RETURN).
Create PROCEDURE checkstate @param varchar(11)
AS
IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE BusinessEntityID = @param) = 'WA'
RETURN 1
ELSE
RETURN 2 ;
GO
declare @returnstatus int
EXEC @returnstatus = checkstate 291
select @returnstatus
But we use integer expression with RETURN in a stored procedure and it works...
I am confused that will integer expression works only in stored procedure and not in sql statements :blink:
It's easy: in a BATCH, you can use RETURN, but without a return value. In functions and procs you can use RETURN with an integer value!
Do you know the difference between BATCH, FUNCTS and PROCS? 🙂

Yes I know the differences between these....

Thanks its clear to me now 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

TomThomson SSC Guru Points: 104773 More actions · Answer 8

kapil_kk (7/17/2013)
Carlo Romagnano (7/17/2013)
kapil_kk (7/16/2013)
Hi,
as per the explanation- The stmt "return (select 5)" gives error because in a batch RETURN with integer_expression is not admitted (note: PARENTHESIS cast the SELECT to an integer_expression for RETURN).
Create PROCEDURE checkstate @param varchar(11)
AS
IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE BusinessEntityID = @param) = 'WA'
RETURN 1
ELSE
RETURN 2 ;
GO
declare @returnstatus int
EXEC @returnstatus = checkstate 291
select @returnstatus
But we use integer expression with RETURN in a stored procedure and it works...
I am confused that will integer expression works only in stored procedure and not in sql statements :blink:
It's easy: in a BATCH, you can use RETURN, but without a return value. In functions and procs you can use RETURN with an integer value!
Do you know the difference between BATCH, FUNCTS and PROCS? 🙂
Yes I know the differences between these....
Thanks its clear to me now 🙂

I imagine the wording of the answer options (for example "Batch 1 returns 1") was chosen to allow that confusion. The reason that you can't use return with a value in a batch is that batches don't return scalar values, they return (possibly empty) result sets or they don't return anything or they raise an error. So technically every one of the answers options is misusing "return" in a way that makes it incorrect, and while it's fairly common usage to use return in that manner it is confusing to do so when discussion the RETURN primitive in SQL.

But it's a nice fun question anyway.

Tom

Bangla Hall of Fame Points: 3137 More actions · Answer 9

Bangla

Hall of Fame

Points: 3137

July 17, 2013 at 4:20 am

#1633168

Nice one.....

paul s-306273 SSChampion Points: 10727 More actions · Answer 10

paul s-306273

SSChampion

Points: 10727

July 17, 2013 at 8:26 am

#1633311

Nice qn.

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 11

Excellent Question

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Primo Dang SSCrazy Points: 2643 More actions · Answer 12

L' Eomot Inversé (7/17/2013)
The reason that you can't use return with a value in a batch is that batches don't return scalar values, they return (possibly empty) result sets or they don't return anything or they raise an error.

Thanks for the explanation, Tom. I had no idea batches could return anything at all.

Also, thank you Carlo for the question. I loved the trick with non-existing table! 😀

kapil_kk SSC-Insane Points: 21316 More actions · Answer 13

Thanks Tom for the explanation.......

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/