Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Eli Leiba » Methods For Converting a Stored Procedure
Add to briefcase
38 posts, Page 1 of 4
1234»»»

Methods For Converting a Stored Procedure Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Eli Leiba
Posted Wednesday, October 28, 2009 9:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 12, 2013 6:04 AM
Points: 47, Visits: 17
Comments posted to this topic are about the item Methods For Converting a Stored Procedure
Post #810479
RamakrishnaMothukuri
Posted Thursday, October 29, 2009 12:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:18 AM
Points: 2, Visits: 17
What are the advanatages of using this method over the standard method of Insert and Exec ?
Post #810522
sanjarani
Posted Thursday, October 29, 2009 2:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 14, 2013 6:07 AM
Points: 57, Visits: 162
Once a view is created, which can be created dynamically in a Stored Procedure, It can be used to join to other Views and tables.
I don't see the advantages of your method. Perhaps you can state the specific uses of this methods and its advantages over other ways of accessing data.
Post #810556
David McKinney
Posted Thursday, October 29, 2009 2:13 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:02 AM
Points: 583, Visits: 1,612
I think the key information in the article is that you can use OPENROWSET to turn the output of a stored proc to a recordset.

This message got a bit lost with all the stuff relating to creating a view with dynamic sql.

The article could have been structured differently, to deliver the message first strongly and clearly i.e HOW TO DO IT, and WHY THIS IS USEFUL. (A discussion about the difficulties in sorting and filtering the output of a stored procedure.)

Then, as a 'bonus', the stuff about automating the view creation, could have been tagged on.

But I'm giving you five stars anyway, just for including the code for OPENROWSET.

Thanks,

David.
Post #810558
ruedifuchs
Posted Thursday, October 29, 2009 2:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 17, 2013 5:33 AM
Points: 2, Visits: 25
I agree, the code for using OPENROWSET is very interesting.

But since the stored procedure has to return a result set, what is the advantage over using a table-valued function?
Post #810570
David McKinney
Posted Thursday, October 29, 2009 3:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:02 AM
Points: 583, Visits: 1,612
ruedifuchs (10/29/2009)
I agree, the code for using OPENROWSET is very interesting.

But since the stored procedure has to return a result set, what is the advantage over using a table-valued function?


I'm not sure if I understand your question, ruedi, but here goes.

The examples given, sp_who, sp_lock, are SYSTEM stored procedures (i.e. not yours!) - they are not table-valued functions. The data returned by these is very useful - but difficult to use effectively (filter, join, sort etc) unless you can put it into a recordset of some sort (table / view / CTE etc). The OPENROWSET code enables you to do that. Short of rewriting the entire sp as a table-valued function, I'm not sure how a table-valued function can help you?

Does this clarify?
Post #810577
clementhuge
Posted Thursday, October 29, 2009 3:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:28 PM
Points: 89, Visits: 288
The stored procedure is interesting. However it does require a lot of prerequisites and open security holes that PCI-DSS would not allow (for example like distributed transaction).
Moreover, the stored procedure does not work with sp_listprocess or sp_who2 as they have duplicate column names.

Also, there needs to have the linked server set up in prerequisites (do not forget the instance name).

The stored procedure is promising but lack some improvements but I think it is a great start.

Clement
Post #810581
thierry.vandurme
Posted Thursday, October 29, 2009 4:27 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, June 17, 2013 8:50 AM
Points: 583, Visits: 291
The OPENROWSET code and the examples are interesting, but I wonder about performance.
BTW, I'm always reluctant to change system parameters (in this case ad hoc dist queries) to achieve a goal. I would rather search for alternatives first (I'm far from an expert though ;))

The examples are ok for 2000. 2005 and up: better to use DMV's I suppose
Post #810622
David McKinney
Posted Thursday, October 29, 2009 4:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:02 AM
Points: 583, Visits: 1,612
clementhuge (10/29/2009)
The stored procedure is interesting. However it does require a lot of prerequisites and open security holes that PCI-DSS would not allow (for example like distributed transaction).
Moreover, the stored procedure does not work with sp_listprocess or sp_who2 as they have duplicate column names.

Also, there needs to have the linked server set up in prerequisites (do not forget the instance name).

The stored procedure is promising but lack some improvements but I think it is a great start.


Good point about the duplicate column names in sp_who2 etc! But I don't get your point about the linked server?
Post #810623
drnetwork
Posted Thursday, October 29, 2009 4:34 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 3:38 AM
Points: 445, Visits: 82
What are the security implications of making the configuration change for allowing ad hoc distributed queries?


Post #810626
« Prev Topic | Next Topic »

Add to briefcase
38 posts, Page 1 of 4
1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.