Missing abstrophe in dynamic SQL

  • Hello ,

    I haven't been able since morning to find how to add the missing abstrophe in my procedure that uses dynamic SQL

    I added a print in my script to display the exec instruction and I have a missing apstrophe in the name of the proc

    Anyone have an idea please?

    USE [test]
    GO
    /****** Object: StoredProcedure [dbo].[ADMIN_DEPLOYPS_PROD] Script Date: 13/05/2022 07:14:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    alter PROCEDURE [dbo].[ADMIN_DEPLOYPS_PROD] @LNK_Name nvarchar(80) , @bdd varchar(80) ,@PS VARCHAR(80), @SCH SYSNAME = 'dbo'
    AS

    BEGIN
    Declare @sqlm varchar(max)
    Declare @sql1 nvarchar(max)
    Declare @sql nvarchar(max)
    Declare @sqlZ varchar(max)
    --Declare @bdd varchar(35)


    Declare @link1 nvarchar(max)
    Declare @link2 nvarchar(max)
    Declare @link3 nvarchar(max)
    Declare @link4 nvarchar(max)
    Declare @Login varchar(80)
    Declare @Datetimedatetime
    SET NOCOUNT ON;

    IF (ORIGINAL_LOGIN() IN (''))
    BEGIN;
    PRINT 'Déploiement interdit - Contactez l''équipe SQL Link';
    RETURN;
    END;

    SET @Datetime = Getdate()
    SET @Login = system_user

    SELECT @sql = Definition
    FROM sys.procedures p
    INNER JOIN sys.sql_modules m ON p.object_id = m.object_id
    where name = @PS and p.schema_id = SCHEMA_ID(@SCH)

    BEGIN DISTRIBUTED TRAN

    raiserror('--- Alim Z_CODESOURCE de Prod',0,1) WITH NOWAIT
    SET @sqlZ = 'USE [' + @bdd + ']; EXEC Z_CODESOURCE_Alim @NOM_OBJET = ''' + @PS + ''''
    --EXECUTE (@sqlZ) AT [uat-link-ariane4pl-db.bollore-logistics.com,50006]
    SET @LINK1 = N'EXEC ('''+@sqlZ+''') AT ' + QUOTENAME(@LNK_Name);
    --EXEC sp_executesql @LINK1;
    print @LINK1
    SET NOCOUNT OFF



    IF EXISTS (SELECT TOP 1 1 FROM ADMIN_DEPLOY_BLOCK WHERE NOM_PS = @PS AND Nom_Schema = @SCH)
    BEGIN
    SELECT'DEPLOIEMENT INTERDIT PAR ' + ACTEUR + '. RAISON: ' + RAISON
    FROM ADMIN_DEPLOY_BLOCK
    WHERE NOM_PS = @PS
    AND Nom_Schema = @SCH;

    PRINT 'Pas de déploiement !'
    END
    ELSE
    BEGIN TRY
    -- Purge préalable de la table METADATA pour ne pas créer d'interférences entre les triggers
    SET @sqlm = 'USE [' + @bdd + ']; DELETE FROM dbo.METADATA WHERE NAME = '''+@PS+''''
    --EXECUTE (@sqlm) AT [uat-link-ariane4pl-db.bollore-logistics.com,50006]
    SET @LINK2 = N'EXEC ('''+@sqlm+''') AT ' + QUOTENAME(@LNK_Name);
    EXEC sp_executesql @LINK2;

    -- Verif + delete
    SET @sql1 = 'IF EXISTS (SELECT * FROM sys.objects WHERE type IN (''P'', ''FN'') AND name = '''+@PS+''' AND schema_id = ( select schema_id from sys.schemas WHERE name=''' + @SCH + ''' )) DROP PROCEDURE ' + @SCH + '.' + @PS
    --SET @sql1 = 'IF EXISTS (SELECT * FROM sys.objects WHERE type IN (''P'', ''FN'') AND name = '''+@PS+''') DROP PROCEDURE '+@PS
    SET @sql1 = REPLACE(@sql1,'''','''''')
    SET @sql1 = 'USE [' + @bdd + ']; EXEC(''' + @sql1 + ''')'
    --EXECUTE (@sql1) AT [uat-link-ariane4pl-db.bollore-logistics.com,50006]
    SET @LINK3 = N'EXEC ('''+@sql1+''') AT ' + QUOTENAME(@LNK_Name);
    EXEC sp_executesql @LINK3;

    SET @sql = REPLACE(@sql,'''','''''')
    SET @sql = 'USE [' + @bdd + ']; EXEC(''' + @sql + ''')'
    --EXECUTE (@sql) AT [uat-link-ariane4pl-db.bollore-logistics.com,50006]
    SET @LINK4 = N'EXEC ('''+@sql+''') AT ' + QUOTENAME(@LNK_Name);
    EXEC sp_executesql @LINK4;

    INSERT INTO LOG_DEPLOYPS (LOGIN, PS, DAT) values (@login, @PS, @datetime)

    Print ''
    Print ''
    Print 'Déploiement effectuée avec succès'
    COMMIT TRAN
    END TRY
    BEGIN CATCH
    Print ''
    Print ''
    Print 'Une erreur est survenue: ' + CAST(@@ERROR AS VARCHAR(255))

    SELECT
    @PS AS ProcedureName
    ,ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;

    ROLLBACK TRAN

    END CATCH

    END

    Script Exécuté

    USE [Test]
    GO

    DECLARE@return_value int

    EXEC@return_value = [dbo].[ADMIN_DEPLOYPS_PROD]
    @bdd = N'link4PL',
    @PS = 'SP_test',
    @LNK_Name='uat-link'


    SELECT'Return Value' = @return_value

    GO

     

    Sans titre

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • There are a lot of problems with that code - formatting makes it very difficult to read as well as missing alias references and so many places where you are attempting to single-quote items.

    A couple of tricks to make it easier to manage/maintain:

    1. Use QUOTENAME(..., char(39)) instead of ''' + @someVar + '''
    2. Format dynamic SQL the same - instead of one continuous string
    3. Use a consistent style - in other words, if you capitalize keywords then capitalize them all the time.  In your code you have some capital, some lowercase and some mixed case.
    4. Reference the table alias for all columns in your statements (exception would be DELETE FROM table WHERE)
    5. No need to use TOP 1 inside an EXISTS - since all the code is doing is checking for existence
    6. Use CONCAT instead of plus sign to concatenate strings where possible.  This would allow for coding such as: DECLARE @singleQuote char(1) = char(39);  SET @someVar = CONCAT('EXEC (', @singleQuote, @someVar, @singleQuote, ') AT ', QUOTENAME(@LNK_Name))
    7. Use sp_executeSql with parameters instead of concatenating strings
    8. Use DROP ... IF EXISTS ...

    Here is an example of how to format some dynamic SQL:

    Declare @sql nvarchar(max) = '
    Select someColumn
    From someTable st
    Where st.Col1 = @parm1
    And st.Col2 = @parm2;'

    Execute sys.sp_executeSql
    @stmt = @sql
    , @params = N'@parm1 nvarchar(10), @parm2 int'
    , @parm1 = @p1, @parm2 = @p2;

    The more you can simplify your code and use a consistent style/format - the easier it will be to read and that will make it much easier to debug.

    Here is another example:

        Set @dropStmt = concat('DROP PROCEDURE IF EXISTS ', concat_ws('.', @SCH, @PS), ';');

    Execute sys.sp_executeSql @stmt = @dropStmt;

    This can replace your code that checks for the existence of the procedure you are dropping.  However - if the purpose of this code is to update/change an existing procedure (which I don't see being done here), then you shouldn't worry about dropping the existing procedure and instead use 'CREATE OR ALTER PROCEDURE'.  That way, if it doesn't exist it gets created - and if it does it will be altered.

    Note: this appears to be code that you are executing across a linked server.  I would be very hesitant to even allow something like this to be implemented.  I would have to be convinced that using dynamic code on one server to create/update procedures on another server is required or needed.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • At a guess you have one too many single quotes (apostrophes) at the end of this line, i.e. four where it should have been only three:

    SET @SQLZ = 'USE [' + @bdd + ']; EXEC Z_CODESOURCE_Alim @NOM_OBJET = ''' + @ps + ''''

    Incidently the same goes for this one:

    SET @sqlm = 'USE [' + @bdd + ']; DELETE FROM dbo.METADATA WHERE NAME = '''+@PS+''''

  • This was removed by the editor as SPAM

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

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