Select from stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rafal S. » Select from stored procedure

12 posts, Page 2 of 2

««1 2

Select from stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

Steven Willis

Posted Wednesday, December 14, 2011 9:32 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:19 AM
Points: 283, Visits: 1,239

Eric M Russell (12/14/2011)

I think more explanation is needed about what your script is demonstrating. Is your specific intent to demonstrate how to select from a "remote" stored procedure?

When I want to return a filtered or transformed result from a stored procedure, I will insert into a table variable or temp table like so. This seems cleaner, because it required no connection string.

create procedure dbo.proc_simple as
   select top 5 object_id, name from sys.objects
go

declare @objects table ( obj_id int, obj_name varchar(180) );

insert into @objects ( obj_id, obj_name )
   exec dbo.proc_simple;

select * from @objects;

obj_id      obj_name
----------- --------------------
4           sysrowsetcolumns
5           sysrowsets
7           sysallocunits
8           sysfiles1
13          syshobtcolumns

+1

This is the way I do it. Then you have a table you can join with too if necessary.

Post #1221816

Karl-845976

Posted Thursday, December 15, 2011 10:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:36 AM
Points: 3, Visits: 15

We have many situation where we run into this issue using an old "API" that used rowsets for results from stored procs.
Our solution, which does not require a connection string, is as follows:

use AdventureWorks2000
go

if exists(select * from sysobjects where id = object_id('dbo.proc_simple') and type = 'P')
	drop procedure dbo.proc_simple
go

create procedure dbo.proc_simple
as
begin
	select LocationId, [Name], rowguid from  dbo.Location
end
go

select * from openrowset('SQLOLEDB', 'Trusted_Connection=Yes;Server=(local);Database=AdventureWorks2000', 'exec dbo.proc_simple')
go
-- Declare target variable
DECLARE @LocationID smallint;
-- Create Temp table to receive resultset (Table variables will not work)
CREATE TABLE #TmpProcSimple (
	LocationId	smalllint,
	[Name] varchar(50),
	rowguid uniqueidentifier
)
-- Execute into Temp table
INSERT INTO #TmpProcSimple
	EXECUTE dbo.proc_simple;
-- Select value from Temp table
SELECT @LocationID = LocationID
FROM #TmpProcSimple;
GO

Post #1222599

« Prev Topic | Next Topic »

12 posts, Page 2 of 2

««1 2

Permissions