Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select from stored procedure


Select from stored procedure

Author
Message
skra
skra
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 228
Comments posted to this topic are about the item Select from stored procedure
Gianluca Sartori
Gianluca Sartori
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6528 Visits: 13322
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
tdennis-674196
tdennis-674196
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 138
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
skra
skra
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 228
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.
J-440512
J-440512
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 949
Er, I hope it is not a dumb quesiton, but I do not see what go again over just running the stored proc...
Michael Silva
Michael Silva
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 38
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
Riz Gulamhussein
Riz Gulamhussein
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 31
To connect to remote server:

select * from openrowset('SQLOLEDB', 'Uid=myUsername;Pwd=myPassword;Server=ServerNameOrAddress;Database=AdventureWorks2000', 'exec dbo.proc_simple')
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3801 Visits: 3276
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.

I run on tuttopodismo
J-440512
J-440512
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 949
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.
Eric M Russell
Eric M Russell
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6100 Visits: 10009
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



"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search