March 4, 2008 at 8:53 pm
Comments posted to this topic are about the item Select from stored procedure
April 23, 2008 at 2:19 am
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 insert / exec, but I used CLR to achieve it, providing a connection key mapped to a small table holding connection information.
-- Gianluca Sartori
April 23, 2008 at 10:00 pm
Unrelated suggestion or correction:
I noticed you used this statement to remove the procedure before recreating it
if exists(select * from sysobjects where id = object_id('dbo.proc_simple') and type = 'P')
drop procedure dbo.proc_simple
go
I'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_simple
go
April 24, 2008 at 12:28 am
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.
April 28, 2008 at 1:08 pm
Er, I hope it is not a dumb quesiton, but I do not see what go again over just running the stored proc...
July 9, 2008 at 2:56 pm
I use this method when I need a dynamic solution:
use AdventureWorks2000
go
if exists(select * from sysobjects where id = object_id('dbo.proc_dynamic') and type = 'P')
drop procedure dbo.proc_dynamic
go
create procedure dbo.proc_dynamic
@name varchar(50)
as
begin
select LocationId, [Name], rowguid from dbo.Location where [Name] = @name
end
go
DECLARE @SELECT varchar(1000),
@NAMEvarchar(50)
SET @NAME = 'Location'
SET @SELECT = 'SELECT * FROM OPENQUERY([' + @@SERVERNAME + '],''exec AdventureWorks2000.dbo.proc_dynamic ''''' + @Name + ''''' '')'
EXEC(@SELECT)
go
July 24, 2008 at 6:28 pm
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]
Riz
August 6, 2008 at 7:12 am
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.
August 6, 2008 at 9:42 am
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.
December 14, 2011 at 7:27 am
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
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 14, 2011 at 9:32 am
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 asselect 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.
December 15, 2011 at 10:13 am
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 (
LocationIdsmalllint,
[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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply