SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to get Store Proc result into Variable		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IK1972
Starting Member

41 Posts
Posted - 10/16/2012 :  23:00:48  Show Profile  Reply with Quote

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

India
47069 Posts
Posted - 10/16/2012 :  23:28:25  Show Profile  Reply with Quote
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
Starting Member

41 Posts
Posted - 10/17/2012 :  12:32:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/17/2012 :  13:32:33  Show Profile  Reply with Quote
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.
Edited by - sunitabeck on 10/17/2012 13:32:58
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 10/17/2012 :  23:18:41  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000