How Do I Call Multilple Procs Into Into Once Proc

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

i have 4 procedure which will take

1 st procedure take variable id , monthly
2 nd procedure take variable id , yearly
3 rd procedure take variable id, quarterly
4 rd procedure take variable id, half yearly

all 4 procedure give same result in same table with columns

id,name,frequency,result.

when i call 1st procedure it need to take 1st procedure variables

EX:- exec x 1,56,JAN

exec x 2,56,Y1

how do i combine all these ?

X main procedure name

Posted 24-Nov-14 21:54pm

Burning Thoughts

Updated 24-Nov-14 22:30pm

v5

Add a Solution

Comments

Shweta N Mishra 25-Nov-14 4:19am

what is 1,56 or 2,56 ?

Burning Thoughts 25-Nov-14 4:23am

if i select 1 st procedure exec query need to take 56-id and JAN as variable
if i select 2 nd procedure exec query need to take 56-id and y1 as variable

Shweta N Mishra 25-Nov-14 4:30am

On what basis you select which Stored procedure to execute ?

Burning Thoughts 25-Nov-14 4:34am

if(@x=1)
select * from ... where id=@id and month=@month
if(@x=2)
select * from where id=@id and month=@year

while executing

if i select 1

procedure need to take variable input as id and month

if i select 1

procedure need to take variable input as id and month

if i select 2

procedure need to take variable input as id and year

Burning Thoughts 25-Nov-14 4:43am

let me try .. hope it will come .any way thank you

Shweta N Mishra 25-Nov-14 5:23am

np :), Mark it as accepted if it works for you.

Burning Thoughts 26-Nov-14 1:06am

2nd solution also good i got answer for my question

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shweta N Mishra · Accepted Answer · 2014-11-24T22:37:00

I think it should be something like this

SQL

Create Proc MainProc(@procNameToExecute varchar(50),@VariableId int,@Month int,@Year int,@Quarter int)
As
BEGIN

	if @procNameToExecute='1stProc'
	BEGIN
	  exec 1stChildProc @VariableId,@Month
	END
	if @procNameToExecute='2ndProc'
	BEGIN
	  exec 2ndChildProc @VariableId,@Year
	END
	if @procNameToExecute='3rdProc'
	BEGIN
	  exec 3rdChildProc @VariableId,@Quarter
	END

END

jaket-cp · Accepted Answer · 2014-11-25T04:58:00

As possible alternatives, modification of Shweta N Mishra's solution:

SQL

--If @Month, @Year and @Quarter have the same datatype
CREATE PROCEDURE [MainProc]
	@procNameToExecute varchar(50),
	@VariableId int,
	@VariableValue int
AS
BEGIN
	If @procNameToExecute='1stProc' Begin
	  exec 1stChildProc @VariableId,@VariableValue;
	End;
	If @procNameToExecute='2ndProc' Begin
	  exec 2ndChildProc @VariableId,@VariableValue;
	End;
	If @procNameToExecute='3rdProc'	Begin
	  exec 3rdChildProc @VariableId,@VariableValue;
	End;
END

--Or if only one of @Month, @Year or @Quarter are populated, then @procNameToExecute can be ommitted
CREATE PROCEDURE [MainProc]
	@VariableId int,
	@Month int = null,
	@Year int = null,
	@Quarter int = null
AS
BEGIN
	If @Month is not null Begin
	  exec 1stChildProc @VariableId,@Month;
	End;
	If @Year is not null Begin
	  exec 2ndChildProc @VariableId,@Year;
	End;
	If @Quarter is not null Begin
	  exec 3rdChildProc @VariableId,@Quarter;
	End;
END

How Do I Call Multilple Procs Into Into Once Proc

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0