sql2005与sql2000的语法区别_sqlserver2005和2012语法差异-优快云博客

本文链接：https://blog.youkuaiyun.com/zmrdlb/article/details/5317770

这篇博客对比了SQL2005和SQL2000在创建存储过程时的语法差异，特别是在使用TOP关键字与参数结合时的处理方式。在SQL2005中，可以直接在TOP后使用变量，而在SQL2000中则需要通过动态SQL实现。此外，还提到了SQL2000中变量使用时字符串的处理注意事项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.sql2005中的存储过程

CREATE PROCEDURE [dbo].[GetNewsAllPage]
-- Add the parameters for the stored procedure here
(@PageIndex int,
@PageSize int)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
if (@PageIndex=1)
   begin
       select TOP(@PageSize) * from TableNews order by NewsId desc
   end
else
   begin
    Select TOP (@PageSize) * from TableNews where (NewsId <
    (Select Min(NewsId) From
     (Select TOP ((@PageIndex-1)*@PageSize) NewsId from TableNews order by NewsId desc)
    as T))
    order by NewsId desc
   end
END
2.sql2000中的存储过程

在SQL2000中，Select Top后是不能直接更变量的,例如：

ALTER PROCEDURE [dbo].[GetNewsAllPage]
-- Add the parameters for the stored procedure here
(@PageIndex int,
      @PageSize int)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
    declare @sql varchar(500),@count int
--    set @sql = 'select top ' + cast(@i as varchar) + ' * from table'

    -- Insert statements for procedure here
if (@PageIndex=1)
   begin
       set @sql='select top '+cast(@PageSize as varchar)+' * from TableNews order by NewsId desc'
      -- print @sql
exec(@sql)
--       select TOP(@PageSize)* from TableNews order by NewsId desc
   end
else
   begin
    set @count=(@PageIndex-1)*@PageSize
    set @sql=
    'Select TOP '+cast(@PageSize as varchar)+' * from TableNews where (NewsId <
    (Select Min(NewsId) From
     (Select TOP '+cast(@count as varchar)+' NewsId from TableNews order by NewsId desc)
    as T))
    order by NewsId desc'
--print @sql
    exec(@sql)
   end
END

3.另外在sql2000中变量的使用也应该注意'' 的使用

ALTER PROCEDURE [dbo].[GetLeaveWordPages]
-- Add the parameters for the stored procedure here
(@PageIndex int,
      @PageSize int)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
   declare @b varchar(50)
   declare @sql varchar(500),@count int
   set @b='True'

    -- Insert statements for procedure here
if (@PageIndex=1)
   begin
       set @sql='select TOP '+cast(@PageSize as varchar)+' * from TableLeaveWord where ShowWord='''+@b+''' order by LeaveWordId desc'
       exec(@sql)
   end
else
   begin
     set @count=(@PageIndex-1)*@PageSize
     set @sql=
    'Select TOP '+cast(@PageSize as varchar)+' * from TableLeaveWord where (LeaveWordId <
    (Select Min(LeaveWordId) From
     (Select TOP '+cast(@count as varchar)+' LeaveWordId from TableLeaveWord where ShowWord='''+@b+''' order by LeaveWordId desc)
    as T))
    order by LeaveWordId desc'
    exec(@sql)
   end

END