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

[yujun366]

请问以下存储过程错在哪呢？是不是 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]

[zjcxc]

2005才支持参数使用变量, 2000及以下的版本都不会

[zjcxc]

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]
')

[xeqtr1982]

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)

[xeqtr1982]

老大好快：）

[xeqtr1982]

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)

[yujun366]

谢谢楼上各位，我去试试！