Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Select from stored procedure Expand / Collapse
Posted Wednesday, December 14, 2011 9:32 AM


Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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

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


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

Post #1221816
Posted Thursday, December 15, 2011 10:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 16, 2014 10:28 AM
Points: 4, Visits: 33
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

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

create procedure dbo.proc_simple
select LocationId, [Name], rowguid from dbo.Location

select * from openrowset('SQLOLEDB', 'Trusted_Connection=Yes;Server=(local);Database=AdventureWorks2000', 'exec dbo.proc_simple')
-- 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;

Post #1222599
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse