SQL Server stored procedure need to declare scalar variable

Question

I am trying this stored procedure:

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 < 1 SET @PageNo = 1
    IF @ItemsPerPage < 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 >= ' 
                        + CONVERT(varchar(9), @StartIdx) + ' AND
                       Row <=  ' + 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

it works well but I tried extending it with a view here is the 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 < 1 SET @PageNo = 1
    IF @ItemsPerPage < 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 >= ' 
                        + CONVERT(varchar(9), @StartIdx) + ' AND
                       Row <=  ' + 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

but when I try to execute the stored procedure an error is returning it says

Must declare the scalar variable "@fname"

Answer 1

If you are using variables in your sp_executesql you need to define them, as you are doing in your second sp_executesql

@params = N'@TotalRows INT OUTPUT', 
@TotalRows = @TotalRows OUTPUT 

So you need to add

@params = N'@fname varchar(50), @mname varchar(50), @lname varchar(50), @qualifier varchar(10)',
@fname = @fname, @mname = @mname, @lname=@lname, @qualifier = @qfr

to your first sp_executesql call

Although why you're using dynamic SQL at all is not immediately apparent to me.

If you're using SQL 2012, you may be interested in the OFFSET and FETCH commands

Answer 2

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 < 1 SET @PageNo = 1
    IF @ItemsPerPage < 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 = @firstname or @firstname = '') and (mname = @midname or @midname = '') and (lname = @lastname or @lastname = '') and (qualifier = @quali or @quali = '')
                       ) AS tbl WHERE  Row >= ' 
                        + CONVERT(varchar(9), @StartIdx) + ' AND
                       Row <=  ' + CONVERT(varchar(9), @EndIdx)
    EXEC sp_executesql @SQL,
    @params = N'@firstname varchar(50), @midname varchar(50), @lastname varchar(50), @quali varchar(10)',
    @firstname = @fname, @midname = @mname, @lastname=@lname, @quali = @qfr

    SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM searcherview' 
    EXEC sp_executesql 
        @query = @SQL, 
        @params = N'@TotalRows INT OUTPUT', 
        @TotalRows = @TotalRows OUTPUT 
END

the final code and it work thanks to @podiluska