从 SQL Server 2012 提取数据集时偏移量附近的语法错误不正确

Question

我是SQL新手，我遵循了标准编写过程，但是当我填写数据集时，异常被捕获。由于数据集无法访问该表。我因接近偏移的语法不正确而出现异常。

我的代码在这里：

USE [OSO]
GO
/****** Object:  StoredProcedure [dbo].[FetchCustomer]    Script Date: 1/5/2017 11:02:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[FetchCustomer]
@Type Nvarchar(20),
@Condition Nvarchar(20),
@Start int,
@Length int,
@UserId nvarchar(30),
@ColumnIndex int,
@SortOrder nvarchar(10)
 AS
BEGIN
    SET NOCOUNT ON;
  declare @ColumnName nvarchar(50);
  declare @genericsearchsql nvarchar(max);
  declare @genericsql nvarchar(max);
    if @ColumnIndex=0
        begin
            if exists (select * from SystemSettingsSubForms where SubFormName='CUSTOMERLIST' and FieldName='TABLESORT' and Value<>'0,asc' and Value<>'0,desc' and UserId=@UserId)
                select @ColumnIndex=substring(Value,1,1),@SortOrder=substring(Value,3,4) from SystemSettingsSubForms where SubFormName='CUSTOMERLIST' and FieldName='TABLESORT'
            else
                begin
                    set @ColumnName='Company'
                    set @SortOrder='asc'
                end
        end
        if @ColumnIndex=1
            set @ColumnName='Company'
        else if @ColumnIndex=2
            set @ColumnName='FirstName'
        else if @ColumnIndex=3
            set @ColumnName='Street'
        else if @ColumnIndex=4
            set @ColumnName='City'
        else if @ColumnIndex=5
            set @ColumnName='State'
    set @genericsearchsql='SELECT * FROM Customers where 
            FirstName like '''+'%'+@Condition+'%'' or
            Company like '''+'%'+@Condition+'%'' or
            LastName like '''+'%'+@Condition+'%'' or
            Street like '''+'%'+@Condition+'%'' or
            City like '''+'%'+@Condition+'%'' or
            [State] like '''+'%'+@Condition+'%''
            order by '+@ColumnName+' '+@SortOrder+'
            offset @Start rows FETCH NEXT @Length ROWS ONLY';
    set @genericsql='SELECT * FROM Customers order by '+@ColumnName+' '+@SortOrder+' offset @Start rows FETCH NEXT @Length ROWS ONLY'; 

     if(@Type='ALL')
    Begin
      SELECT COUNT(*)
          FROM Customers
          exec sp_executesql @genericsql,N'@Start int,@Length int',@Start=@Start,@Length=@Length          
  end
  else if(@Type='CUSTOMERSEARCH')
    Begin
            SELECT COUNT(*)
            FROM Customers where  
            (FirstName like '%'+@Condition+'%' or                                       
            Company like '%'+@Condition+'%' or
            Street like '%'+@Condition+'%' or
            [State] like '%'+@Condition+'%' or                                      
            City like '%'+@Condition+'%')
            exec sp_executesql @genericsearchsql,N'@Start int,@Length int',@Start=@Start,@Length=@Length
    end
   END

任何人都可以帮忙。

Answer 1

由于您正在构建 SQL 命令，因此您需要将 @Start 和 @Length 的值嵌入到字符串中，因为它们对执行上下文不可用。此版本应该可以工作。

set @genericsearchsql='SELECT * FROM Customers where 
            FirstName like '''+'%'+@Condition+'%'' or
            Company like '''+'%'+@Condition+'%'' or
            LastName like '''+'%'+@Condition+'%'' or
            Street like '''+'%'+@Condition+'%'' or
            City like '''+'%'+@Condition+'%'' or
            [State] like '''+'%'+@Condition+'%''
            order by '+@ColumnName+' '+@SortOrder+'
            offset ' + CAST(@Start as varchar) + ' rows FETCH NEXT ' + Cast(@Length as varchar) + ' ROWS ONLY';

Answer 2

您正在尝试动态执行 SELECT，但以下内容在 SELECT 语句中无效：

   offset @Start rows FETCH NEXT @Length ROWS ONLY

我不确定你想用这个子句做什么，但我的猜测是，它不需要，所以我会删除它。