今天用Sqlserver2000写了一个存储过程 中间用到了top 结果报错,参考一下资料发现是以下问题
原程序:
create
procedure
Pr_GetUz_Nearbuilding
(
@uzoneid int , @type int , @count1 int
)
AS
begin
declare @spaceid int
set @spaceid = ( select spaceid from building where buildingid = @uzoneid )
select * from building where spaceid = @spaceid AND isbuilding = @type
end
(
@uzoneid int , @type int , @count1 int
)
AS
begin
declare @spaceid int
set @spaceid = ( select spaceid from building where buildingid = @uzoneid )
select * from building where spaceid = @spaceid AND isbuilding = @type
end
结果报以下的错误:
服务器: 消息 170,级别 15,状态 1,过程 Pr_GetUz_Nearbuilding,行 10
第 10 行: '@count1' 附近有语法错误。
后来更成下面的方式,成功执行!
create
procedure
Pr_GetUz_Nearbuilding
(
@uzoneid int , @type int , @count1 int
)
AS
begin
declare @spaceid int
set @spaceid = ( select spaceid from building where buildingid = @uzoneid )
ExEc ( ' select top ' + @count1 + ' * from building where spaceid= ' + @spaceid + ' AND isbuilding= ' + @type )
end
-- or next
create procedure Pr_GetUz_Nearbuilding
(
@uzoneid int , @type int , @count1 int
)
AS
begin
declare @spaceid int
set Rowcount @count1
set @spaceid = ( select spaceid from building where buildingid = @uzoneid )
select * from building where spaceid = @spaceid AND isbuilding = @type
end
(
@uzoneid int , @type int , @count1 int
)
AS
begin
declare @spaceid int
set @spaceid = ( select spaceid from building where buildingid = @uzoneid )
ExEc ( ' select top ' + @count1 + ' * from building where spaceid= ' + @spaceid + ' AND isbuilding= ' + @type )
end
-- or next
create procedure Pr_GetUz_Nearbuilding
(
@uzoneid int , @type int , @count1 int
)
AS
begin
declare @spaceid int
set Rowcount @count1
set @spaceid = ( select spaceid from building where buildingid = @uzoneid )
select * from building where spaceid = @spaceid AND isbuilding = @type
end
有人说 用server2005 的话是完全可以的 就是在top 后面加上()就行了。
我没有试验 。您如果有兴趣可以试验一下