Output value from sproc

Question

Post reply

Output value from sproc

Steve Johnson

Default port

Points: 1404
More actions
June 11, 2004 at 12:03 pm

#161459

I am trying to output a value from a sproc to a global variable in a Execute Sql Task. When I execute the task it will allow me to pass the input variable in but it wont let me capture the output of the sproc and send it to my global variable. Here is the sproc:
alter procedure prRecCount @vTbl varchar(25) as
declare
@sql varchar(2000),
@vCnt int
set @sql = 'select cnt from openquery( {servername}, ''select count(*) cnt from ' + @vTbl +''')'
execute(@sql)
return
go
here is the code that I am executing from the Execute Sql Task:
execute prRecCount ?
Thanks

Steve Johnson
June 14, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

kailash sahu SSC Veteran Points: 229 More actions · Answer 1

try declaring the varibale explicitly as "output" variable (see below) and check if it is working for you?

alter procedure prRecCount @vTbl varchar(25) as

declare

@sql varchar(2000) output,

@vCnt int

set @sql = 'select cnt from openquery( {servername}, ''select count(*) cnt from ' + @vTbl +''')'

execute(@sql)

return

go

Hope this help!!

draisin SSC Enthusiast Points: 112 More actions · Answer 2

Use a variable to store the sp's returned value, so long as the sp returns a single value (not multiple values)?

For example:

exec @recCount = prRecCount ?

David Burrows SSC Guru Points: 65138 More actions · Answer 3

Try this

alter procedure prRecCount @vTbl varchar(25) as

declare

@sql varchar(2000),

@vCnt int

set @sql = 'select @vCnt = cnt from openquery( {servername},''select count(*) cnt from ' + @vTbl +''')'

exec sp_executesql @sql, N'@vCnt output', @vCnt output

return @vCnt

go

declare @reccount int

exec @reccount = prRecCount 'tablename'

Far away is close at hand in the images of elsewhere.
Anon.

Steve Johnson Default port Points: 1404 More actions · Answer 4

Here is how I was able to address the issue that I was having. I ended up creating a sproc that altered another sproc.

1. sproc:

CREATE procedure prAlterprRecCount @vTbl varchar(25) as

declare

@sql2 varchar(2000)

set @sql2 = 'alter procedure prRecCount as declare @sql varchar(2000) set @sql = ''select cnt from openquery( {Server Name}, ''''select count(*) cnt from ' + @vTbl

set @sql2 = @sql2 + ''''')'' execute(@sql)'

execute (@sql2)

GO

2. sproc:

CREATE procedure prRecCount as declare @sql varchar(2000) set @sql = 'select cnt from openquery( tatooine, ''select count(*) cnt from pub.customer'')' execute(@sql)

GO

We were able to create a DTS that we can pass a table name into and get a record count from a linked server database .

Thank you all for your input it is always greatly appriciated

Steve Johnson