sql2005与sql2000的语法区别

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