August 14, 2002 at 10:21 pm
I am trying to execute a stored procedure from an Execute SQL Task. The stored procedure has two input parameters and one output parameter. I am having a problem getting the syntax correct for executing the sproc and was hoping someone could steer me in the right direction. The only example I could find was for executing a sproc with an output parameter but no input parameters. I am thinking the syntax should be something like:
DECLARE @myval int
Exec spTest ?,?, @output = @myval OUTPUT
Select @myval as myval
Anyway, any help would be appreciated!
Many thanks,
Michael
Michael Weiss
Michael Weiss
August 14, 2002 at 11:17 pm
Sorry, I should have added that I want to map the output parameter value from the sproc to a global variable...I also realize I can do this from a Dynamic Properties task but there should be a way to do it via an Execute SQL task as well...
tia,
Michael
Michael Weiss
Michael Weiss
August 15, 2002 at 2:15 pm
I don't think you'll be able to get the value from the SP into the global variable. Unless... Thinking here... You execute the dts package from within a stored procedure. Your master procedure would first call your other stored procedure and retrieve the value. Then you can pass the value to the global variable in the dts package when you call DTS run from xp_cmdshell.
Good luck,
John
Oh yeah. Here's the syntax you were looking for:
create procedure _test
(
@id int,
@xtype varchar(1),
@name varchar(25) output
)
as
select @name = name from sysobjects where id = @id and xtype = @xtype
return
declare @id int, @xtype varchar(1), @name varchar(25)
set @id = 1
set @xtype = 's'
exec _test @id, @xtype, @name output
print @name
August 15, 2002 at 6:08 pm
Thank you! I will save the example code you provided, but I wimped out and did it through an ActiveX task using ADO and a command object which I passed the parameters to...
Thank you,
Michael
Michael Weiss
Michael Weiss
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply