ALTER PROCEDURE Up_Get_UserBaseInfo_List
(
@User_Type varchar(10),
@PageSize int,
@MaxId int
)
AS
if @MaxId =0
begin
exec ('select top '+@PageSize+' [User_Id],UserBase_Email,UserBase_QQ,UserBase_Tel,UserBase_RealName,UserBase_Birthday,UserBase_Sex,UserBase_Address
from V_UserDetails where User_UIfuVerify=1 and User_Type='+@User_Type)
end
else
exec ( 'select top '+@PageSize+' [User_Id],UserBase_Email,UserBase_QQ,UserBase_Tel,UserBase_RealName,UserBase_Birthday,UserBase_Sex,UserBase_Address
from V_UserDetails where User_UIfuVerify=1 and User_Type='+@User_Type+' and ([User_Id]>(select max([User_Id]) from (select Top '+@MaxId+' [User_Id] from V_UserDetails where User_Type='+@User_Type+') as t))')
SQL SERVER 2000不支持 TOP + 变量 只能用动态SQL,即:在exec('')中执行语句! 注意:top与变量之间 ,变量与字段名之间,必须有一个空格,否则报错!
于是 SQL SERVER 2005 支持这个功能了!
关于(sql2005/sql2008):如下:
Alter proc getWorkPlan2
(
@intCounter int
, @lngUserID int
)
as
set rowcount @intCounter
select lngWorkID,strWorkName,strExecHumanName,strBeginDate
from worklist where lngExecHumanID = @lngUserID
order by lngWorkID desc
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/downmoon/archive/2006/04/12/660557.aspx
http://www.cnblogs.com/tianlong0072/archive/2007/10/28/940766.html