November 14, 2016 at 11:14 am
Comments posted to this topic are about the item Change Stored Procedure Automaticly
November 22, 2016 at 12:10 am
Nice feature using dbo.sp_HelpText.
Note, that all CREATE is changed to ALTER!
November 22, 2016 at 4:10 am
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
Good reminder about sp_HelpText.
November 22, 2016 at 7:01 am
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
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy