October 24, 2008 at 1:10 pm
I am creating a stored procedure that will check an input variable and based on that execute another stored procedure. I am sure this is the easiest thing in the world to do, but I am stumped. I tried it with a case:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE initTran
@type int,
@data char(82) output,
@return int output
AS
BEGIN
SET NOCOUNT ON;
CASE @type
when 1 then--Get Operator Name from number
execute getUser @data output
when 2 then--Get Packout Name from number
execute getUser @data output
when 3 then--Get schedule information
execute getSchedule @data output
when 4 then--Get # of stations for line
execute getStations @data output
when 5 then--Get Label Display
execute getLabelDisplay @data output
when 6 then--Get Temp item label type
execute getTempItemLabel @data output
end
But found something that said that won't work, so I tried an if statement:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE initTran
@type int,
@data char(82) output,
@return int output
AS
BEGIN
SET NOCOUNT ON;
If @type= 1 --Get Operator Name from number
execute getUser @data output
end
end
Still to no avail...I keep getting incorrect syntax.
So what is the "preferred" method of calling multiple SPs from within a SP? And where am I going wrong?
October 24, 2008 at 1:16 pm
Looks to me that you have one too many "end" statements in the second stored proc.
😎
October 24, 2008 at 1:18 pm
Yeah, sorry about that, I had to retype the code in word and accidentally added the extra one.
October 24, 2008 at 1:26 pm
So what is the actual error message you are getting with the second proc?
😎
October 24, 2008 at 1:28 pm
Okay, so I figured it out to the point where it executes, but there has to be a cleaner way of doing this. I am going to have upwards of 20 or so if statements before I am done. Is there no way to use the case at all?
Here is the working IF statements:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE initTran
@type int,
@data char(82) output,
@return int output
AS
BEGIN
SET NOCOUNT ON;
IF (@type = 1)
BEGIN
execute getUser @data output
END
IF (@type = 2)
BEGIN
execute getUser @data output
END
IF (@type = 3)
BEGIN
execute getSchedule @data output
END
IF (@type = 4)
BEGIN
execute getStations @data output
END
IF (@type = 5)
BEGIN
execute getLabelDisplay @data output
END
RETURN @data
END
October 24, 2008 at 1:31 pm
CASE is not a control flow statement, its actually a function. For what you want to do, you need to use the IF statement.
😎
October 24, 2008 at 1:37 pm
Hey thanks for the help! Might not be the answer I was looking for...but what are you going to do!?!?!
Appreciated
Jeff
October 27, 2008 at 4:38 am
You can also get rid of the "BEGIN...END" stuff: after an IF, if you have a single statement, BEGIN...END is not needed.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE initTran
@type int,
@data char(82) output,
@return int output
AS
BEGIN
SET NOCOUNT ON;
IF (@type = 1)
execute getUser @data output
IF (@type = 2)
execute getUser @data output
IF (@type = 3)
execute getSchedule @data output
IF (@type = 4)
execute getStations @data output
IF (@type = 5)
execute getLabelDisplay @data output
RETURN @data
END
No way to use CASE to control the flow in a Stored.
You can use a CASE only in a SELECT or so...
See The Jenga blog
October 27, 2008 at 8:12 am
I know many people who use the BEGIN END even if there is only one statement. They do it so that if they need to add additional statements later, they don't have to worry about adding the BEGIN END pair while doing so. Doesn't hurt anything to have them and for some adds clarity to the code.
😎
October 27, 2008 at 10:51 am
The begin...end is useful; it adds readability and I've had problems with comments in an IF with no BEGIN...END.
To speed things up, if the SPs to execute are mutually exclusive (ie only 1 will get executed) as it appears, then use
IF ... [BEGIN]
exec ...
[END] ELSE IF ... [BEGIN]
exec ...
[END]
etc.
October 27, 2008 at 12:46 pm
If you are looking to simplify the code, you could do something like:
If (@type In (1, 2))
Begin;
Execute ...
End;
If (@type In (3))
Begin;
Execute ...
End;
...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 28, 2008 at 1:40 am
simon (10/27/2008)
The begin...end is useful; it adds readability and I've had problems with comments in an IF with no BEGIN...END.
De gustibus non est disputandum.
I think that without the BEGIN ... END the code is more readable.
I always try to reduce the lines of code taking away non needed stuff.
See The Jenga blog
October 28, 2008 at 8:26 am
Comes down to personal preferences and coding styles. Neither way is right or wrong, just different.
😎
October 28, 2008 at 9:21 am
Yeah, Once I started reading this BEGIN/END stuff, I realized it is like {} in C code, and then it made sense to me. I can see both sides of the debate, and I think in this case, there should never need to be more than one external call, so I am going to leave them out of the main routine. But, like you said, I am leaving them in my external programs, cause you ALWAYS wind up going back and adding another statement somewhere along the development track!
Thanks for everyone's responses!
Jeff
October 28, 2008 at 1:59 pm
Hi
This is what I would do to be able to use the case statement but I am sure that some people will be againt dynamic sql but this works
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE initTran
@type int,
@data char(82) output,
@return int output
AS
BEGIN
DECLARE @sqlstring varchar(50)
SET NOCOUNT ON;
SELECT
@sqlstring =
CASE @type
when 1 then--Get Operator Name from number
'getUser'
when 2 then--Get Packout Name from number
'getUser'
when 3 then--Get schedule information
'getSchedule'
when 4 then--Get # of stations for line
'getStations'
when 5 then--Get Label Display
'getLabelDisplay'
when 6 then--Get Temp item label type
'getTempItemLabel'
end
execute @sqlstring @data output
END
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply