title: Using table type param in SQL server stored procedure
body: <p>I use  table type param (@listdraftIds) and split data in table to compare with data in DB but the error is as below. Help me, thank you.</p>
  
  <pre><code> CREATE OR ALTER   PROCEDURE [dbo].[sp_sel_contract]
      (
          @listDraftId  nvarchar(max),
          @Page int,
          @PageNumber int,
          @TotalRecord int OUTPUT
      )
          AS
          BEGIN
          SET NOCOUNT ON
  
          BEGIN
          DECLARE @listdraftIds TABLE (draftIds nvarchar(max))
          INSERT INTO @listdraftIds VALUES(@listDraftId)
  
          DECLARE @sql varchar(MAX)
          SET @sql= ' ALTER DATABASE '+ quotename(db_name()) + ' SET COMPATIBILITY_LEVEL = 130 ';
          EXEC(@sql)
  
          END
  
          DECLARE @query NVARCHAR(MAX)
          DECLARE @newquery NVARCHAR(MAX)
  
          DECLARE @RowIndex int 
          SET @RowIndex = (@Page-1) * @PageNumber
  
          SET @query = ''
  
          SET @query = @query + ' SELECT DISTINCT'
          SET @query = @query + ' [d].[draft_id], '
          ...
  
          SET @query = @query + 'WHERE '
          SET @query = @query + ' [d].[del_flg] = ''FALSE'''
  
          SET @query = @query + 'AND '
          SET @query = @query + ' [d].[draft_id]  '
          SET @query = @query + 'IN ('
          SET @query = @query + ' SELECT  DISTINCT  '
          SET @query = @query + ' value AS draft_id '
          SET @query = @query + 'FROM '
          SET @query = @query + ' @listdraftIds '
          SET @query = @query + 'CROSS APPLY STRING_SPLIT(draftIds, '','') '
          SET @query = @query + 'WHERE '
          SET @query = @query + ' RTRIM(value) &lt;&gt; '''' )'
  
          PRINT  @query
  
          SET @newquery = '  SET @TotalRecord = (SELECT COUNT(*) FROM (' + @query +') AS t) '
  
          SET @newquery = @newquery + ' ORDER BY '
          SET @newquery = @newquery + '    [draft_date] DESC, [d].[draft_id] DESC, [g].[detail_no] ASC'
          SET @newquery = @newquery + '    OFFSET @RowIndex ROWS FETCH NEXT @PageNumber ROWS ONLY'  
  
          EXECUTE sp_executesql @newquery,
              @listDraftId = @listDraftId,
              @RowIndex = @RowIndex,
              @PageNumber = @PageNumber, 
              @TotalRecord = @TotalRecord OUTPUT
      END
  
  Then Exec proc:
  
  DECLARE @return_value int,
          @TotalRecord int
  
  EXEC    @return_value = [dbo].[sp_sel_draft_condition_api1]
          @listDraftId = N'123,345',
          @Page = 1,
          @PageNumber = 20,
          @TotalRecord = @TotalRecord OUTPUT
  
  SELECT  @TotalRecord as N'@TotalRecord'
  
  SELECT  'Return Value' = @return_value
  
  GO
  </code></pre>
  
  <p>Error:</p>
  
  <blockquote>
    <p>Msg 102, Level 15, State 1, Line 3
        Incorrect syntax near '30302'.</p>
    
    <p>Msg 1087, Level 15, State 2, Line 3
        Must declare the table variable "@listdraftIds".</p>
    
    <p>Msg 137, Level 15, State 2, Line 3
        Must declare the scalar variable "@PageNumber".</p>
  </blockquote>