Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Select from stored procedure Expand / Collapse
Author
Message
Posted Tuesday, March 04, 2008 8:53 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, May 09, 2013 12:41 AM
Points: 711, Visits: 209
Comments posted to this topic are about the item Select from stored procedure
Post #464188
Posted Wednesday, April 23, 2008 2:19 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067
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.



Get your two-cent-answer quickly
The Spaghetti DBA
Post #489099
Posted Wednesday, April 23, 2008 10:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 5:06 PM
Points: 16, Visits: 129
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
Post #489662
Posted Thursday, April 24, 2008 12:28 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, May 09, 2013 12:41 AM
Points: 711, Visits: 209
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.
Post #489689
Posted Monday, April 28, 2008 1:08 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, September 14, 2009 5:22 AM
Points: 438, Visits: 918
Er, I hope it is not a dumb quesiton, but I do not see what go again over just running the stored proc...
Post #491668
Posted Wednesday, July 09, 2008 2:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 03, 2013 2:55 PM
Points: 8, Visits: 30
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),
@NAME varchar(50)
SET @NAME = 'Location'
SET @SELECT = 'SELECT * FROM OPENQUERY([' + @@SERVERNAME + '],''exec AdventureWorks2000.dbo.proc_dynamic ''''' + @Name + ''''' '')'

EXEC(@SELECT)
go
Post #531232
Posted Thursday, July 24, 2008 6:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 16, 2013 2:51 AM
Points: 8, Visits: 31
To connect to remote server:

select * from openrowset('SQLOLEDB', 'Uid=myUsername;Pwd=myPassword;Server=ServerNameOrAddress;Database=AdventureWorks2000', 'exec dbo.proc_simple')
Post #540637
Posted Wednesday, August 06, 2008 7:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:04 AM
Points: 1,968, Visits: 1,819
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.
Post #547478
Posted Wednesday, August 06, 2008 9:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, September 14, 2009 5:22 AM
Points: 438, Visits: 918
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

and OBJECTPROPERTY(id, N'IsProcedure') = 1)

instead of

and type = 'P'

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.
Post #547653
Posted Wednesday, December 14, 2011 7:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 1,162, Visits: 3,331
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





"Wise people understand the 10,000 things without going to each one.
They know them without having to look at each one,
and they transform all without acting on each one." - The Tao Te Ching: Verse 47
Post #1221672
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse