SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Methods For Converting a Stored Procedure


Methods For Converting a Stored Procedure

Author
Message
Eli Leiba
Eli Leiba
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 19
Comments posted to this topic are about the item Methods For Converting a Stored Procedure
RamakrishnaMothukuri
RamakrishnaMothukuri
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 21
What are the advanatages of using this method over the standard method of Insert and Exec ?
sanjarani
sanjarani
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 303
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.
David McKinney
David McKinney
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4489 Visits: 2094
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.
ruedifuchs
ruedifuchs
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 27
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?
David McKinney
David McKinney
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4489 Visits: 2094
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?
clementhuge
clementhuge
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 301
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
thierry.vandurme
thierry.vandurme
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1741 Visits: 573
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 Wink)

The examples are ok for 2000. 2005 and up: better to use DMV's I suppose
David McKinney
David McKinney
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4489 Visits: 2094
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?
drnetwork
drnetwork
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 82
What are the security implications of making the configuration change for allowing ad hoc distributed queries?



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