March 14, 2008 at 5:02 am
Im just beginning to get my head round SP's so Im about to ask a very basic question :blush:
say you have 4 stored procedures. Each using different parameters
EXEC usp_a '2007-09-03 00:00:00.000'
EXEC usp_b 'SEN'
EXEC usp_c '2007-01-26 00:00:00.000'
EXEC ups_d 'LAC'
For example the parameters are, from UPS_a to usp_d @FolderDate, @Servicea, @acYearStart, @Serviceb
And you create a top layer stored procedure to execute all 4 procedures
CREATE Procedure usp_Run_all_4_Procedures
@FolderDate datetime
@Servicea string
@acYearStart datetime
@Serviceb string
AS
EXEC usp_Run_all_4_Procedures
but what then goes into the procedure itsself since you want to change the parameters at the top
layer
you cant do this anymore
EXEC usp_a '2007-09-03 00:00:00.000'
EXEC usp_b 'SEN'
EXEC usp_c '2007-01-26 00:00:00.000'
EXEC ups_d 'LAC'
I dont know how much sense this makes so apologies.
Debbie
March 14, 2008 at 5:06 am
Is that what you have in code?
CREATE Procedure usp_Run_all_4_Procedures
@FolderDate datetime
@Servicea string
@acYearStart datetime
@Serviceb string
AS
EXEC usp_Run_all_4_Procedures
It should be
CREATE Procedure usp_Run_all_4_Procedures
(
@FolderDate datetime,
@Servicea string,
@acYearStart datetime,
@Serviceb string
)
AS
EXEC usp_a @FolderDate
EXEC usp_b @Servicea
EXEC usp_c @acYearStart
EXEC ups_d @Serviceb
Piotr
...and your only reply is slàinte mhath
March 14, 2008 at 5:09 am
Sorry that was just example code not the actual code Im running, I just forgot to put the commas in.
March 14, 2008 at 6:40 am
OK. I'm confused.
You can use a wrapper proc to call the other procedures. You just have to make sure that the wrapper proc has parameters for those procedures. I do this all the time because you can put all kinds of IF...ELSE logic in the wrapper proc and call the appropriate procedures, passing in parameters, and still get good execution plans & minimal recompiles.
So what was it you were trying to do again?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 14, 2008 at 7:41 am
I dont know what a wrapper procedure is....
:ermm:
March 14, 2008 at 7:50 am
Debbie
A wrapper procedure is just a procedure that calls other procedures. Now, what is it exactly that you're trying to do?
John
March 14, 2008 at 8:38 am
Ive tried to think it through but i dont know how else to explain it than what Ive already written.
Im trying to create a top sp that executes 4 SP's each with different parameters.
Normally you would create the lower proc with for example
@FolderDate dateTime
Then in the main body something like WHERE folderDate = FolderDate.
Then the bit where you execute it would say
Exec usp_proca 2007 23/04/2001
BUT because you are actually calling 4 procedures from the top procedure you dont want to add the parameter there becasue you wont be executing at that level so Im not sure what to do???
March 14, 2008 at 8:42 am
But you said that wasn't the actual code you're running. Why does Piotr's example not work for you?
John
March 14, 2008 at 8:45 am
Sorry Im afraid you have lost me now....
March 14, 2008 at 8:47 am
Snap.
March 14, 2008 at 8:53 am
Debbie,
Piotr's example is exactly what you are looking for. In his example, you pass in 4 parameters into the main stored procedure,
@FolderDate datetime
@Servicea string
@acYearStart datetime
@Serviceb string
You then execute each of the secondary stored procedures with the appropriate variable
EXEC usp_1 @FolderDate
EXEC usp_2 @Servicea
EXEC usp_3 @acYearStart
EXEC usp_4 @Serviceb
March 14, 2008 at 9:02 am
Ah,
Thats brilliant, Thanks for that.
(I only just got started with SP's a short while ago so Im only a beginner with it. That makes sense though Ill get it set up)
🙂
March 14, 2008 at 9:19 am
NP, let us know how it goes. 🙂
March 14, 2008 at 9:38 am
Worked a treat,
I executed from the top as
Exec usp_SessAtt_Run_SessionalAttendance_SPs
@AttendDate ='2007-09-03 00:00:00.000',
@WK_COMM = '2007-09-03 00:00:00.000'
(Bearing in mind this is the proper code rather than the examples I put in here)
Ive now added this to a job that will run ever evening so I dont have to run scripts for this task anymore.
Thanks ever so much for your help. Im slowly but surely picking things up! 🙂
March 14, 2008 at 9:49 am
Cool. Thanks for the feedback.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply