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

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 :)

Nested Stored Procedure Creation Problem

1 solution

Solution 1

Add your solution here

Preview 0

Nested Stored Procedure Creation Problem

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us