• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 138
  • Last Modified:

Script changes to stored procedures

Hi,
I have around 40 stored procedures all of them need to be amended, I will need to amend the select statement , my question is how to script the changes I.e. how to automate the changes as the first amendment will be to point to test data then all of them need to be amended again to point to live data.

appreciate your help in advance!
0
UKIT
Asked:
UKIT
2 Solutions
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
If you could give the sample Stored procedure and the before and after how it should look like, it will help experts to suggest.
0
 
UKITAuthor Commented:
Sure,

this is before
ALTER PROCEDURE [dbo].[XXXXX]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT
        BB.MID
      , BB.CD
      , BB.Mf as OMf
      from CM
inner join dbo.ST
on cm.STID = BB.STID

left join dbo.TT
on cm.STTID = tt.TTID

AFTER

ALTER PROCEDURE [dbo].[XXXXX]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT
        BB.MID
      , BB.CD
      , BB.Mf as OMf
======xxxx=====
,NMF as ANMF
=======xxxx=====
    from CM

=================This is the change that has to be scripted ==Here=
INNER JOIN
[server].[dbname].[dbo].[table]
on cm.[MRf]=[omc] collate database_default
=========================================================
inner join dbo.ST
on cm.STID = BB.STID

left join dbo.TT
on cm.STTID = tt.TTID

the changes are in between the ====== signs
0
 
Walter RitzelSenior Software EngineerCommented:
The change I' seeing highlighted is to add a field to the projection of the SELECT and a new inner join.
If you want to automate that, I suggest dynamic SQL statements. In that way, you change these 40 procedures once and will never worry about.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Shaun KlineLead Software EngineerCommented:
I would suggest creating a view that points to the table on the foreign server. Modify all of your stored procedures to use this new view. Then when you are ready to switch to the production table, just modify the view to point to the correct table.
0
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
I did not test this.
This is powershell code, You need to save the following code as file.ps1 and right click the file and click "run with Powershell". It will modify one stored procedure and then stops for you to verify and continue to next procedure.
In the first 2 lines, you enter the right server name and database name.

$inst = "YourServerName"
$database = "DatabaseName"


$srch = "OMf"
$repl = " OMf, NMF as ANMF " 

$src2 = "inner"
$repl2 = " INNER JOIN
[server].[dbname].[dbo].[table]
on cm.[MRf]=[omc] collate database_default
inner "

# Connect to the specified instance                              
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst                              
                               
# Get the specified database where the changes will be made                              
$db = $s.Databases[$database]                              
                               
$procs = $db.StoredProcedures                              
foreach ($proc in $procs) 
{                              
    if($proc.IsSystemObject -eq $False) 
        {                              
            $txt = $proc.TextBody                              
            $chgd = $False                              
                          
            # Check to see if the text of the proc contains the search string and replace it if so                              
            if ($txt.Contains($srch) -eq $True)
             {                              
                $txt = $txt.Replace($srch,$repl)                              
                $chgd = $True                              
             }
             if ($txt.Contains($srch2) -eq $True)
             {                              
                $txt = $txt.Replace($srch2,$repl2)                              
                $chgd = $True                              
             }                              
                               
            # If a change occurred, set the updated TextBody property and alter the proc                              
            if ($chgd = $True) 
             {                              
                $proc.TextBody = $txt                              
                $proc.Alter()                              
             }                              
        }                              
   Read-host "Please check the stored procedure and if you are happy Press Enter Key to continue. If not Press Control+C to stop"
   
}

Open in new window

0
 
Scott PletcherSenior DBACommented:
I think this code should be able to generate the new code for you.  Then copy it from the output window to a query window to run.  Or, if you need it to run automatically without checking it, let me know and we can add code for that.

--press Ctrl-T (text output) before running; press Ctrl-D to go back to grid output after running.
SET NOCOUNT ON
SELECT REPLACE(REPLACE(REPLACE(proc_code,
    'CREATE PROCEDURE', 'ALTER PROCEDURE'),
    ', BB.Mf as OMf', ', BB.Mf as OMf, NMF as ANMF'),
    'from CM', 'from CM INNER JOIN ' +
        '[server].[dbname].[dbo].[table] ' +
        'on cm.[MRf]=[omc] collate database_default ') + '
GO' + '
'
FROM sys.objects o
CROSS APPLY (
    SELECT OBJECT_DEFINITION(o.object_id) AS proc_code
) AS assign_alias_names
WHERE
    o.type = 'P' AND
    (proc_code LIKE '%, BB.Mf as OMf%' OR
     proc_code LIKE '%from CM[^#0-9a-z]%')
SET NOCOUNT OFF
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ukit, do you still need help with this question?
0
 
UKITAuthor Commented:
This solution was the best for my case, thank you so much for the swift help and sorry for the delay response, much appreciated guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now