Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to get Store Proc result into Variable

Author  Topic 

IK1972

56 Posts

Posted - 2012-10-16 : 23:00:48

Hi,

I have one store procedure like below and I want to run this in another Store Procedure and want to get the "TestId" in variable so I can able to use that later. I'm not able to update the store procedure to return or add the output parameter.

Please let me know if this is possible and if yes then how I can do that.

create procedure dbo.test
@Param1 varchar(50),
@Param2 varchar(50)
as
begin
set nocount on

insert into dbo.test
values ('a','b')

select scope_identity() as TestId
end

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-16 : 23:28:25
then only option is to use a temporary table and use

INSERT #Temp
EXEC dbo.test val1,val2

to get returned id

didnt understand why you cant add return or output parameter though!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

IK1972

56 Posts

Posted - 2012-10-17 : 12:32:51
Is it possible to get Store Proc output into #temp table with out creating table structure. We have hundred of SPs and I need to run time by time through query analyzer and then analysis the data and filter some data and then publish the report. Its hard to create temp table every time first.


Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-17 : 13:32:33
You can use OPENROWSET. In your case, it would be like this:
SELECT * INTO #temp 
FROM OPENROWSET(
'SQLNCLI',
'Server=(local);Trusted_Connection=yes',
'EXEC YourDatabaseNameHere.dbo.test'
)
If not already enabled, you will need to enable Ad Hoc Distributed Queries for this to work. To do this:
sp_configure 'Show Advanced Options', 1
GO

RECONFIGURE
GO

sp_configure 'Ad Hoc Distributed Queries', 1
GO

RECONFIGURE
GO
I don't really use this - it is sort of a workaround for a problem that should be avoided, in my opinion. But it is there if you want to use it.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-17 : 23:18:41
i dont using distributed query option for a scenario like this.
I would go ahead and try to tweak stored procedure to return value using OUTPUT operator

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -