Missing abstrophe in dynamic SQL

Question

Post reply

Missing abstrophe in dynamic SQL

abdalah.mehdoini

Ten Centuries

Points: 1001

May 13, 2022 at 1:46 pm

#4032414

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

May 25, 2022 at 10:35 am

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

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

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

Jeffrey Williams SSC Guru Points: 90245 More actions · Answer 2

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:

Use QUOTENAME(..., char(39)) instead of ''' + @someVar + '''
Format dynamic SQL the same - instead of one continuous string
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.
Reference the table alias for all columns in your statements (exception would be DELETE FROM table WHERE)
No need to use TOP 1 inside an EXISTS - since all the code is doing is checking for existence
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))
Use sp_executeSql with parameters instead of concatenating strings
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

kaj SSCommitted Points: 1580 More actions · Answer 3

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+''''