Nested Stored Procedure Creation Problem

Question

0.00/5 (No votes)

See more:

hi,
i need to create nested SP, i used previous SP to execute inside my Own SP. But it throws error...

Please give me some Productive input to fix this mess....

create procedure ALLinone
(
@Sname varchar(50),
@Srollno varchar(50),
@Pname varchar(50),
@Saddr varchar(50)
)
As
Begin
Exec SP_Testone @Sname, @Srollno
Go
Exec SP_Testtwo @Sname, @Pname
Go
Exec SP_Testthree @Srollno, @Saddr
End

It throws error like...

Msg 102, Level 15, State 1, Procedure ALLinone, Line 11<br />
Incorrect syntax near '@Srollno'.<br />
Msg 137, Level 15, State 2, Line 2<br />
Must declare the scalar variable "@Sname".<br />
Msg 137, Level 15, State 2, Line 2<br />
Must declare the scalar variable "@Srollno".

Even i tried run each and every SP seperately its works Perfect....But problem is while create this nested SP

Posted 22-Jul-11 4:12am

J.Karthick

Add a Solution

1 solution

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)

Manas Bhardwaj · Accepted Answer · 2011-07-22T04:14:00

Solution 1

GO isn't a TSQL command. It marks the end of a batch in Query Analyzer and
therefore signals the end of a stored procedure definition in that batch, so
it should not be part of an SP.

NOTE : I assumed that three calls are independent of each other and that is why error handling is seperate...

CREATE PROCEDURE ALLinone
(
	@Sname varchar(50),
	@Srollno varchar(50),
	@Pname varchar(50),
	@Saddr varchar(50)
)
AS
BEGIN
	CREATE TABLE #Errors (ErrAt NVARCHAR(20), ErrNumber INT, ErrorMessage NVARCHAR(MAX))
	
	BEGIN TRY
		EXEC SP_Testone @Sname, @Srollno
	END TRY
	BEGIN CATCH
		INSERT #Errors
		SELECT 'SP_Testone', ERROR_NUMBER(), ERROR_MESSAGE()
	END CATCH;
	
	BEGIN TRY
		EXEC SP_Testtwo @Sname, @Pname
	END TRY
	BEGIN CATCH
		INSERT #Errors
		SELECT 'SP_Testtwo', ERROR_NUMBER(), ERROR_MESSAGE()
	END CATCH;
	
	BEGIN TRY
		EXEC SP_Testthree @Sname, @Pname
	END TRY
	BEGIN CATCH
		INSERT #Errors
		SELECT 'SP_Testthree', ERROR_NUMBER(), ERROR_MESSAGE()
	END CATCH;
	
	SELECT * FROM #Errors
END

Posted 22-Jul-11 4:14am

Manas Bhardwaj

Updated 22-Jul-11 4:34am

v3

Comments

J.Karthick 22-Jul-11 10:24am

Wooow....Thanks :)
You fix this.....

J.Karthick 22-Jul-11 10:25am

One more question...Is it possible to use try...catch for my nested SP?

Manas Bhardwaj 22-Jul-11 10:32am

modified my answer

J.Karthick 22-Jul-11 10:35am

Is this the method to Handle the error??

But it will create an table for each and every execution??

Manas Bhardwaj 22-Jul-11 10:37am

Table is created once. Error results are inserted into table (if any). This may or may not be the best way to handle error in your SP.

As, I said in answer, I have assumed 3 SPs to be independdent of each other and thats why three different try catch blocks.

J.Karthick 22-Jul-11 10:40am

k fine...

@Sname varchar(50),
@Srollno varchar(50),
@Pname varchar(50),
@Saddr varchar(50)
)
As
Begin

Begin try
Begin transaction;
Exec SP_Testone @Sname, @Srollno
print 'Testone Success'
commit transaction;
End try

Begin catch
rollback transaction;
raiserror ('Error in SP_Testone')
End catch

Exec SP_Testtwo @Sname, @Pname

Exec SP_Testthree @Srollno, @Saddr

End

What the error in this??

J.Karthick 22-Jul-11 10:41am

Is that possible for transaction ??
I just tried...It shows error like

Incorrect syntax near ')'

J.Karthick 22-Jul-11 10:54am

Solved my Issue....

k fine...

@Sname varchar(50),
@Srollno varchar(50),
@Pname varchar(50),
@Saddr varchar(50)
)
As
Begin

Begin try
Begin transaction;
Exec SP_Testone @Sname, @Srollno
commit transaction;
End try

Begin catch
rollback transaction;
End catch

Exec SP_Testtwo @Sname, @Pname

Exec SP_Testthree @Srollno, @Saddr

End

Get Ready Folks..... :) :D :)