sp_executesql Not Working with Parameters

Question

I am running into a strange problem with dynamic sql that I believe is some sort of formatting issue, and I've been tearing my hair out the last few hours over this. I would really appreciate any assistance I can get.

For some background information the purpose of this stored procedure is to clean up data in our centralized (hq) database that is supposed to have a complete copy of all of the records for all of our stores. If it matters, we are running Microsoft Dynamics RMS (2.0.2022) and SQL 2014 Standard (For HQ, the stores use Express).

Initially I was going to go with putting the sql statement in a parameter and using the exec command, however while I don't use it a lot I think sp_executesql looks a lot cleaner and it is easier to reuse queries as templates and make changes to them. Right now I just have a few select scripts, but once they are finalized I am going to create an additional stored procedure to insert, delete, and update records.

Executing the sql statement works, but for whatever reason I cannot get sp_executesql to work with the parameters in the stored procedure. No matter what I change the parameter to, including using a static value in the stored procedure, it just outputs the parameter name. So regardless of:

@LS = @LinkedServer

OR

@LS = 'StaticLinkedServerName'

The error message it returns is:

Could not find server '@LS' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Non Working Solution

CREATE PROCEDURE [dbo].[usp_bn_ShowStoreHQSync]  
(
      @StoreCode        NVARCHAR(10)
     ,@LinkedServer     NVARCHAR(50)    
     ,@StoreDB          NVARCHAR(40)
)
AS          
BEGIN   
    BEGIN TRANSACTION
        BEGIN TRY
            IF @StoreCode IS NULL
                RAISERROR('@StoreCode Cannot Be NULL',15,1)
            ELSE IF @StoreCode NOT IN (SELECT StoreCode FROM [HQDB].[dbo].[Store])
                RAISERROR('@StoreCode Not In DB',15,1)
            ELSE IF @LinkedServer IS NULL
                RAISERROR('@LinkedServer Cannot Be NULL',15,1)
            ELSE IF @LinkedServer NOT IN (SELECT name FROM sys.servers)
                RAISERROR('@LinkedServer Does Not Exist or Cannot Connect',15,1)
            ELSE
                DECLARE @LinkedServerCatalog AS TABLE
                (
                    DBName NVARCHAR(MAX)
                    , Description NVARCHAR(MAX)
                )
                INSERT INTO @LinkedServerCatalog
                EXEC sp_catalogs @server_name = @LinkedServer
            
                IF @StoreDB NOT IN (SELECT DBName FROM @LinkedServerCatalog)
                    RAISERROR('@Database Does Not Exist or Cannot Connect',15,1)
                ELSE
                    DECLARE @StoreID INT;
                    DECLARE @Last401 DATETIME;

                    SET @StoreID = (SELECT ID FROM Store WHERE StoreCode = @StoreCode)

                    SET @Last401 = dbo.Last401(@StoreID)

                    EXECUTE sp_executesql   
                    N'SELECT SOPO.ID
                        , SOPO.ID
                        , SOPO.PONumber
                        , SOPO.POTitle
                        , SOPO.Status
                        , SOPO.LastUpdated
                        , SOPO.DateCreated
                        , SOPO.DatePlaced
                        , SOPO.IsPlaced
                        , SOPO.Remarks
                        , SOPO.StoreID
                        , SOPO.OtherStoreID
                        , SOPO.[To]
                        , SOPO.ShipTo
                        , SOPO.WorksheetID
                        FROM [@LS].[@DB].[dbo].[PurchaseOrder] SOPO
                        WHERE SOPO.ID NOT IN (SELECT HQPO.ID FROM HQDB.dbo.PurchaseOrder HQPO WHERE HQPO.StoreID = @SID)'  
                    , N'@LS NVARCHAR(50), @DB NVARCHAR(40), @SID NVARCHAR(10)'
                    , @LS = @LinkedServer
                    , @DB = @StoreDB
                    , @SID = @StoreID;

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        SELECT
        ERROR_NUMBER() AS ErrorNumber
        , ERROR_STATE() AS ErrorState
        , ERROR_SEVERITY() AS ErrorSeverity
        , ERROR_PROCEDURE() AS ErrorProcedure
        , ERROR_LINE() AS ErrorLine
        , ERROR_MESSAGE() AS ErrorMessage;

        ROLLBACK TRANSACTION;
    END CATCH
END

Working Solution

CREATE PROCEDURE [dbo].[usp_bn_ShowStoreHQSync]  
(
      @StoreCode        NVARCHAR(10)
     ,@LinkedServer     NVARCHAR(50)    
     ,@StoreDB          NVARCHAR(40)
)
AS          
BEGIN   
    BEGIN TRANSACTION
        BEGIN TRY
            IF @StoreCode IS NULL
                RAISERROR('@StoreCode Cannot Be NULL',15,1)
            ELSE IF @StoreCode NOT IN (SELECT StoreCode FROM [HQDB].[dbo].[Store])
                RAISERROR('@StoreCode Not In DB',15,1)
            ELSE IF @LinkedServer IS NULL
                RAISERROR('@LinkedServer Cannot Be NULL',15,1)
            ELSE IF @LinkedServer NOT IN (SELECT name FROM sys.servers)
                RAISERROR('@LinkedServer Does Not Exist or Cannot Connect',15,1)
            ELSE
                DECLARE @LinkedServerCatalog AS TABLE
                (
                    DBName NVARCHAR(MAX)
                    , Description NVARCHAR(MAX)
                )
                INSERT INTO @LinkedServerCatalog
                EXEC sp_catalogs @server_name = @LinkedServer
            
                IF @StoreDB NOT IN (SELECT DBName FROM @LinkedServerCatalog)
                    RAISERROR('@Database Does Not Exist or Cannot Connect',15,1)
                ELSE
                    DECLARE @StoreID INT;
                    DECLARE @Last401 DATETIME;

                    DECLARE @SQL_MissingPO NVARCHAR(MAX);

                    SET @StoreID = (SELECT ID FROM Store WHERE StoreCode = @StoreCode)

                    SET @Last401 = dbo.Last401(@StoreID)

                    SET @SQL_MissingPO = '  SELECT ''@SQL_MissingPO'' AS ''Script''
                                            , SOPO.ID
                                            , SOPO.PONumber
                                            , SOPO.POTitle
                                            , SOPO.Status
                                            , SOPO.LastUpdated
                                            , SOPO.DateCreated
                                            , SOPO.DatePlaced
                                            , SOPO.IsPlaced
                                            , SOPO.Remarks
                                            , SOPO.StoreID
                                            , SOPO.OtherStoreID
                                            , SOPO.[To]
                                            , SOPO.ShipTo
                                            , SOPO.WorksheetID
                                            FROM [' + @LinkedServer + '].[' + @StoreDB + '].[dbo].[PurchaseOrder] SOPO
                                            WHERE SOPO.ID NOT IN (SELECT HQPO.ID FROM HQDB.dbo.PurchaseOrder HQPO WHERE HQPO.StoreID = ' + CAST(@StoreID AS NVARCHAR(20)) + ')'
                                            
                    EXEC (@SQL_MissingPO)

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        SELECT
        ERROR_NUMBER() AS ErrorNumber
        , ERROR_STATE() AS ErrorState
        , ERROR_SEVERITY() AS ErrorSeverity
        , ERROR_PROCEDURE() AS ErrorProcedure
        , ERROR_LINE() AS ErrorLine
        , ERROR_MESSAGE() AS ErrorMessage;

        ROLLBACK TRANSACTION;
    END CATCH
END;

EXECUTION COMMAND (For Both Solutions)

EXEC usp_bn_ShowStoreHQSync '01', 'Store_001', 'bnstr01'

Answer 1

erland

You can't use object names as parameters in dynamic SQL. That's why your first attempt doesn't work

If you combine your first and second attempts, you can at least parameterize the @StoreId parameter.

Also, keep in mind that just using square brackets isn't fully safe. You need to use QUOTENAME.

DECLARE
    @sql nvarchar(MAX) = N'';

SELECT
   @sql += N'
SELECT 
    SOPO.ID
    SOPO.ID,
    SOPO.PONumber,
    SOPO.POTitle,
    SOPO.Status,
    SOPO.LastUpdated,
    SOPO.DateCreated,
    SOPO.DatePlaced,
    SOPO.IsPlaced,
    SOPO.Remarks,
    SOPO.StoreID,
    SOPO.OtherStoreID,
    SOPO.[To],
    SOPO.ShipTo,
    SOPO.WorksheetID
FROM ' + 
QUOTENAME(@LS) + 
N'.' +
QUOTENAME(@DB) +
N'.dbo.PurchaseOrder SOPO
WHERE SOPO.ID NOT IN (SELECT HQPO.ID FROM HQDB.dbo.PurchaseOrder HQPO WHERE HQPO.StoreID = @SID);';  
                                                        
EXECUTE sys.sp_executesql   
    @sql,
    N'@SID NVARCHAR(10)',
    @SID = @StoreID;

Answer 2

I think you have a misconception about the way sp_executesql works. The @stmt is not a kind of format string that gets replaced by the parameters. It merely takes the text provided, declares the parameter and their values and executes the whole statement. So for example the following code

EXECUTE sys.sp_executesql @stmt = N'SELECT TOP(10) * FROM [@srv].[@db].[@schema].[@obj]',
                          @parameters = N'@srv sysname, @db sysname, @schema sysname, @obj sysname',
                          @srv = 'test',
                          @db = 'master',
                          @schema = 'sys',
                          @obj = 'messages'

will be executed as something like

DECLARE @srv sysname = 'test',
        @db sysname = 'master',
        @schema sysname='sys',
        @obj sysname='messages'
SELECT  TOP(10) *
FROM    [@srv].[@db].[@schema].[@obj] 

which in itself is not a valid statement because variables are not allowed in these places so they will not get replaced.

Due to the fact that the variables are surrounded by square brackets, that makes them an allowed object/schema/server/database name. Because of that the error message reads Could not find server '@LS' in sys.servers. in your case.