经典存储过程

企业级的存储过程:

第一 分页:

ALTER  PROC P_User_GetUserList
@PageIndex int,
@PageSize int,
@RowCount int output
AS
DECLARE @FirstIndex int,@LastIndex int
SET @FirstIndex=(@PageIndex-1)*@PageSize
SET @LastIndex=@PageIndex*@PageSize
begin
 IF EXISTS( SELECT NAME FROM SYSOBJECTS WHERE TYPE='U' AND NAME='#Table')
 DROP TABLE #Table
CREATE TABLE #Table
(
TableID int identity ,
UserName varchar(20)
)
INSERT  INTO #Table(UserName) SELECT  B.UserName FROM TBL_TEMP B
SET @RowCount=@@RowCount
SELECT b.* FROM #Table, Tbl_Temp b
Where #Table.TableID>@FirstIndex  
 AND #Table.TableID<=@LastIndex
 And #Table.UserName=b.UserName

DROP TABLE #Table
end 

第二 存储过程中 循环的应用

ALTER  PROC P_User_InitAddUser
@Count int
AS
DECLARE @i int,@UserNameTemp varchar(20)
SET @i=0
WHILE(@i<=@Count)
Begin
SET @UserNameTemp='XIAOYE'+CONVERT(char,@i)
 IF NOT EXISTS(SELECT UserName FROM Tbl_temp WHERE UserName=@UserNameTemp) 
INSERT INTO Tbl_temp(UserName,Pwd)
 VALUES(@UserNameTemp,'88305959')
SELECT * FROM TBL_TEMP
SET @i=@i+1
end