Methods For Converting a Stored Procedure

  • Eli Leiba

    SSC-Addicted

    Points: 461

    Comments posted to this topic are about the item Methods For Converting a Stored Procedure

  • RamakrishnaMothukuri

    SSC Rookie

    Points: 28

    What are the advanatages of using this method over the standard method of Insert and Exec ?

  • sanjarani

    SSC Veteran

    Points: 259

    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

    SSChampion

    Points: 10358

    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

    Valued Member

    Points: 50

    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

    SSChampion

    Points: 10358

    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

    SSC-Addicted

    Points: 445

    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

    Hall of Fame

    Points: 3098

    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

  • David McKinney

    SSChampion

    Points: 10358

    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

    SSCommitted

    Points: 1815

    What are the security implications of making the configuration change for allowing ad hoc distributed queries?

  • clementhuge

    SSC-Addicted

    Points: 445

    When I deployed the code, the linked server was not recognized (the name of my server is ServerName\Instance1.

    It seems that the code does not work with named instance.

    I set up a linked server names .\Instance1 and it worked (I added the parameter @sLinkedServer Varchar(80) to the stored procedure.

    Clement

  • clementhuge

    SSC-Addicted

    Points: 445

    I have performed recently a PCC-DSS Sql server audit and one of the best practice on the list was to avoid distributed transactions.

    therefore if your industry requires stringent security rules, you have to make sure that allowing server properties are not against your industry rules.

    Clement

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    we just migrated a server to new hardware and SQL 2005 from 2000 and got rid of a lot of openrowset code in the process. One reason is there is a bug in SQL 2005 SP2 and earlier. I forgot the CU where they fixed it, but it was due to a PSS case we opened. almost 2 years ago we bought a new reporting server and set it up according to security best practices. no domain administrator account to run the service.

    a few months later people complain that they can't see some indexes in a database. we open a case and that was about the time we find that using a linked server to that server was flaky and didn't work a lot of times with integrated authentication. turns out there was a bug if you install SQL according to BOL instructions then some things won't work.

  • cwalker-700550

    Grasshopper

    Points: 13

    I'm stupid. you are using MASTER and I made a best practice comment on the assumption you weren't using MASTER. I should stop answering the phone when I read these columns. :hehe:

    Just an aside, I never name my stored procedures with sp_XXX as this is normally reserved to MASTER.

    It also causes additional searches and added i/o

    It will first search MASTER for the procedure, if not found it will then acquire a exclusive COMPILE lock to perform a second search of the other databases

    Also if the procedure has the same name as an sp_XXX procedure in MASTER your stored procedure will never execute

  • Tom Garth

    SSCertifiable

    Points: 6173

    David McKinney (10/29/2009)


    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?

    I think that "ruedifuchs" meant that a Table Valued Function does provide a recordset including the table definition and could probably replace the stored procedure and be called directly with a select statement.

    Some advantages are...

    It can use stored procedures to populate it.

    It can utilize temp tables where views cannot.

    It can have parameters.

    It will present the data definition of it's output to a calling program where a stored procedure will not.

    It can be used in the FROM clause of a view if it doesn't have parameters.

    Some disadvantages are...

    Excel will not allow you to use it for a datasource.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply