title: Parameter in dynamical sql server query
body: <p>i have problem with passing parameters in my dynamical query. Here is the error message</p>
  
  <blockquote>
    <p>Msg 50000, Level 11, State 1, Procedure WriteJobLog, Line 101 Error
    writing job log: Line #90: [ERR]  #7: Must declare the scalar variable
    "@LastId".</p>
  </blockquote>
  
  <p>and my sql code is:</p>
  
  <pre><code>ALTER PROCEDURE [WarehouseMgmt].[SyncReportServerFormatEntries]
      @SyncJobId varchar(50),
      @SyncJobStep varchar(50)=NULL, -- Sproc name will be used as a job step if not specified
      @SyncExecId int=NULL, -- Will be obtained from @SyncJobStep step execution if not specified
      @LastId BIGINT=NULL, -- Last transaction id to snyc, will be obtained from WarehouseMgmt.SyncSQLData if not specified
      @SyncObjectName VARCHAR(50) = 'WarehouseMgmt.FactReportServerExecutionLog'
  AS
  ..........
  DECLARE @sql nvarchar(max);
          DECLARE @server nvarchar(255) = (SELECT [Value] FROM [WarehouseMgmt].[SyncConfig] WHERE [Key] = 'ReportServerLinkedServer')
          DECLARE @database nvarchar(255) = (SELECT [Value] FROM [WarehouseMgmt].[SyncConfig] WHERE [Key] = 'ReportServerDatabase')
  
          SET @sql='MERGE [WarehouseMgmt].[DimReportServerFormatEntries] AS Target
          USING( 
                    SELECT CASE [Format]
                    WHEN ''RPL'' THEN ''View''
                    ELSE ''Export'' 
                    END [Name],
                    [Format] [OriginalFormatName],
                    LogEntryId 
                    FROM OPENQUERY('+@server+',
                    ''SELECT CASE [Format]
                    WHEN ''''RPL'''' THEN ''''View''''
                    ELSE ''''Export'''' 
                    END [Name],
                    [Format] [OriginalFormatName],
                    LogEntryId  
                    FROM '+@database+'.dbo.[ExecutionLogStorage] WHERE LogEntryId &gt; @LastId AND [Parameters] IS NOT NULL '' )               
  
                ) AS Source
          ON Source.LogEntryId=Target.SourceOrigId
          WHEN NOT MATCHED BY TARGET THEN
              INSERT 
              (
                  [Name],
                  [OriginalFormatName],
                  [SourceOrigId],
                  SyncExecId
              )
              VALUES 
              (
                  ISNULL(Source.[Name],''&lt;UNKNOWN&gt;''),
                  ISNULL(Source.[OriginalFormatName],''&lt;UNKNOWN&gt;''),
                  Source.[LogEntryId],
                  @SyncExecId
              )
              OUTPUT  
                  ISNULL(Source.[Name],''&lt;UNKNOWN&gt;''),
                  ISNULL(Source.[OriginalFormatName],''&lt;UNKNOWN&gt;''),
                  Source.[LogEntryId]
              INTO #NewReportServerFormatEntries;' 
  
          EXEC sp_executesql @sql,N'@LastId bigint,@SyncExecId int',@LastId,@SyncExecId;
  </code></pre>