SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Rafal S. / Select from stored procedure / Latest Posts

RE: Select from stored procedure

Karl-845976 — Thu, 15 Dec 2011 10:13:28 GMT

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:[code="sql"]use AdventureWorks2000goif exists(select * from sysobjects where id = object_id('dbo.proc_simple') and type = 'P') drop procedure dbo.proc_simplegocreate procedure dbo.proc_simpleasbegin select LocationId, [Name], rowguid from dbo.Locationendgoselect * from openrowset('SQLOLEDB', 'Trusted_Connection=Yes;Server=(local);Database=AdventureWorks2000', 'exec dbo.proc_simple')go-- Declare target variableDECLARE @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 tableINSERT INTO #TmpProcSimple EXECUTE dbo.proc_simple;-- Select value from Temp tableSELECT @LocationID = LocationIDFROM #TmpProcSimple;GO[/code]

RE: Select from stored procedure

Steven Willis — Wed, 14 Dec 2011 09:32:41 GMT

[quote][b]Eric M Russell (12/14/2011)[/b][hr]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. [code="sql"]create procedure dbo.proc_simple as select top 5 object_id, name from sys.objectsgodeclare @objects table ( obj_id int, obj_name varchar(180) );insert into @objects ( obj_id, obj_name ) exec dbo.proc_simple;select * from @objects;[/code][code="plain"]obj_id obj_name----------- --------------------4 sysrowsetcolumns5 sysrowsets7 sysallocunits8 sysfiles113 syshobtcolumns[/code][/quote]+1This is the way I do it. Then you have a table you can join with too if necessary.

RE: Select from stored procedure

Eric M Russell — Wed, 14 Dec 2011 07:27:59 GMT

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. [code="sql"]create procedure dbo.proc_simple as select top 5 object_id, name from sys.objectsgodeclare @objects table ( obj_id int, obj_name varchar(180) );insert into @objects ( obj_id, obj_name ) exec dbo.proc_simple;select * from @objects;[/code][code="plain"]obj_id obj_name----------- --------------------4 sysrowsetcolumns5 sysrowsets7 sysallocunits8 sysfiles113 syshobtcolumns[/code]

RE: Select from stored procedure

J-440512 — Wed, 06 Aug 2008 09:42:45 GMT

The first statement is very close to what SQL Server generates when you script a procedure, except that the last condition in the WHERE clause is [font="Courier New"] and OBJECTPROPERTY(id, N'IsProcedure') = 1)[/font]instead of [font="Courier New"]and type = 'P'[/font]I was just using this statement out of habit, did not think of doing it in a simpler way, such as yours. Nice one.I love this forum, learning something new everyday.

RE: Select from stored procedure

Carlo Romagnano — Wed, 06 Aug 2008 07:12:29 GMT

Good idea! But just for few rows result set. Else it takes too long to run if put the result set in join with another table.

RE: Select from stored procedure

Riz Gulamhussein — Thu, 24 Jul 2008 18:28:56 GMT

To connect to remote server:[font="Courier New"]select * from openrowset('SQLOLEDB', 'Uid=myUsername;Pwd=myPassword;Server=ServerNameOrAddress;Database=AdventureWorks2000', 'exec dbo.proc_simple')[/font]

RE: Select from stored procedure

Michael Silva — Wed, 09 Jul 2008 14:56:28 GMT

I use this method when I need a dynamic solution:use AdventureWorks2000goif exists(select * from sysobjects where id = object_id('dbo.proc_dynamic') and type = 'P') drop procedure dbo.proc_dynamicgocreate procedure dbo.proc_dynamic @name varchar(50)asbegin select LocationId, [Name], rowguid from dbo.Location where [Name] = @nameendgoDECLARE @SELECT varchar(1000), @NAME varchar(50)SET @NAME = 'Location'SET @SELECT = 'SELECT * FROM OPENQUERY([' + @@SERVERNAME + '],''exec AdventureWorks2000.dbo.proc_dynamic ''''' + @Name + ''''' '')'EXEC(@SELECT)go

RE: Select from stored procedure

J-440512 — Mon, 28 Apr 2008 13:08:10 GMT

Er, I hope it is not a dumb quesiton, but I do not see what go again over just running the stored proc...

RE: Select from stored procedure

skra — Thu, 24 Apr 2008 00:28:35 GMT

IMHO not.Your way seems to me to be more "clean" and I think that it is better than mine (which is perhaps unnecessarily to sofisticated than it should).It is just my bad habit to drop objects in this way.

RE: Select from stored procedure

tdennis-674196 — Wed, 23 Apr 2008 22:00:55 GMT

Unrelated suggestion or correction:I noticed you used this statement to remove the procedure before recreating itif exists(select * from sysobjects where id = object_id('dbo.proc_simple') and type = 'P') drop procedure dbo.proc_simplegoI've always been using this instead. Is what I'm doing wrong in any way?if object_id('dbo.proc_simple','P') is not null drop procedure dbo.proc_simplego

RE: Select from stored procedure

spaghettidba — Wed, 23 Apr 2008 02:19:59 GMT

Nice way to get it to work!I don't see why it shouldn't be used in production environment, it just needs to be a bit refined... I wouldn't include connection strings in the statement, but I don't see it nor dangerous nor unstable, connection strings apart.I coded something similar a couple of years ago to work around limitations on [code]insert / exec[/code], but I used CLR to achieve it, providing a connection key mapped to a small table holding connection information.

Select from stored procedure

skra — Tue, 04 Mar 2008 20:53:20 GMT

Comments posted to this topic are about the item [B]Select from stored procedure[/B]