首页 新闻 搜索 专区 学院

sql问题 求助

0
[已解决问题] 解决于 2013-07-04 09:43

USE [test]
GO
/****** Object: StoredProcedure [dbo].[test1] Script Date: 07/02/2013 22:59:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[test1]
@str VARCHAR(20) ,
@pageindex INT ,
@pagesize INT
AS
BEGIN
DECLARE @temp VARCHAR(4000)
DECLARE @startRow INT ,
@endRow INT
SET @startRow = ( @pageIndex - 1 ) * @pageSize + 1
SET @endRow = @startRow + @pageSize - 1
PRINT @startRow
PRINT @endRow
DECLARE @sql VARCHAR(2000)
SET @sql = ''
SET @sql += 'select *,ROW_NUMBER() over(order by noid ) AS RowNumber from sys_NoItems where 1=1'
IF @str = '1'
BEGIN
SET @sql += ' and NoSeq=5'
PRINT '@sql:' + @sql
END

SET @temp = ''
SET @temp += ' SELECT *
FROM ( ' + @sql + '
) AS T
WHERE T.RowNumber < @startRow and T.RowNumber < @endRow'
PRINT '完整:' + @temp
EXECUTE(@temp)
END

 

 

 

执行结果

1
10
@sql:select *,ROW_NUMBER() over(order by noid ) AS RowNumber from sys_NoItems where 1=1 and NoSeq=5
完整: SELECT *
FROM ( select *,ROW_NUMBER() over(order by noid ) AS RowNumber from sys_NoItems where 1=1 and NoSeq=5
) AS T
WHERE T.RowNumber < @startRow and T.RowNumber < @endRow
消息 137,级别 15,状态 2,第 4 行
必须声明标量变量 "@startRow"。

(1 行受影响)

(1 行受影响)

 

////////////////////////求解决方法

Ganler1988的主页 Ganler1988 | 初学一级 | 园豆:3
提问于:2013-07-02 23:27
< >
分享
最佳答案
0

SET @temp += ' SELECT * FROM ( ' + @sql + ' ) AS T WHERE T.RowNumber < @startRow and T.RowNumber < @endRow

修改:SET @temp += ' SELECT * FROM ( ' + @sql + ' ) AS T WHERE T.RowNumber < ' + @startRow + 'and T.RowNumber < ' + @endRow

奖励园豆:5
kylin.chen | 小虾三级 |园豆:983 | 2013-07-02 23:58

注意:@startRow类型转换 cast(@startRow as varchar(5))

kylin.chen | 园豆:983 (小虾三级) | 2013-07-03 00:08
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册