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 4, 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: Friday, May 9, 2014 9:03 AM
Points: 711, Visits: 217
Comments posted to this topic are about the item Select from stored procedure
Post #464188
Posted Wednesday, April 23, 2008 2:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 5,014, Visits: 10,517
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #489099
Posted Wednesday, April 23, 2008 10:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 12:54 PM
Points: 16, Visits: 130
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: Friday, May 9, 2014 9:03 AM
Points: 711, Visits: 217
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, July 14, 2014 12:33 PM
Points: 440, Visits: 929
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 9, 2008 2:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 4:23 PM
Points: 8, Visits: 37
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 6, 2008 7:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 2,468, Visits: 2,346
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 6, 2008 9:42 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, July 14, 2014 12:33 PM
Points: 440, Visits: 929
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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 1,605, Visits: 4,595
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


Post #1221672
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse