1、登录验证:
CREATE procedure proc_logon (@username nvarchar(50), --账号 @password nvarchar(50), --密码 @reValue int output --返回结果 ) AS if exists(select id from P_login where username=@username) begin if exists(select id from P_login where username=@username and password=@password) set @reValue=0 --通过验证 else set @reValue=1 --用户存在,密码不正确 end else set @reValue=2 --用户不存在 --return isnull(@reValue,3) GO
执行:
declare @reValue int exec proc_logon @username='test',@password='test',@reValue=@reValue output --如果存储过程里写的是:return @reValue的话,那执行完上面的语句,还要执行:print @reValue才能打印出结果
2、通用分页:
CREATE proc proc_Pagination @tblName nvarchar(30), -- 需要查询的表名 @selectField nvarchar(100) = '*', -- 需要返回的列 @pageSize int = 10, -- 每页显示的行数 @pageIndex int = 1, -- 页码 @primaryKey nvarchar(250), -- 主键 @where nvarchar(250) = '', -- 查询条件 (注意: 执行时不要加 where) @orderBy nvarchar(250)='', --排序字段 @orderType nvarchar(250)='', --排序方式,为空还是desc @totalCount int output --总记录数 as set nocount on declare @PageLowerBound int declare @PageUpperBound int declare @createNewTablelStr nvarchar(1000) declare @selectStr nvarchar(1000) begin create table #indextable(id int primary key identity(1,1),nid int) if @where='' begin set @where='1=1' end set @PageLowerBound=(@pageIndex-1)*@pageSize set @PageUpperBound=@PageLowerBound+@pageSize set @createNewTablelStr='insert into #indextable(nid) select '+ @primaryKey +' from [' + @tblName+ '] where '+@where + ' order by ' + @orderBy + ' ' + @orderType exec (@createNewTablelStr) select @totalCount=count(*) from #indextable --或: set @tatalCount=@@rowcount set @selectStr='select '+ @selectField +' from ['+@tblName +'] O,#indextable t where O.'+@primaryKey+ '=t.nid and t.id between '+ convert(varchar(12),@PageLowerBound+1) +' and '+ convert(varchar(12),@PageUpperBound) +' order by t.id' end exec(@selectStr) delete #indextable set nocount off GO
执行:
declare @totalCount int exec proc_pagination @tblName='P_RecordUserInfo',@selectField='o.id,userip',@pageSize=10,@pageIndex=2,@primaryKey='id',@where='id>10',@orderBy='id',@orderType='asc',@totalCount=@totalCount