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

它运行良好，但是我尝试使用视图进行扩展，以下是代码：

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

但是当我尝试执行存储过程时，会返回一个错误，提示如下：

必须声明标量变量 "@fname"

- angelogogo

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命令感兴趣。

- podiluska

---

我正在使用2008 R2，我尝试了你的建议，但它显示错误信息：Msg 102，Level 15，State 1，Procedure spx_Pager，Line 34，@params附近语法不正确。 - angelogogo

@angelogogo 你可能忘了在 SQL 后面加上逗号。 - podiluska

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

最终的代码得以运行，感谢 @podiluska。

- angelogogo