sp_executesql Not Working with Parameters

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])

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;

StackExchange Database Administrators Q#320436, answer score: 11