sql-server - SQL Server 存储过程需要声明标量变量

Question

我正在尝试这个存储过程:

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

它运行良好，但我尝试用 View 扩展它，这里是代码

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

但是当我尝试执行存储过程时返回一个错误，它说

Must declare the scalar variable "@fname"

Answer 1

如果您在 sp_executesql 中使用变量，则需要像在第二个 sp_executesql 中那样定义它们

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

所以你需要添加

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

到您的第一个 sp_executesql 调用

虽然我不是很清楚为什么要使用动态 SQL。

如果您使用的是 SQL 2012，您可能会对 OFFSET 和 FETCH 命令感兴趣