123常用存储过程集锦.doc

常用存储过程集锦,都是一些mssql常用的一些，大家可以根据需要选择使用。=分页=/*分页查找数据*/ CREATEPROCEDUREdbo.GetRecordSet strSqlvarchar(8000),-查询sql,如select*fromuser PageIndexint,-查询当页号 PageSizeint-每页显示记录 AS setnocounton declarep1int declarecurrentPageint setcurrentPage=0 declareRowCountint setRowCount=0 declarePageCountint setPageCount=0 execsp_cursoropenp1output,strSql,scrollopt=1,ccopt=1,rowcount=rowCountoutput-得到总记录数 selectPageCount=ceiling(1.0*rowCount/pagesize)-得到总页数 ,currentPage=(PageIndex-1)*PageSize+1 selectRowCount,PageCount execsp_cursorfetchp1,16,currentPage,PageSize execsp_cursorclosep1 setnocountoff GO=用户注册= /* 用户注册，也算是添加吧 */ Createprocdbo.UserAdd ( loginIDnvarchar(50),-登录帐号 passwordnvarchar(50),-密码 emailnvarchar(200)-电子信箱 ) as declareuserIDint-用户编号 -登录账号已经被注册 ifexists(selectloginIDfromtableNamewhereloginID=loginID) begin return-1; end -邮箱已经被注册 elseifexists(selectemailfromtableNamewhereemail=email) begin return-2; end -注册成功 else begin selectuserID=isnull(max(userID),100000)+1fromtableName insertintotableName (userID,loginID,password,userName,linkNum,address,email,createTime,status) values (userID,loginID,password,email,getdate(),1) returnuserID end=sql server系统存储过程=1.给表中字段添加描述信息 CreatetableT2(idint,namechar(20) GO EXECsp_addextendedpropertyMS_Description,EmployeeID,user,dbo,table,T2,column,id EXECsp_updateextendedpropertyMS_Description,thisisatest,user,dbo,table,T2,column,id 2.修改数据库名称 EXECsp_renamedbold_db_name,new_db_name 3.修改数据表名称和字段名称 EXECsp_renameold_table_name,new_table_name修改数据表名称 EXECsp_renametable_name.old_column_name,new_column_name,COLUMN修改字段名称 4.给定存储过程名，获取存储过程内容 execsp_helptextsp_name /*以下是有关安全控制的系统存储过程或SQL语句，详细语法查阅联机丛书相关内容*/ 创建新的SQLServer登录，使用户得以连接使用SQLServer身份验证的SQLServer。 EXECsp_addloginloginame=,passwd=,defdb=,deflanguage=NULL,sid=NULL,encryptopt=NULL 使WindowsNT用户或组帐户得以使用Windows身份验证连接到SQLServer。 EXECsp_grantloginloginame= 删除SQLServer登录，以阻止使用该登录名访问SQLServer。 EXECsp_droploginloginame= 阻止WindowsNT用户或组连接到SQLServer。 EXECsp_denyloginloginame= 从SQLServer中删除用sp_grantlogin或sp_denylogin创建的WindowsNT用户或组的登录项。 EXECsp_revokeloginloginame= 更改登录的默认数据库。 EXECsp_defaultdbloginame=,defdb= 更改登录的默认语言。 EXECsp_defaultlanguageloginame=,language= 添加或更改SQLServer登录密码。 EXECsp_passwordold=,new=,loginame= 添加服务器角色新成员。 EXECsp_addsrvrolememberloginame=,rolename= 添加服务器角色某成员。 EXECsp_dropsrvrolememberloginame=,rolename= 为SQLServer登录或WindowsNT用户或组在当前数据库中添加一个安全帐户，并使其能够被授予在数据库中执行活动的权限(授予默认的“public”数据库角色)。 EXECsp_grantdbaccessloginame=,name_in_db=NULL 或 EXECsp_adduserloginame=,name_in_db=NULL,grpname= 从当前数据库中删除安全帐户。 EXECsp_revokedbaccessname_in_db= 或 EXECsp_dropusername_in_db= 在当前数据库创建新数据库角色。 EXECsp_addrolerolename=,ownername= 在当前数据库删除某数据库角色。 EXECsp_droprolerolename= 在当前数据库中添加数据库角色新成员。 EXECsp_addrolememberrolename=,membername= 在当前数据库中删除数据库角色某成员。 EXECsp_droprolememberrolename=,membername= 权限分配给数据库角色、表、存储过程等对象 1、授权访问 GRANT 2、拒绝访问 DENY 3、取消授权或拒绝 REVOKE 4、Sample(pubs): GRANTSELECTONauthorsTOLimperator DENYSELECTONauthorsTOLimperator REVOKESELECTONauthorsTOLimperator=数据库还原的存储过程=SQLcode createprockillspid(dbnamevarchar(20) as begin declaresqlnvarchar(500) declarespidint setsql=declaregetspidcursorfor selectspid fromsysprocesses wheredbid=db_id(+dbname+) exec(sql) opengetspid fetchnextfromgetspid intospid whilefetch_status-1 begin exec(kill+spid) fetchnextfromgetspid intospid end closegetspid deallocategetspid end GO作用:杀掉传入数据库中的活动进程以进行备份还原等独占操作=阿拉伯数字转大写中文=例：输入12345，程序给出：壹万贰仟叁佰肆拾伍例：输入10023040，程序给出：壹仟另贰万叁仟另肆拾解决方案之一(在SqlServer2000中测试通过)：SQLcode CREATEFUNCTIONfun_cgnum (numINT) RETURNSVARCHAR(100) AS BEGIN DECLAREtempINT,resINT,iTINYINT DECLAREstrVARCHAR(100),noVARCHAR(20),unitVARCHAR(16) SELECTstr=,no=另壹贰叁肆伍陆柒捌玖,unit=拾佰仟万拾佰仟亿 SETtemp=numSELECTi=0,res=temp%10,temp=temp/10 WHILEtemp0 BEGIN IFi=0 SETstr=SUBSTRING(no,res+1,1) ELSE SETstr=SUBSTRING(no,res+1,1)+SUBSTRING(unit,i,1)+str SELECTres=temp%10,temp=temp/10 SETi=i+1 END SETstr=SUBSTRING(no,res+1,1)+SUBSTRING(unit,i,1)+str SETstr=REPLACE(str,另拾,另) SETstr=REPLACE(str,另佰,另) SETstr=REPLACE(str,另仟,另) SETstr=REPLACE(str,另拾,另) SETstr=REPLACE(str,另万,万) WHILEi0 BEGIN SETstr=REPLACE(str,另另,另) SETi=CHARINDEX(另另,str) END SETstr=REPLACE(str,另万,万)