带参数存储过程

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