简单SQL存储过程帮忙~~~

yujun366 2006-03-13 09:48:05
请问以下存储过程错在哪呢?是不是 TOP 后面的参数不能通过外面传递进来来确定的?谢谢

CREATE PROCEDURE [UsersList]
(@RowsToSkip int)
AS
SELECT TOP 40
[dbo].[Users].[UID], [dbo].[Users].[ULoginName],
[dbo].[Users].[UPower], [dbo].[Users].[UName],
[dbo].[Users].[UTel], [dbo].[Users].[UDepartment]
FROM [dbo].[Users]
WHERE [dbo].[Users].[UID] NOT IN
(SELECT TOP @RowsToSkip
[dbo].[Users].[UID]
FROM [dbo].[Users]
ORDER BY [dbo].[Users].[UID])
ORDER BY [dbo].[Users].[UID]
...全文
76 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
yujun366 2006-03-13
谢谢楼上各位,我去试试!
回复
xeqtr1982 2006-03-13
CREATE PROCEDURE [UsersList]
(@RowsToSkip int)
AS

declare @sql varchar(8000)
set @sql=
'SELECT TOP 40
[dbo].[Users].[UID], [dbo].[Users].[ULoginName],
[dbo].[Users].[UPower], [dbo].[Users].[UName],
[dbo].[Users].[UTel], [dbo].[Users].[UDepartment]
FROM [dbo].[Users]
WHERE [dbo].[Users].[UID] NOT IN
(SELECT TOP ' +cast(@RowsToSkip as varchar)+'
[dbo].[Users].[UID]
FROM [dbo].[Users]
ORDER BY [dbo].[Users].[UID])
ORDER BY [dbo].[Users].[UID]'
exec(@sql)
回复
xeqtr1982 2006-03-13
老大好快:)
回复
xeqtr1982 2006-03-13
CREATE PROCEDURE [UsersList]
(@RowsToSkip int)
AS

declare @sql varchar(8000)
set @sql=
'SELECT TOP 40
[dbo].[Users].[UID], [dbo].[Users].[ULoginName],
[dbo].[Users].[UPower], [dbo].[Users].[UName],
[dbo].[Users].[UTel], [dbo].[Users].[UDepartment]
FROM [dbo].[Users]
WHERE [dbo].[Users].[UID] NOT IN
(SELECT TOP ' +cast(@RowsToSkip as varchar)+'
[dbo].[Users].[UID]
FROM [dbo].[Users]
ORDER BY [dbo].[Users].[UID])
ORDER BY [dbo].[Users].[UID])'
select @sql
exec(@sql)
回复
zjcxc 2006-03-13
CREATE PROCEDURE [UsersList]
(@RowsToSkip int)
AS
exec(' SELECT TOP 40
[dbo].[Users].[UID], [dbo].[Users].[ULoginName],
[dbo].[Users].[UPower], [dbo].[Users].[UName],
[dbo].[Users].[UTel], [dbo].[Users].[UDepartment]
FROM [dbo].[Users]
WHERE [dbo].[Users].[UID] NOT IN
(SELECT TOP '+@RowsToSkip+'
[dbo].[Users].[UID]
FROM [dbo].[Users]
ORDER BY [dbo].[Users].[UID])
ORDER BY [dbo].[Users].[UID]
')
回复
zjcxc 2006-03-13
2005才支持参数使用变量, 2000及以下的版本都不会
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2006-03-13 09:48
社区公告
暂无公告