Post reply

Change Stored Procedure Automaticly

  • November 14, 2016 at 11:14 am

    #401046

    Comments posted to this topic are about the item Change Stored Procedure Automaticly

  • November 22, 2016 at 12:10 am

    #1913912

    Nice feature using dbo.sp_HelpText.

    Note, that all CREATE is changed to ALTER!

  • November 22, 2016 at 4:10 am

    #1913941

    you can replace that CURSOR with

    --Add variable all text of Proc, no need for CURSORS

    DECLARE @QueryAll AS VARCHAR(MAX)

    SELECT@QueryAll = COALESCE(@QueryAll,' ') + ProcTxt

    FROM#TempProcScript

  • November 22, 2016 at 6:48 am

    #1913982

    Good reminder about sp_HelpText.

  • November 22, 2016 at 7:01 am

    #1913990

    While this is a good illustration of how to leverage the output of sp_helptext, I would caution against using this in a production environment or without the benefit of a test platform. Many stored procedures I have seen perform the same activity against different record-sets depending on a condition, and these stored procedures will often contain all of these different updates together. So if a set meets condition A, then a column value is set to X, and if the next recordset meets condition B, then the column value is set to Y, etc.

    Relying up a quick update like this to make a change may have a much larger impact upon your code if you are not careful. A quick review of the code before using this tool would help prevent this, but also a more thorough check of the code afterwards. Always look for possiblilities that there are unintended results.

    An analog to this problem I am describing would be if you were using the "Find and Replace' tools within SSMS . For example, you are editing a comma-separated listing of address data to be placed into a table, and you decide to replace occurrences of ' ' with an empty field ''. Instead of typing the field exactly as I have shown it, you type ' ' reasoning that this will capture all of the shorter blank strings and save you the work of having to search for all of them not captured by searching for ' '. It works! But the unintended consequence is that all strings of words in your CSV are now one big long word.

    Sorryforthelongwindedexample

    jeffbennett

  • November 22, 2016 at 9:39 pm

    #1914153

    jonas.gunnarsson 52434 (11/22/2016)

    Nice feature using dbo.sp_HelpText.

    Note, that all CREATE is changed to ALTER!

    My proc used to contain CREATE TABLE and now says ALTER TABLE. Is this script why? (:

    ATBCharles Kincaid

Viewing 6 posts - 1 through 5 (of 5 total)

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