gpt4 book ai didi

sql-server - SQL Server查询语法错误

转载 作者:行者123 更新时间:2023-12-03 08:24:06 24 4
gpt4 key购买 nike

我写了一个SQL Server查询:

declare  @TaxYear VARCHAR(50)
set @TaxYear='13'
declare @BBL VARCHAR(50)
set @BBL=''
declare @Appartment VARCHAR(50)
set @Appartment=''
declare @ResidenceTypeDescription VARCHAR(200)
set @ResidenceTypeDescription=''
declare @SN1 VARCHAR(20)
set @SN1=''
declare @SN2 VARCHAR(20)
set @SN2=''
declare @Status VARCHAR(100)
set @Status='Unassigned'
declare @RowIndex INT
set @RowIndex=1
declare @MaxRows INT
set @MaxRows=25
declare @SortExpression varchar(50)
set @SortExpression='bbl desc'
declare @sql varchar(max)
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = @RowIndex
SET @EndRow = ( @StartRow + @MaxRows ) - 1
set @sql= 'SELECT *
FROM ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY '+@SortExpression +') AS ROW
FROM vwApplicationList
WHERE TaxYear = '+@TaxYear+'
AND Status = '+@Status+'
AND REPLACE(BBL, ''--'', '''') LIKE ''%' + @BBL
+ '%''
AND COALESCE(UnitOrAppartmentNumber, '''') LIKE ''%'
+ @Appartment + '%''
AND COALESCE(ResidenceTypeDescription, '''') LIKE ''%'
+ @ResidenceTypeDescription + '%''
AND ( COALESCE(SN1, '''') LIKE ''%' + @SN1 + '%''
OR COALESCE(SN2, '''') LIKE ''%' + @SN2
+ '%''
)
) AS NumberedUsers
WHERE ROW BETWEEN '+@StartRow+' AND '+@EndRow
exec (@sql)

当我运行此查询时,我得到一个错误:

最佳答案

问题是在您试图连接整数值的查询末尾,您已经将其转换为varchar ...

declare  @TaxYear VARCHAR(50)
set @TaxYear='13'
declare @BBL VARCHAR(50)
set @BBL=''
declare @Appartment VARCHAR(50)
set @Appartment=''
declare @ResidenceTypeDescription VARCHAR(200)
set @ResidenceTypeDescription=''
declare @SN1 VARCHAR(20)
set @SN1=''
declare @SN2 VARCHAR(20)
set @SN2=''
declare @Status VARCHAR(100)
set @Status='Unassigned'
declare @RowIndex INT
set @RowIndex=1
declare @MaxRows INT
set @MaxRows=25
declare @SortExpression varchar(50)
set @SortExpression='bbl desc'
declare @sql varchar(max)
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = @RowIndex
SET @EndRow = ( @StartRow + @MaxRows ) - 1
set @sql=
'SELECT *
FROM ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY '+@SortExpression +') AS ROW
FROM vwApplicationList
WHERE TaxYear = '+@TaxYear+'
AND Status = '+@Status+'
AND REPLACE(BBL, ''--'', '''') LIKE ''%' + @BBL
+ '%''
AND COALESCE(UnitOrAppartmentNumber, '''') LIKE ''%'
+ @Appartment + '%''
AND COALESCE(ResidenceTypeDescription, '''') LIKE ''%'
+ @ResidenceTypeDescription + '%''
AND ( COALESCE(SN1, '''') LIKE ''%' + @SN1 + '%''
OR COALESCE(SN2, '''') LIKE ''%' + @SN2
+ '%''
)
) AS NumberedUsers
WHERE ROW BETWEEN '+ Convert(varchar,@StartRow)+' AND '+Convert(varchar,@EndRow)

exec (@sql)

关于sql-server - SQL Server查询语法错误,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9108443/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com