I have a stored procedure (PROC1) that calls another stored procedure (PROC2
)
multiple times. Both procedures have an output parameter and I am wondering
if I can take the multiple output parameters from PROC2 and combine them int
o
the parameter that is output for PROC1. Example...
Say that PROC2 is called 4 times and the output from each call is this
1
2
3
4
I want the output of PROC1 to be 1234. Any help is appreciated.
ThanksYes, create a varchar() column, of appropriate size, and each time you call
the stored proc (Proc2) append the return value to the VarChar variable
Now, the output of the stored proc can be in an output parameter, or just
the return value... WHich is it? If it's an Output parameter
Declare @.Var VarChar(10)
Set @.Var = '' -- Set as empty string to avoid Null propagation issues
Exec PROC2 ..., <@.OutputParam OUTPUT, ...
Select @.Var = @.Var + @.OutputParam
-- ---
Exec PROC2 ..., <@.OutputParam OUTPUT, ...
Select @.Var = @.Var + @.OutputParam
-- ---
Exec PROC2 ..., <@.OutputParam OUTPUT, ...
Select @.Var = @.Var + @.OutputParam
-- ---
Exec PROC2 ..., <@.OutputParam OUTPUT, ...
Select @.Var = @.Var + @.OutputParam
-- ---
"Andy" wrote:
> I have a stored procedure (PROC1) that calls another stored procedure (PRO
C2)
> multiple times. Both procedures have an output parameter and I am wonderi
ng
> if I can take the multiple output parameters from PROC2 and combine them i
nto
> the parameter that is output for PROC1. Example...
> Say that PROC2 is called 4 times and the output from each call is this
> 1
> 2
> 3
> 4
> I want the output of PROC1 to be 1234. Any help is appreciated.
> Thanks|||Example:
use northwind
go
create procedure proc1
@.p1 int output
as
set nocount on
set @.p1 = (select top 1 orderid from dbo.orders order by newid())
return @.@.error
go
create procedure proc2
@.p1 varchar(128) output
as
set nocount on
declare @.i int
declare @.j int
declare @.s varchar(128)
set @.i = 1
set @.s = ''
while @.i <= 4
begin
set @.j = null
exec proc1 @.J output
if @.j is not null
set @.s = @.s + case when @.s > '' then '|' else '' end + ltrim(@.j)
set @.i = @.i + 1
end
set @.p1 = @.s
return 0
go
declare @.s varchar(128)
exec proc2 @.s output
print @.s
go
drop procedure proc2, proc1
go
AMB
"Andy" wrote:
> I have a stored procedure (PROC1) that calls another stored procedure (PRO
C2)
> multiple times. Both procedures have an output parameter and I am wonderi
ng
> if I can take the multiple output parameters from PROC2 and combine them i
nto
> the parameter that is output for PROC1. Example...
> Say that PROC2 is called 4 times and the output from each call is this
> 1
> 2
> 3
> 4
> I want the output of PROC1 to be 1234. Any help is appreciated.
> Thanks|||Thanks a lot for your help on this. I should have mentioned that I am using
dynamic SQL. Here is a sample of my code. I have declared all of the
variables in my code and am trying to have @.p as my output. I know you
cannot use Set in dynamic SQL but am hoping I have other options.
@.BottomLimit and @.TopLimit are .5
@.TableName is the name of the table
@.PreviousLoad and @.CurrentLoad are passed in as '200408' and '200409'
The tables I am looking at are partitioned and I am comparing last month's
row count to this month's. Just to give you a better idea of the code below
.
Set @.SQL = 'Set @.p = (Select Case When(((SELECT Count(*) FROM ' + @.TableName
+ @.CurrentLoad + ' ) Between
( SELECT Count(*) - Count(*) * ' + @.BottomLimit + ' FROM ' + @.TableName +
@.PreviousLoad + ' ) AND
( SELECT Count(*) + Count(*) * ' + @.TopLimit + ' FROM ' + @.TableName +
@.PreviousLoad + ' )))
THEN ''QC has passed for ' + @.TableName + @.CurrentLoad + '''
ELSE ''QC has failed for ' + @.TableName + @.CurrentLoad + '''END)'
exec(@.SQL)
Any help you can provide is greatly appreciated!!!!!
"Alejandro Mesa" wrote:
> Example:
> use northwind
> go
>
> create procedure proc1
> @.p1 int output
> as
> set nocount on
> set @.p1 = (select top 1 orderid from dbo.orders order by newid())
> return @.@.error
> go
> create procedure proc2
> @.p1 varchar(128) output
> as
> set nocount on
> declare @.i int
> declare @.j int
> declare @.s varchar(128)
> set @.i = 1
> set @.s = ''
> while @.i <= 4
> begin
> set @.j = null
> exec proc1 @.J output
> if @.j is not null
> set @.s = @.s + case when @.s > '' then '|' else '' end + ltrim(@.j)
> set @.i = @.i + 1
> end
> set @.p1 = @.s
> return 0
> go
> declare @.s varchar(128)
> exec proc2 @.s output
> print @.s
> go
> drop procedure proc2, proc1
> go
>
> AMB
>
> "Andy" wrote:
>|||andy,
if you absolutely have to use dynamic SQL, then you need to switch from
using "EXEC" to using a built-in System stored proc called sp_ExecuteSQL()
... Look it upo in the Books On LIne.
When you use EXEC, the called stored proc runs in it's own context, and the
value of Output parameters is not available to the caller stored proc. If
you use so_ExecuteSQL, then toy CAN define an output Parameter in the Caller
SP whose value will be modified by the Called SP, and will later be avaliabl
e
back in the caller after the SP runs...
"Andy" wrote:
> Thanks a lot for your help on this. I should have mentioned that I am usi
ng
> dynamic SQL. Here is a sample of my code. I have declared all of the
> variables in my code and am trying to have @.p as my output. I know you
> cannot use Set in dynamic SQL but am hoping I have other options.
> @.BottomLimit and @.TopLimit are .5
> @.TableName is the name of the table
> @.PreviousLoad and @.CurrentLoad are passed in as '200408' and '200409'
> The tables I am looking at are partitioned and I am comparing last month's
> row count to this month's. Just to give you a better idea of the code bel
ow.
> Set @.SQL = 'Set @.p = (Select Case When(((SELECT Count(*) FROM ' + @.TableNa
me
> + @.CurrentLoad + ' ) Between
> ( SELECT Count(*) - Count(*) * ' + @.BottomLimit + ' FROM ' + @.TableName
+
> @.PreviousLoad + ' ) AND
> ( SELECT Count(*) + Count(*) * ' + @.TopLimit + ' FROM ' + @.TableName +
> @.PreviousLoad + ' )))
> THEN ''QC has passed for ' + @.TableName + @.CurrentLoad + '''
> ELSE ''QC has failed for ' + @.TableName + @.CurrentLoad + '''END)'
> exec(@.SQL)
>
> Any help you can provide is greatly appreciated!!!!!
> "Alejandro Mesa" wrote:
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment