SQL SERVER 2008 , Nested procedures issue

Question

Considering the following example

Procedure1
..........
IF(@errorcode<>0) ROLLBACK TRANSACTION
ELSE COMMIT TRANSACTION
SELECT @errorcode

Procedure2
..........
WHILE [condition] BEGIN
   EXEC @proc1result = Procedure1 [parameters]
   IF(@proc1result=0) SET @totalresult=@totalresult+1
END
SELECT @totalresult

The problem is that @totalresult is incremented correctly but the value returned by Procedure2 is 0. How to get it right?

I am using sql server 2008 and Entity Framework 4. Procedure1 works well.

Yves M. · Answer 1 · 2011-09-29T09:34:02.387

0

"but the value returned by Procedure2 is 0"

You do a SELECT @totalresult. Should it be return @totalresult?

Answer to Upendra...

CREATE PROC dbo.TestReturn (@InValue int)
AS
Return @Invalue+1
GO

declare @value int
exec @value = TestReturn 100
select @value

edited Sep 29 '11 at 09:34

answered Sep 29 '11 at 09:16

Yves M.

3,330
14
12

How can return statement will return in procedure ?. It should work in function. – Upendra Chaudhari Sep 29 '11 at 09:27
Since i am using Entity Framework , it seems that i need to use select instead of return to return a scalar value – gigi Sep 29 '11 at 12:47

Bogdan Sahlean · Accepted Answer · 2011-09-29T09:42:59.500

0

(1) For the first stored procedure, you should use RETURN @errorcode and not SELECT @errorcode. This is also my recommendation.

OR (NOT AND)

(2) For the second stored procedure, you should use INSERT ... EXEC Procedure1 like this:

WHILE [condition] 
BEGIN
   DECLARE @Results TABLE (RetValue INT PRIMARY KEY);

   INSERT @Results
   EXEC Procedure1 [parameters];

   --IF 0=ALL(SELECT a.RetValue FROM @Results a)
   IF NOT EXISTS(SELECT * FROM @Results a WHERE a.RetValue <> 0)
      SET @totalresult=@totalresult+1;

END
SELECT @totalresult

edited Sep 29 '11 at 09:42

answered Sep 29 '11 at 09:36

Bogdan Sahlean

19,233
3
42
57

If the transaction from procedure1 rollback i get "Cannot use the ROLLBACK statement within an INSERT-EXEC statement." exception, If everything goes well and Procedure1 commits every time i get the right result. – gigi Sep 29 '11 at 12:29
@gigi: If you look at my answer you will see my recommendation. For a stored procedure, `RETURN @something` must be used to return the error code while `SELECT something ...` must be used to return a record-set. – Bogdan Sahlean Sep 29 '11 at 15:46

SQL SERVER 2008 , Nested procedures issue

2 Answers2