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...
>

Execute DTS 2000 Package Task Editor (Inner Variables vs Outer Variables)

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

Jo?o

Can anyone Help me? Any help is welcome. Thanks a lot!

Jo?o

|||

Hi,

Were you able to pass the variables from SSIS to DTS package (called from SSIS using Execute DTS 2000 package task) and back to the parent package (SSIS) global variables?

Thanks in advance,

Subah.

|||

The outer variables are a means of communication from the SSIS package to the DTS package it executes using late binding (the outer variable is evaluated before the DTS package is executed and not earlier).

We map the outer variables from the SSIS package as global variables to the DTS package. E.g. if you add an outer variable called User::mystring variable to the list, we strip the namespace (because DTS does not know about namespaces) and pass it as a global variable named mystring to the DTS package.

|||Thanks, that helped my understanding a lot. I was able to implement the same.

Execute DTS 2000 Package Task Editor (Inner Variables vs Outer Variables)

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

Jo?o

Can anyone Help me? Any help is welcome. Thanks a lot!

Jo?o

|||

Hi,

Were you able to pass the variables from SSIS to DTS package (called from SSIS using Execute DTS 2000 package task) and back to the parent package (SSIS) global variables?

Thanks in advance,

Subah.

|||

The outer variables are a means of communication from the SSIS package to the DTS package it executes using late binding (the outer variable is evaluated before the DTS package is executed and not earlier).

We map the outer variables from the SSIS package as global variables to the DTS package. E.g. if you add an outer variable called User::mystring variable to the list, we strip the namespace (because DTS does not know about namespaces) and pass it as a global variable named mystring to the DTS package.

|||Thanks, that helped my understanding a lot. I was able to implement the same.