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 1234»»»

Methods For Converting a Stored Procedure Expand / Collapse
Author
Message
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: Monday, February 17, 2014 2:05 AM
Points: 47, Visits: 18
Comments posted to this topic are about the item Methods For Converting a Stored Procedure
Post #810479
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: Wednesday, August 20, 2014 3:59 AM
Points: 2, Visits: 21
What are the advanatages of using this method over the standard method of Insert and Exec ?
Post #810522
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, September 26, 2014 12:24 AM
Points: 64, Visits: 218
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
Posted Thursday, October 29, 2009 2:13 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:54 AM
Points: 648, Visits: 1,876
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
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: Friday, January 17, 2014 4:57 AM
Points: 2, Visits: 26
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
Posted Thursday, October 29, 2009 3:04 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:54 AM
Points: 648, Visits: 1,876
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
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: Sunday, August 31, 2014 10:04 AM
Points: 89, Visits: 294
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
Posted Thursday, October 29, 2009 4:27 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:12 AM
Points: 724, Visits: 405
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
Posted Thursday, October 29, 2009 4:29 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:54 AM
Points: 648, Visits: 1,876
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
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 1234»»»

Permissions Expand / Collapse