EXECuting SP within another SP

I am trying to call an SP within another SP, but the result being returned
from the inner SP is Null, whereas I should expect it to return a 1 or 0...
Here is the code:
ALTER Proc Returns_RecordPostClean
@.SerialNo int,
@.LineID int,
@.PostCleanDate smalldatetime,
@.PostCleanResult varchar(10),
@.PostCleanText varchar(50),
@.Exothermed tinyint,
@.Turned bit = 0
As
Set NoCount On
Begin Tran
Update Cleans
Set PostCleanDate = @.PostCleanDate,
PostCleanResult = @.PostCleanResult,
PostCleanText = @.PostCleanText,
Exothermed = @.Exothermed,
Turned = @.Turned
Where SerialNo = @.SerialNo
and LineID = @.LineID
Declare @.Status varchar(30)
Declare @.PFE bit
Declare @.Result int
Select @.PFE=O.PFE
From Orders O
inner join OrderDetail D on D.OrderID = O.OrderID
where D.LineID = @.LineID
If @.PostCleanResult = 'Passed'
If @.PFE = 1
Set @.Status = 'PFE Credit Note'
Else
Begin
Set @.Status = 'Complete'
Exec @.Result=Common_UpdateOrderStatus @.LineID
<========= calling 2nd SP
End
Else
Set @.Status = 'Quarantined'
Update OrderDetail
Set Status = @.Status
Where LineID = @.LineID
If @.@.RowCount = 1 and @.Result = 1
Begin
Select @.Result as Success
Commit tran
End
Else
Begin
Select @.Result as Success
rollback tran
End
Set NoCount Off
If I call the 2nd SP outside this SP, it works fine. But for reasons I can't
be bothered going in to, it really needs to be call from within this SP.
This is actually the first time I've asked one SP to call another, so I'm
not sure if I'm missing something..
Thanks
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Can you post the code of the second sp?
AMB
"CJM" wrote:

> I am trying to call an SP within another SP, but the result being returned
> from the inner SP is Null, whereas I should expect it to return a 1 or 0..
.
> Here is the code:
> ALTER Proc Returns_RecordPostClean
> @.SerialNo int,
> @.LineID int,
> @.PostCleanDate smalldatetime,
> @.PostCleanResult varchar(10),
> @.PostCleanText varchar(50),
> @.Exothermed tinyint,
> @.Turned bit = 0
> As
> Set NoCount On
> Begin Tran
> Update Cleans
> Set PostCleanDate = @.PostCleanDate,
> PostCleanResult = @.PostCleanResult,
> PostCleanText = @.PostCleanText,
> Exothermed = @.Exothermed,
> Turned = @.Turned
> Where SerialNo = @.SerialNo
> and LineID = @.LineID
> Declare @.Status varchar(30)
> Declare @.PFE bit
> Declare @.Result int
> Select @.PFE=O.PFE
> From Orders O
> inner join OrderDetail D on D.OrderID = O.OrderID
> where D.LineID = @.LineID
> If @.PostCleanResult = 'Passed'
> If @.PFE = 1
> Set @.Status = 'PFE Credit Note'
> Else
> Begin
> Set @.Status = 'Complete'
> Exec @.Result=Common_UpdateOrderStatus @.LineID
> <========= calling 2nd SP
> End
> Else
> Set @.Status = 'Quarantined'
>
> Update OrderDetail
> Set Status = @.Status
> Where LineID = @.LineID
> If @.@.RowCount = 1 and @.Result = 1
> Begin
> Select @.Result as Success
> Commit tran
> End
> Else
> Begin
> Select @.Result as Success
> rollback tran
> End
> Set NoCount Off
>
> If I call the 2nd SP outside this SP, it works fine. But for reasons I can
't
> be bothered going in to, it really needs to be call from within this SP.
> This is actually the first time I've asked one SP to call another, so I'm
> not sure if I'm missing something..
> Thanks
>
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||Sure, here it is:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Proc Common_UpdateOrderStatus
@.LineID int
As
Declare @.Total int, @.Complete int, @.OrderID int
Select
@.OrderID=(Select O.OrderID
from Orders O
inner join OrderDetail D on D.OrderID = O.OrderID
where D.LineID = @.LineID),
@.Total=(Select Count(*)
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join OrderDetail D2 on D2.OrderID = D.OrderID
where D2.LineID = @.LineID),
@.Complete=(Select Count(*)
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join OrderDetail D2 on D2.OrderID = D.OrderID
where D2.LineID = @.LineID
and D.Status = 'Complete' or D.Status = 'Cancelled')
If @.Complete = @.Total
Begin
/* all lines complete or cancelled */
Update Orders
Set Status = 'Complete'
Where OrderID = @.OrderID
If @.@.RowCount = 1
Select 1 as Success
Else
Select 0 as Success
End
Else
Select 2 As Success
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5021F829-D752-4C84-B154-ACF772380731@.microsoft.com...
> Can you post the code of the second sp?
>
> AMB
>|||CJM wrote:
> Sure, here it is:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER Proc Common_UpdateOrderStatus
> @.LineID int
> As
> Declare @.Total int, @.Complete int, @.OrderID int
> Select
> @.OrderID=(Select O.OrderID
> from Orders O
> inner join OrderDetail D on D.OrderID = O.OrderID
> where D.LineID = @.LineID),
> @.Total=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID),
> @.Complete=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID
> and D.Status = 'Complete' or D.Status = 'Cancelled')
> If @.Complete = @.Total
> Begin
> /* all lines complete or cancelled */
> Update Orders
> Set Status = 'Complete'
> Where OrderID = @.OrderID
> If @.@.RowCount = 1
> Select 1 as Success
> Else
> Select 0 as Success
> End
> Else
> Select 2 As Success
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:5021F829-D752-4C84-B154-ACF772380731@.microsoft.com...
You need to use a RETURN statement to return a... return value. Do not
use a result set as it will play havok with your application if not
managed carefully.
Instead of:
Select 0
Use:
Return 0
David Gugick
Imceda Software
www.imceda.com|||CJM,
A sp returns a value using the RETURN keyword, mainly to indicate success or
fail (0 - success, another int value diff from 0 to indicate fail).
If you want to return a value with a specific meaning, is better to use an
output parameter.
ALTER Proc Common_UpdateOrderStatus
@.LineID int,
@.op int output
As
...
if @.@.error <> 0 return 1
If @.Complete = @.Total
Begin
/* all lines complete or cancelled */
Update Orders
Set Status = 'Complete'
Where OrderID = @.OrderID
If @.@.RowCount = 1
set @.op = 1
Else
set @.op = 0
End
Else
set @.op = 2
return 0
go
declare @.i int
declare @.Result int
Exec @.Result=Common_UpdateOrderStatus @.LineID, @.i int output
print @.Result
print @.i
AMB
"CJM" wrote:

> Sure, here it is:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER Proc Common_UpdateOrderStatus
> @.LineID int
> As
> Declare @.Total int, @.Complete int, @.OrderID int
> Select
> @.OrderID=(Select O.OrderID
> from Orders O
> inner join OrderDetail D on D.OrderID = O.OrderID
> where D.LineID = @.LineID),
> @.Total=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID),
> @.Complete=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID
> and D.Status = 'Complete' or D.Status = 'Cancelled')
> If @.Complete = @.Total
> Begin
> /* all lines complete or cancelled */
> Update Orders
> Set Status = 'Complete'
> Where OrderID = @.OrderID
> If @.@.RowCount = 1
> Select 1 as Success
> Else
> Select 0 as Success
> End
> Else
> Select 2 As Success
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:5021F829-D752-4C84-B154-ACF772380731@.microsoft.com...
>