c# - 从 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';