如何在Dynamic Sql中添加变量，而不是连接它们？

发布于 2024-08-10 14:15:17 字数 286 浏览 5 评论 0原文

我有以下动态 sql 语句，我想在其中添加 @StartRowIndex + @MaximumRows 并从中减去 1。我不清楚在声明中将单引号放在哪里。这里是：

SET @sql = @sql + ' SELECT *
 FROM
LicenseInfo
WHERE RowNum 
BETWEEN ' + @StartRowIndex + ' AND ' + 
'(' + @StartRowIndex + @MaximumRows + ')'  -  1
+ ' ORDER BY cnt desc'

原文

I have the following dynamic sql statement where I want to add @StartRowIndex + @MaximumRows and subtract 1 from it. I am unclear on where to put the single quotes in the statement. Here it is:

SET @sql = @sql + ' SELECT *
 FROM
LicenseInfo
WHERE RowNum 
BETWEEN ' + @StartRowIndex + ' AND ' + 
'(' + @StartRowIndex + @MaximumRows + ')'  -  1
+ ' ORDER BY cnt desc'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

栖迟 2024-08-17 14:15:17

创建新变量@EndRowIndex并在构造动态sql语句之前计算它。

像这样的东西：

DECLARE @EndRowIndex int

SET @EndRowIndex = @StartRowIndex + @MaximumRows - 1

SET @sql = @sql + ' SELECT *
 FROM
LicenseInfo
WHERE RowNum 
BETWEEN ' + @StartRowIndex + ' AND ' + @EndRowIndex 
+ ' ORDER BY cnt desc'

Create new variable @EndRowIndex and calculate it before you construct the dynamic sql statement.

Something like:

DECLARE @EndRowIndex int

SET @EndRowIndex = @StartRowIndex + @MaximumRows - 1

SET @sql = @sql + ' SELECT *
 FROM
LicenseInfo
WHERE RowNum 
BETWEEN ' + @StartRowIndex + ' AND ' + @EndRowIndex 
+ ' ORDER BY cnt desc'

回复收藏 0 原文

dawn曙光 2024-08-17 14:15:17

您需要将 int 参数转换为 varchar

SET @sql = @sql + ' SELECT *
 FROM
LicenseInfo
WHERE RowNum 
BETWEEN ' + @StartRowIndex + ' AND ' + 
'(' + CAST(@StartRowIndex as varchar(10)) + CAST(@MaximumRows as varchar(10)) + ') - 1  
 ORDER BY cnt desc'

You need to cast the int parameters into varchar

SET @sql = @sql + ' SELECT *
 FROM
LicenseInfo
WHERE RowNum 
BETWEEN ' + @StartRowIndex + ' AND ' + 
'(' + CAST(@StartRowIndex as varchar(10)) + CAST(@MaximumRows as varchar(10)) + ') - 1  
 ORDER BY cnt desc'

回复收藏 0 原文

幸福不弃 2024-08-17 14:15:17

声明一个变量，进行计算并在生成 SQL 语句时将其转换为 varchar

DECLARE @LastRowIndex int

SET @LastRowIndex = @StartRowIndex + @MaximumRows - 1

SET @sql = @sql + ' 
SELECT *
FROM LicenseInfo
WHERE 1=1
AND RowNum BETWEEN ' + CAST (@StartRowIndex as VarChar) + 
' AND ' + CAST (@LastRowIndex as VarChar)
+ ' ORDER BY cnt DESC'

您必须进行转换才能让 SQL Server 连接字符串值，否则它将尝试将 nVarChar 转换为数字并尝试将它们添加为数字。

Declare a variable, do the calculation and CAST it to varchar when generating the SQL statement

DECLARE @LastRowIndex int

SET @LastRowIndex = @StartRowIndex + @MaximumRows - 1

SET @sql = @sql + ' 
SELECT *
FROM LicenseInfo
WHERE 1=1
AND RowNum BETWEEN ' + CAST (@StartRowIndex as VarChar) + 
' AND ' + CAST (@LastRowIndex as VarChar)
+ ' ORDER BY cnt DESC'

You have to cast in order to let SQL Server concatenate string values, otherwise it will try to convert the nVarChar to number and try to add them as numerics.

回复收藏 0 原文

~没有更多了~