存储过程

带参数存储过程

create proc proc_roles
@uid int,
@organid int
as
begin
DECLARE @Postid int
DECLARE @rid int
select @Postid=Postid from StuOrganAndStu where U_id=@uid and Organid=@organid;
select @rid=rid from StuOrganPost where Postid=@Postid;
select * from powers where rid=@rid;
end

执行存储过程

exec  proc_roles 1,16

 

存储过程实现分页

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

CREATE PROC [dbo].[PROC_GetPageData]
@pageIndex INT ,--当前的页码值
@pageSize INT ,--每页显示的记录数.(每页显示几行数据)
@rowCount INT OUTPUT ,--表示总的记录数
@pageCount INT OUTPUT ,--表示总页数
@tableName VARCHAR(50) ,--表名
@idName VARCHAR(20) ,--排序字段
@whereStr VARCHAR(512) ,--where条件
@isDesc BIT--是否降序
AS
BEGIN
DECLARE @sql NVARCHAR(512)
DECLARE @where NVARCHAR(512)
DECLARE @orderBy NVARCHAR(50)
SET @where=''
IF @whereStr <> '' AND @whereStr IS NOT NULL
BEGIN
SET @where = @whereStr
END
IF @isDesc <> 0
BEGIN
SET @orderBy = ' desc'
END
ELSE
BEGIN
SET @orderBy = ' asc'
END
SET @sql = 'select @totalRowCount=count(*) from ' + @tableName + @where

EXEC sp_executesql @sql, N'@totalRowCount int output', @rowCount OUTPUT
SET @sql = 'select @totalPageCount=' + CONVERT(NVARCHAR, CEILING(@rowCount / CONVERT(FLOAT, @pageSize)))
EXEC sp_executesql @sql, N'@totalPageCount int output', @pageCount OUTPUT

IF @pageIndex <= 1
BEGIN

SET @pageIndex = 1
END
IF @pageIndex >= @pageCount
BEGIN

SET @pageIndex = @pageCount
END
SET @sql = 'select * from (select row_number() over(order by '
+ @idName + @orderBy + ' ) as num,* from ' + @tableName + @where
+ ') as temp where num>' + CONVERT(NVARCHAR, ( @pageIndex - 1 )
* @pageSize) + ' and num<=' + CONVERT(NVARCHAR, @pageIndex
* @pageSize)

END
EXEC sp_executesql @sql