May 16, 2012 at 8:12 am
I have a developer who is trying to code a stored procedure in T-SQL like you would code in C or a number of other languages. He wants to be able to complete a task and immediately exit the stored procedure without performing the rest of the code if a certain condition is met.
I'm pretty sure that there is no such functionality in T-SQL, but I wanted to verify. Does anyone know of such a thing?
May 16, 2012 at 8:27 am
The RETURN statement will do that.
May 16, 2012 at 8:36 am
Really? The RETURN statement will stop a proc in mid execution?
I'm looking over BOL on that and don't see any statements to that effect. That's why I posted here.
May 16, 2012 at 8:46 am
From BOL:
Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.
Now - the examples aren't very good since they encapsulate everything in IF statements, but you can prove it with something like this:
create procedure testfun as
print 'hi'
return 0
print 'hi2'
select 1/0 --should error
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 16, 2012 at 8:47 am
Here is a link http://msdn.microsoft.com/en-us/library/ms174998.aspx
May 16, 2012 at 8:53 am
Brandie Tarvin (5/16/2012)
Really? The RETURN statement will stop a proc in mid execution?I'm looking over BOL on that and don't see any statements to that effect. That's why I posted here.
in any of my procedures that are user accessible i have a block at the beginning something like this:
IF @Input <> (good input)
BEGIN
SELECT 'you have bad input'
RETURN
END
kills it with out going through any other code.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 16, 2012 at 9:07 am
Thank everyone for the relevant highlights and links. I can't believe I missed that.
May 16, 2012 at 9:10 am
Matt Miller (#4) (5/16/2012)
Now - the examples aren't very good since they encapsulate everything in IF statements...
Exactly the problem I was having when reading the entries. I hate BOL examples sometimes.
Thank you for the quick n dirty non-IF example.
May 16, 2012 at 9:17 am
capn.hector (5/16/2012)
in any of my procedures that are user accessible i have a block at the beginning something like this:
IF @Input <> (good input)BEGIN
SELECT 'you have bad input'
RETURN
END
kills it with out going through any other code.
That's something that the stored procs I've written as utilities for me have. If you run them without parameters, they also return a Help entry explaining how to use them. 🙂
May 16, 2012 at 9:25 am
mtassin (5/16/2012)
capn.hector (5/16/2012)
in any of my procedures that are user accessible i have a block at the beginning something like this:
IF @Input <> (good input)BEGIN
SELECT 'you have bad input'
RETURN
END
kills it with out going through any other code.
That's something that the stored procs I've written as utilities for me have. If you run them without parameters, they also return a Help entry explaining how to use them. 🙂
same here. however the politeness of the help text depends on who will see it.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 16, 2012 at 9:34 am
You can also use RAISERROR with a severity greater than/equal to 10. But RAISERROR should be used with TRY..CATCH to be used effectively as a exit mechanism.
Sample piece
Use TempDB
GO
Create proc testImmedtExit
as
begin
begin try
Print 'Hello folks'
Raiserror ('Someone screamed Hello',16,1)
-- This will cause an error
select 1/0
end try
begin catch
Print 'we are in catch block ; problem somewhere'
end catch
end ;
GO
exec testImmedtExit;
GO
Drop proc testImmedtExit;
GO
{Edit : added extra information}
May 16, 2012 at 9:37 am
mtassin (5/16/2012)
capn.hector (5/16/2012)
in any of my procedures that are user accessible i have a block at the beginning something like this:
IF @Input <> (good input)BEGIN
SELECT 'you have bad input'
RETURN
END
kills it with out going through any other code.
That's something that the stored procs I've written as utilities for me have. If you run them without parameters, they also return a Help entry explaining how to use them. 🙂
Okay, now you've peaked my interest. How do you do the help entry? Does it just do a PRINT? Or a SELECT? Or is there some other neat trick you do?
May 16, 2012 at 10:02 am
Brandie Tarvin (5/16/2012)
Okay, now you've peaked my interest. How do you do the help entry? Does it just do a PRINT? Or a SELECT? Or is there some other neat trick you do?
I just use Print, but you could use RAISERROR as mentioned above, not a big fan of select, as I normally have data come back in a grid... and playing games to tell me how to run the stored proc I wrote for myself doesn't thrill me.
May 16, 2012 at 10:08 am
ColdCoffee,
RAISEERROR would definitely work, but in this case, the dev specified that he wanted to exit without raising an error. Which limits the options down to RETURN().
May 16, 2012 at 1:30 pm
mtassin (5/16/2012)
Brandie Tarvin (5/16/2012)
Okay, now you've peaked my interest. How do you do the help entry? Does it just do a PRINT? Or a SELECT? Or is there some other neat trick you do?
I just use Print, but you could use RAISERROR as mentioned above, not a big fan of select, as I normally have data come back in a grid... and playing games to tell me how to run the stored proc I wrote for myself doesn't thrill me.
Im the oppisite, i use select so i can have a nice header by aliasing the column with help error or some other meaningful thing.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply