I have an error during the execution of my proc that I must declare my variable while this last variable is well declared

because I declared my variables in the proc

Proc Syntax

USE [Test]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [dbo].[ADMIN_D]  @LNK_Name nvarchar(60) ,@bdd  varchar(50) ,@PS VARCHAR(80), @SCH SYSNAME = 'dbo'
AS
 
 
    Declare @sqlm varchar(max)
    Declare @sql1 nvarchar(max)
    Declare @sql nvarchar(max)
    Declare @sqlZ varchar(max)
 
    Declare @LINK nvarchar(max)
    Declare @LINK1  nvarchar(max)
    Declare @LINK2  varchar(max)
    --Declare @bdd varchar(35)
    Declare @Login      varchar(80)
    Declare @Datetime   datetime
    SET NOCOUNT ON;
 
    IF (ORIGINAL_LOGIN() IN (''))
    BEGIN;
        PRINT 'Déploiement interdit - Contactez l''équipe SQL ';
        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 = 'EXEC dbo.Z_CODESOURCE_Alim @NOM_OBJET = '''   @PS   ''''
        SET @LINK = N'EXEC (@sqlZ) AT '   QUOTENAME(@LNK_Name);;
        EXEC sp_executesql @LINK;
    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
 
                SET @sqlm = ' USE ['   @bdd   '];DELETE FROM METADATA WHERE NAME = ''' @PS ''''
                SET @LINK = N'EXEC (@sqlm) AT '   QUOTENAME(@LNK_Name);
                                EXEC sp_executesql @LINK ;
 
 
                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   ''')'
                SET @LINK1 = N'EXEC (@sql1) AT '   QUOTENAME(@LNK_Name) ;
                                EXEC sp_executesql @LINK1 ;
 
 
                SET @sql = REPLACE(@sql,'''','''''')
                SET @sql = 'USE ['   @bdd   ']; EXEC('''   @sql   ''')'
                SET @LINK2 = N'EXEC (@sql) AT '   QUOTENAME(@LNK_Name) ;
                                EXEC sp_executesql @LINK2 ;
 
                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

Execute Proc

 USE [Test]
GO
DECLARE @return_value int
 
EXEC    @return_value = [dbo].[ADMIN_D]
        @bdd = N'PROD',
        @PS = N'SP_login',
        @LNK_Name=N'uat-link'
 
SELECT  'Return Value' = @return_value
GO

Someone who can help me please

enter image description here

Run stored procedure on linked server (linked server name as variable)

CodePudding user response：

The problem is these lines (and other places later on where you use the same pattern):

SET @LINK = N'EXEC (@sqlZ) AT '   QUOTENAME(@LNK_Name);;
EXEC sp_executesql @LINK;

The sp_executesql function creates a new execution context that doesn't know about the @sqlZ variable. To get around this, there are additional arguments you must pass to the function to set up the variables and their values.

CodePudding user response：

Instead of this:

SET @sqlm = ' USE ['   @bdd   '];DELETE FROM METADATA WHERE NAME = ''' @PS ''''
SET @LINK = N'EXEC (@sqlm) AT '   QUOTENAME(@LNK_Name);
                            EXEC sp_executesql @LINK ;

Try:

SET @sqlm = N'DELETE dbo.METADATA WHERE NAME = @PS;';

DECLARE @exec nvarchar(1024) = CONCAT(QUOTENAME(@LNK_Name),
  char(46), QUOTENAME(@bdd), char(46), N'sys.sp_executesql');

EXEC @exec @sqlm, N'@PS varchar(80)', @PS;

This translates to:

EXEC LinkedServerName.DatabaseName.sys.sp_executesql @sqlm, ...

This allows you to build a safe sp_executesql call without concatenating strings (and opening yourself to SQL injection), dealing with all kinds of nested double-quotes, or putting USE commands inside your SQL.