title: SQL Server stored procedure need to declare scalar variable
body: <p>I am trying this stored procedure:</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE PROCEDURE [dbo].[spx_Pager]
      @PageNo int = 1,
      @ItemsPerPage int = 2,
      @TotalRows int out
  AS
  BEGIN
    SET NOCOUNT ON
    DECLARE
      @StartIdx int,
      @SQL nvarchar(max),  
      @SQL_Conditions nvarchar(max),  
      @EndIdx int
  
      IF @PageNo &lt; 1 SET @PageNo = 1
      IF @ItemsPerPage &lt; 1 SET @ItemsPerPage = 10
  
      SET @StartIdx = (@PageNo -1) * @ItemsPerPage + 1
      SET @EndIdx = (@StartIdx + @ItemsPerPage) - 1
      SET @SQL = 'SELECT FilePath
                  FROM (
                  SELECT  ROW_NUMBER() OVER(ORDER BY ID) AS Row, * 
                        FROM  tblFiles ) AS tbl WHERE  Row &gt;= ' 
                          + CONVERT(varchar(9), @StartIdx) + ' AND
                         Row &lt;=  ' + CONVERT(varchar(9), @EndIdx)
      EXEC sp_executesql @SQL
  
      SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tblFiles' 
      EXEC sp_executesql 
          @query = @SQL, 
          @params = N'@TotalRows INT OUTPUT', 
          @TotalRows = @TotalRows OUTPUT 
  END
  </code></pre>
  
  <p>it works well but I tried extending it with a view here is the code</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  alter PROCEDURE [dbo].[spx_Pager]
      @PageNo int = 1,
      @ItemsPerPage int = 2,
      @TotalRows int out,
      @fname varchar(50),
      @mname varchar(50),
      @lname varchar(50),
      @qfr varchar(10)
  AS
  BEGIN
    SET NOCOUNT ON
    DECLARE
      @StartIdx int,
      @SQL nvarchar(max),  
      @SQL_Conditions nvarchar(max),  
      @EndIdx int
  
      IF @PageNo &lt; 1 SET @PageNo = 1
      IF @ItemsPerPage &lt; 1 SET @ItemsPerPage = 10
  
      SET @StartIdx = (@PageNo -1) * @ItemsPerPage + 1
      SET @EndIdx = (@StartIdx + @ItemsPerPage) - 1
      SET @SQL = N'SELECT path_front
                  FROM (
                  SELECT  ROW_NUMBER() OVER(ORDER BY fname) AS Row, * 
                        FROM  searcherview 
                        where (fname = @fname or @fname = '') and (mname = @mname or @mname = '') and (lname = @lname or @lname = '') and (qualifier = @qfr or @qfr = '')
                         ) AS tbl WHERE  Row &gt;= ' 
                          + CONVERT(varchar(9), @StartIdx) + ' AND
                         Row &lt;=  ' + CONVERT(varchar(9), @EndIdx)
      EXEC sp_executesql @SQL
  
      SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM searcherview' 
      EXEC sp_executesql 
          @query = @SQL, 
          @params = N'@TotalRows INT OUTPUT', 
          @TotalRows = @TotalRows OUTPUT 
  END
  </code></pre>
  
  <p>but when I try to execute the stored procedure an error is returning it says</p>
  
  <blockquote>
    <p>Must declare the scalar variable "@fname"</p>
  </blockquote>