How alter a procedure dinamically in SQL-Server?

Question

I have a testing DB that restores himself everyday. I want to alter a procedure for everyday in the morning. Right now, I'm trying it using this code:

DECLARE
@SSQL1 NVARCHAR(4000),
@SSQL2 NVARCHAR(4000)
BEGIN 
SET @SSQL2 = 'ALTER PROCEDURE [DBO].[SP_FILE_PETI_CONSULTAR] (@PETI VARCHAR(8))
              AS
              SELECT TOP 1 FILE_CONTENT FROM SRVFILE.DOCUMENTOSFINDIRECT.DBO.FILE_PETICION_TEST
              WHERE PT_CD_PETICION = @PETI ORDER BY AUD_FH_MODIFICACION'
SET @SSQL1 = 'USE [CIL_DB]
              GO
              SET ANSI_NULLS ON
              GO
              SET QUOTED_IDENTIFIER ON
              GO
              EXEC (''' + REPLACE(@SSQL2, '''', '''''') + ''')'
EXEC SP_EXECUTESQL @SSQL1
END

But it doesn't work. I'm getting an syntax error on 'GO'.

Can you help me? Thank you, really so much.

This code doesn't make sense. In fact, the entire concept doesn't make sense. Creating and altering stored procedures is a design time operation, it shouldn't be done on a daily basis, and it should absolutely shouldn't be done using dynamic SQL when there's nothing dynamic about it. — Zohar Peled, Jun 17 '19 at 15:48
GO is NOT a t-sql statement. It is the default batch terminator in SSMS. From the code posted I don't understand the point of using dynamic sql here though. — Sean Lange, Jun 17 '19 at 15:49
I believe that the reason is the four.part.table.name in the SELECT statement. It looks to me like they have hardcoded the appropriate server name and/or table name for their production system, then when this other system restores (presumably from a production backup), they want to re-target the server / table in the proc to a different server or table, probably a test table of data. — Laughing Vergil, Jun 17 '19 at 16:02
@LaughingVergil You are probably correct, however in my time on SO I've learned that it's best to avoid assumptions when attempting to understand questions. If the code doesn't make sense to me, I would rather ask the OP for clarifications than assume I understand the author's intent. — Zohar Peled, Jun 17 '19 at 16:11
I would argue the easiest (and safest) thing to do is to **NOT do testing** in a production instance. Far better to have a testing instance where you can define the linked server as needed for test purposes without affecting the production system - and without this need to "clean up" after restoring the production database to a test database. — SMor, Jun 17 '19 at 16:15

score 0 · Answer 1 · answered Jun 17 '19 at 16:10

The simplest way to do what you appear to want to do (target a different table or server based on where / what system the code is running on, would be to check the server name, something like this:

CREATE OR ALTER PROCEDURE [DBO].[SP_FILE_PETI_CONSULTAR] (@PETI VARCHAR(8)) AS
BEGIN
    IF @@SERVERNAME = 'MyTestServer'
        SELECT TOP 1 FILE_CONTENT 
        FROM SRVFILE.DOCUMENTOSFINDIRECT.DBO.FILE_PETICION_TEST
            WHERE PT_CD_PETICION = @PETI 
        ORDER BY AUD_FH_MODIFICACION
    ELSE
        SELECT TOP 1 FILE_CONTENT 
        FROM SRVFILE.DOCUMENTOSFINDIRECT.DBO.FILE_PETICION
            WHERE PT_CD_PETICION = @PETI 
        ORDER BY AUD_FH_MODIFICACION
END

There are other ways of doing a Production/Test flip, but this would be the simplest for an automatic recovery, since the code doesn't actually need to be changed.

worth noting that `create or alter procedure` requires 2016 version or higher... — Zohar Peled, Jun 17 '19 at 16:12

How alter a procedure dinamically in SQL-Server?

1 Answers1