sqlserver存储过程集锦.docx
- 文档编号:2721626
- 上传时间:2023-05-04
- 格式:DOCX
- 页数:14
- 大小:19.28KB
sqlserver存储过程集锦.docx
《sqlserver存储过程集锦.docx》由会员分享,可在线阅读,更多相关《sqlserver存储过程集锦.docx(14页珍藏版)》请在冰点文库上搜索。
sqlserver存储过程集锦
sqlserver存储过程集锦
(一)
常用存储过程集锦,都是一些mssql常用的一些,大家可以根据需要选择使用。
=================分页==========================
/*分页查找数据*/
CREATEPROCEDURE[dbo].[GetRecordSet]
@strSqlvarchar(8000),--查询sql,如select*from[user]
@PageIndexint,--查询当页号
@PageSizeint--每页显示记录
AS
setnocounton
declare@p1int
declare@currentPageint
set@currentPage=0
declare@RowCountint
set@RowCount=0
declare@PageCountint
和 "sqlserver存储过程集锦
(一)" 有关的 数据库 编程小帖士:
strong>LOG
LOG函数返回数值的非自然对数。
set@PageCount=0
execsp_cursoropen@p1output,@strSql,@scrollopt=1,@ccopt=1,@rowcount=@rowCountoutput--得到总记录数
select@PageCount=ceiling(1.0*@rowCount/@pagesize)--得到总页数
,@currentPage=(@PageIndex-1)*@PageSize+1
select@RowCount,@PageCount
execsp_cursorfetch@p1,16,@currentPage,@PageSize
execsp_cursorclose@p1
setnocountoff
GO
=========================用户注册============================
/*
用户注册,也算是添加吧
*/
Createproc[dbo].[UserAdd]
(
@loginIDnvarchar(50), --登录帐号
@passwordnvarchar(50),--密码
@emailnvarchar(200)--电子信箱
)
as
declare@userIDint--用户编号
--登录账号已经被注册
ifexists(selectloginIDfromtableNamewhereloginID=@loginID)
begin
return-1;
end
--邮箱已经被注册
elseifexists(selectemailfromtableNamewhereemail=@email)
begin
return-2;
end
--注册成功
else
begin
select@userID=isnull(max(userID),100000)+1fromtableName
insertintotableName
(userID,loginID,[password],userName,linkNum,address,email,createTime,status)
values
(@userID,@loginID,@password,'','','',@email,getdate(),1)
return@userID
end
=================sqlserver系统存储过程================
–1.给表中字段添加描述信息
CreatetableT2(idint,namechar(20))
GO
EXECsp_addextendedproperty'MS_Description','EmployeeID','user',dbo,'table',T2,'column',id
EXECsp_updateextendedproperty'MS_Description','thisisatest','user',dbo,'table',T2,'column',id
–2.修改数据库名称
EXECsp_renamedb'old_db_name','new_db_name'
–3.修改数据表名称和字段名称
EXECsp_rename'old_table_name','new_table_name'–修改数据表名称
EXECsp_rename'table_name.[old_column_name]','new_column_name','COLUMN'–修改字段名称
–4.给定存储过程名,获取存储过程内容
execsp_helptextsp_name
/*以下是有关安全控制的系统存储过程或SQL语句,详细语法查阅《联机丛书》相关内容*/
–创建新的SQLServer登录,使用户得以连接使用SQLServer身份验证的SQLServer。
EXECsp_addlogin@loginame='',@passwd='',@defdb='',@deflanguage=NULL,@sid=NULL,@encryptopt=NULL
–使WindowsNT用户或组帐户得以使用Windows身份验证连接到SQLServer。
EXECsp_grantlogin@loginame=''
–删除SQLServer登录,以阻止使用该登录名访问SQLServer。
EXECsp_droplogin@loginame=''
–阻止WindowsNT用户或组连接到SQLServer。
EXECsp_denylogin@loginame=''
–从SQLServer中删除用sp_grantlogin或sp_denylogin创建的WindowsNT用户或组的登录项。
EXECsp_revokelogin@loginame=''
–更改登录的默认数据库。
EXECsp_defaultdb@loginame='',@defdb=''
–更改登录的默认语言。
EXECsp_defaultlanguage@loginame='',@language=''
–添加或更改SQLServer登录密码。
EXECsp_password@old='',@new='',@loginame=''
–添加服务器角色新成员。
EXECsp_addsrvrolemember@loginame='',@rolename=''
–添加服务器角色某成员。
EXECsp_dropsrvrolemember@loginame='',@rolename=''
–为SQLServer登录或WindowsNT用户或组在当前数据库中添加一个安全帐户,并使其能够被授予在数据库中执行活动的权限(授予默认的“public”数据库角色)。
EXECsp_grantdbaccess@loginame='',@name_in_db=NULL
–或
EXECsp_adduser@loginame='',@name_in_db=NULL,@grpname=''
–从当前数据库中删除安全帐户。
EXECsp_revokedbaccess@name_in_db=''
–或
EXECsp_dropuser@name_in_db=''
–在当前数据库创建新数据库角色。
EXECsp_addrole@rolename='',@ownername=''
–在当前数据库删除某数据库角色。
EXECsp_droprole@rolename=''
–在当前数据库中添加数据库角色新成员。
EXECsp_addrolemember@rolename='',@membername=''
–在当前数据库中删除数据库角色某成员。
EXECsp_droprolemember@rolename='',@membername=''
–权限分配给数据库角色、表、存储过程等对象
–1、授权访问
GRANT
–2、拒绝访问
DENY
–3、取消授权或拒绝
REVOKE
–4、Sample(pubs):
GRANTSELECTONauthorsTOLimperator
DENYSELECTONauthorsTOLimperator
REVOKESELECTONauthorsTOLimperator
两个sqlserver2000的通用分页存储过程
发表日期:
2007-3-17|
-
第一个支持唯一主键,第二支持多主键,测试过,效率一般
CREATEPROCP_viewPage
/*
no_mIss分页存储过程2007.2.20QQ:
34813284
适用于单一主键或存在唯一值列的表或视图
*/
@TableNameVARCHAR(200),--表名
@FieldListVARCHAR(2000),--显示列名
@PrimaryKeyVARCHAR(100),--单一主键或唯一值键
@WhereVARCHAR(1000),--查询条件不含'where'字符
@OrderVARCHAR(1000),--排序不含'orderby'字符,如idasc,useriddesc,当@SortType=3时生效
@SortTypeINT,--排序规则1:
正序asc2:
倒序desc3:
多列排序
@RecorderCountINT,--记录总数0:
会返回总记录
@PageSizeINT,--每页输出的记录数
@PageIndexINT,--当前页数
@TotalCountINTOUTPUT,--返回记录总数
@TotalPageCountINTOUTPUT--返回总页数
AS
SETNOCOUNTON
IFISNULL(@TableName,'')=''ORISNULL(@FieldList,'')=''
ORISNULL(@PrimaryKey,'')=''
OR@SortType<1OR@SortType>3
OR@RecorderCount<0OR@PageSize<0OR@PageIndex<0
BEGIN
RETURN
END
DECLARE@new_where1VARCHAR(1000)
DECLARE@new_where2VARCHAR(1000)
DECLARE@new_orderVARCHAR(1000)
DECLARE@SqlVARCHAR(8000)
DECLARE@SqlCountNVARCHAR(4000)
IFISNULL(@where,'')=''
BEGIN
SET@new_where1=''
SET@new_where2='WHERE'
END
ELSE
BEGIN
SET@new_where1='WHERE'+@where
SET@new_where2='WHERE'+@where+'AND'
END
IFISNULL(@order,'')=''OR@SortType=1OR@SortType=2
BEGIN
IF@SortType=1SET@new_order='ORDERBY'+@PrimaryKey+'ASC'
IF@SortType=2SET@new_order='ORDERBY'+@PrimaryKey+'DESC'
END
ELSE
BEGIN
SET@new_order='ORDERBY'+@Order
END
SET@SqlCount='SELECT@TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+CAST(@PageSizeASVARCHAR)+')FROM'+@TableName+@new_where1
IF@RecorderCount=0
BEGIN
EXECSP_EXECUTESQL@SqlCount,N'@TotalCountINTOUTPUT,@TotalPageCountINTOUTPUT',
@TotalCountOUTPUT,@TotalPageCountOUTPUT
END
ELSE
BEGIN
SELECT@TotalCount=@RecorderCount
END
IF@PageIndex>CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
SET@PageIndex=CEILING((@TotalCount+0.0)/@PageSize)
END
IF@PageIndex=1
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM'
+@TableName+@new_where1+@new_order
END
ELSE
BEGIN
IF@SortType=1
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM'
+@TableName+@new_where2+@PrimaryKey+'>'
+'(SELECTMAX('+@PrimaryKey+')FROM(SELECTTOP'
+STR(@PageSize*(@PageIndex-1))+''+@PrimaryKey
+'FROM'+@TableName
+@new_where1+@new_order+')ASTMP)'+@new_order
END
IF@SortType=2
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM'
+@TableName+@new_where2+@PrimaryKey+'<'
+'(SELECTMIN('+@PrimaryKey+')FROM(SELECTTOP'
+STR(@PageSize*(@PageIndex-1))+''+@PrimaryKey
+'FROM'+@TableName
+@new_where1+@new_order+')ASTMP)'+@new_order
END
IF@SortType=3
BEGIN
IFCHARINDEX(',',@Order)=0BEGINRETURNEND
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM'
+@TableName+@new_where2+@PrimaryKey+'NOTIN(SELECTTOP'
+STR(@PageSize*(@PageIndex-1))+''+@PrimaryKey
+'FROM'+@TableName+@new_where1+@new_order+')'
+@new_order
END
END
EXEC(@Sql)
GO
CREATEPROCP_public_ViewPage_per
/*
no_mIss通用分页存储过程2007.3.1QQ:
34813284
适用于联合主键/单主键/存在能确定唯一行列/存在能确定唯一行的多列(用英文,隔开)
调用:
第一页查询时返回总记录和总页数及第一页记录:
EXECUTEP_public_ViewPage_per'TableName','col1,col2,col3,col4','pk1,pk2,pk3',
'col5>0andcol7<9','pk1asc,pk2asc,pk3asc',0,10,1,
@TotalCountOUTPUT,@TotalPageCountOUTPUT
其它页调用,比如第89页(假设第一页查询时返回总记录为2000000):
EXECUTEP_public_ViewPage_per'TableName','col1,col2,col3,col4','pk1,pk2,pk3',
'col5>0andcol7<9','pk1asc,pk2asc,pk3asc',2000000,10,89,
@TotalCountOUTPUT,@TotalPageCountOUTPUT
*/
@TableNameVARCHAR(200),--表名
@FieldListVARCHAR(2000),--显示列名
@PrimaryKeyVARCHAR(100),--单一主键或唯一值键或联合主键列表(用英文,隔开)或能确定唯一行的多列列表(用英文,隔开)
@WhereVARCHAR(1000),--查询条件不含'where'字符
@OrderVARCHAR(1000),--排序不含'orderby'字符,用英文,隔开
@RecorderCountINT,--记录总数0:
会返回总记录
@PageSizeINT,--每页输出的记录数
@PageIndexINT,--当前页数
@TotalCountINTOUTPUT,--返回记录总数
@TotalPageCountINTOUTPUT--返回总页数
AS
SETNOCOUNTON
SET@FieldList=REPLACE(@FieldList,'','')
IF@FieldList='*'
BEGINSET@FieldList='A.*'END
ELSE
BEGIN
SET@FieldList='A.'+REPLACE(@FieldList,',',',A.')
END
WHILECHARINDEX(',',@Order)>0
BEGIN
SET@Order=REPLACE(@Order,',',',')
END
IFISNULL(@TableName,'')=''ORISNULL(@PrimaryKey,'')=''
OR@RecorderCount<0OR@PageSize<0OR@PageIndex<0
BEGIN
RETURN
END
DECLARE@new_where1VARCHAR(1000)
DECLARE@new_where2VARCHAR(1000)
DECLARE@new_where3VARCHAR(1000)
DECLARE@new_where4VARCHAR(1000)
DECLARE@new_order1VARCHAR(1000)
DECLARE@new_order2VARCHAR(1000)
DECLARE@FieldsVARCHAR(1000)
DECLARE@SqlVARCHAR(8000)
DECLARE@SqlCountNVARCHAR(4000)
SET@Fields=@PrimaryKey+','
SET@new_where2=''
SET@new_where4=''
IFISNULL(@where,'')=''
BEGIN
SET@new_where1=''
SET@new_where3='WHERE'
END
ELSE
BEGIN
SET@new_where1='WHERE'+@where+''
SET@new_where3='WHERE1=1'
+REPLACE('AND'+@where,'AND','ANDA.')+'AND'
END
WHILECHARINDEX(',',@Fields)>0
BEGIN
SET@new_where2=@new_where2
+'A.'+LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1))
+'=B.'+LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1))+'AND'
SET@new_where4=@new_where4
+'B.'+LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1))+'ISNULLAND'
SET@Fields=SUBSTRING(@Fields,CHARINDEX(',',@Fields)+1,LEN(@Fields))
END
SET@new_where2=LEFT(@new_where2,LEN(@new_
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sqlserver 存储 过程 集锦