和?"sqlserver存储过程集锦（一）"?有关的　数据库　编程小帖士：

strong>LOG

??????? LOG函数返回数值的非自然对数。

　　set@PageCount = 0
　　exec sp_cursoropen @p1output,@strSql,@scrollopt=1,@ccopt=1,@rowcount=@rowCount output --得到总记录数
　　select @PageCount=ceiling(1.0*@rowCount/@pagesize) --得到总页数
　　,@currentPage=(@PageIndex-1)*@PageSize+1
　　select @RowCount,@PageCount
　　exec sp_cursorfetch @p1,16,@currentPage,@PageSize
　　exec sp_cursorclose @p1
　　set nocount off
　　GO

　　=========================用户注册============================
　　/*
　　用户注册，也算是添加吧
　　*/

　　　　Create proc[dbo].[UserAdd]

　　　　(
　　@loginID nvarchar(50),　　　　--登录帐号
　　@password nvarchar(50),--密码
　　@email nvarchar(200) --电子信箱
　　)
　　as
　　declare @userID int --用户编号
　　--登录账号已经被注册
　　if exists(select loginID from tableName where loginID= @loginID)
　　begin
　　return -1;
　　end
　　--邮箱已经被注册
　　else if exists(select email from tableName whereemail = @email)
　　begin
　　return -2;
　　end
　　--注册成功
　　else
　　begin
　　select @userID = isnull(max(userID),100000)+1 fromtableName
　　insert into tableName
　　(userID,loginID,[password],userName,linkNum,address,email,createTime,status)
　　values
　　(@userID,@loginID,@password,'',@email,getdate(),1)
　　return @userID
　　end

　　=================sql server系统存储过程================

　　–1.给表中字段添加描述信息
　　Create table T2 (id int,name char (20))
　　GO
　　EXEC sp_addextendedproperty 'MS_Description','Employee ID','user',dbo,'table',T2,'column',id
　　EXEC sp_updateextendedproperty 'MS_Description','this is a test',id
　　–2.修改数据库名称
　　EXEC sp_renamedb 'old_db_name','new_db_name'
　　–3.修改数据表名称和字段名称
　　EXEC sp_rename 'old_table_name','new_table_name'–修改数据表名称
　　EXEC sp_rename 'table_name.[old_column_name]','new_column_name','COLUMN'–修改字段名称
　　–4.给定存储过程名，获取存储过程内容
　　exec sp_helptext sp_name
　　/*以下是有关安全控制的系统存储过程或 SQL 语句，详细语法查阅《联机丛书》相关内容*/
　　–创建新的 SQL Server 登录，使用户得以连接使用 SQL Server 身份验证的 SQL Server。
　　EXEC sp_addlogin @loginame = '',@passwd = '',@defdb= '',@deflanguage = NULL,@sid = NULL,@encryptopt = NULL
　　–使 Windows NT 用户或组帐户得以使用 Windows 身份验证连接到 SQL Server。
　　EXEC sp_grantlogin @loginame = ''
　　–删除 SQL Server 登录，以阻止使用该登录名访问 SQL Server。
　　EXEC sp_droplogin @loginame = ''
　　–阻止 Windows NT 用户或组连接到 SQL Server。
　　EXEC sp_denylogin @loginame = ''
　　–从 SQL Server 中删除用 sp_grantlogin 或 sp_denylogin 创建的 Windows NT 用户或组的登录项。
　　EXEC sp_revokelogin @loginame = ''
　　–更改登录的默认数据库。
　　EXEC sp_defaultdb @loginame = '',@defdb = ''
　　–更改登录的默认语言。
　　EXEC sp_defaultlanguage @loginame = '',@language =''
　　–添加或更改 SQL Server 登录密码。
　　EXEC sp_password @old = '',@new = '',@loginame = ''
　　–添加服务器角色新成员。
　　EXEC sp_addsrvrolemember @loginame = '',@rolename =''
　　–添加服务器角色某成员。
　　EXEC sp_dropsrvrolemember @loginame = '',@rolename= ''
　　–为 SQL Server 登录或 Windows NT 用户或组在当前数据库中添加一个安全帐户，并使其能够被授予在数据库中执行活动的权限(授予默认的“public”数据库角色)。
　　EXEC sp_grantdbaccess @loginame = '',@name_in_db =NULL
　　–或
　　EXEC sp_adduser @loginame = '',@name_in_db = NULL,@grpname = ''
　　–从当前数据库中删除安全帐户。
　　EXEC sp_revokedbaccess @name_in_db = ''
　　–或
　　EXEC sp_dropuser @name_in_db = ''
　　–在当前数据库创建新数据库角色。
　　EXEC sp_addrole @rolename = '',@ownername = ''
　　–在当前数据库删除某数据库角色。
　　EXEC sp_droprole @rolename = ''
　　–在当前数据库中添加数据库角色新成员。
　　EXEC sp_addrolemember @rolename = '',@membername =''
　　–在当前数据库中删除数据库角色某成员。
　　EXEC sp_droprolemember @rolename = '',@membername =''
　　–权限分配给数据库角色、表、存储过程等对象
　　–1、授权访问
　　GRANT
　　–2、拒绝访问
　　DENY
　　–3、取消授权或拒绝
　　REVOKE
　　–4、Sample(pubs):
　　GRANT SELECT ON authors TO Limperator
　　DENY SELECT ON authors TO Limperator
　　REVOKE SELECT ON authors TO Limperator

两个sql server2000的通用分页存储过程

?

发表日期：2007-3-17 |??

?

???????????????????????

?

-

第一个支持唯一主键，第二支持多主键，测试过，效率一般

?

CREATE PROC P_viewPage

???/*

???????no_mIss 分页存储过程2007.2.20? QQ:34813284

???????适用于单一主键或存在唯一值列的表或视图?????????

???*/

?

???@TableName VARCHAR(200),???? --表名

???@FieldList VARCHAR(2000),??? --显示列名

???@PrimaryKey VARCHAR(100),??? --单一主键或唯一值键

???@Where VARCHAR(1000),??????? --查询条件 不含'where'字符

???@Order VARCHAR(1000),??????? --排序 不含'order by'字符，如id asc,userid desc，当@SortType=3时生效

???@SortType INT,?????????????? --排序规则 1:正序asc 2:倒序desc 3:多列排序

???@RecorderCount INT,????????? --记录总数 0:会返回总记录

???@PageSize INT,?????????????? --每页输出的记录数

???@PageIndex INT,????????????? --当前页数

???@TotalCount INT OUTPUT,????? --返回记录总数

???@TotalPageCount INT OUTPUT?? --返回总页数

AS

???SET NOCOUNT ON

???IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = ''

???????OR ISNULL(@PrimaryKey,'') = ''

???????OR @SortType < 1 OR @SortType >3

???????OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0

???BEGIN???????

???????RETURN

???END

?

???DECLARE @new_where1 VARCHAR(1000)

???DECLARE @new_where2 VARCHAR(1000)

???DECLARE @new_order VARCHAR(1000)??

???DECLARE @Sql VARCHAR(8000)

???DECLARE @SqlCount NVARCHAR(4000)

?

???IF ISNULL(@where,'') = ''

???????BEGIN

???????????SET @new_where1 = ' '

???????????SET @new_where2 = ' WHERE? '

???????END

???ELSE

???????BEGIN

???????????SET @new_where1 = ' WHERE ' + @where

???????????SET @new_where2 = ' WHERE ' + @where + ' AND '

???????END

?

???IF ISNULL(@order,'') = '' OR @SortType = 1 ?OR @SortType = 2

???????BEGIN

???????????IF @SortType = 1 SET @new_order = ' ORDER BY ' + @PrimaryKey + ' ASC'

???????????IF @SortType = 2 SET @new_order = ' ORDER BY ' + @PrimaryKey + ' DESC'

???????END

???ELSE

???????BEGIN

???????????SET @new_order = ' ORDER BY ' + @Order

???????END

?

???SET @SqlCount = 'SELECT@TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'

??????????????????? + CAST(@PageSize ASVARCHAR)+') FROM ' + @TableName + @new_where1

???

???IF @RecorderCount = 0

???????BEGIN

????????????EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCountINT OUTPUT',

?????????????????????????????? @TotalCountOUTPUT,@TotalPageCount OUTPUT

???????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 = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

?????????????????????? + @TableName +@new_where1 + @new_order

???????END

???ELSE

???????BEGIN

???????????IF @SortType = 1

??????????????? BEGIN

??????????????????? SET @Sql = 'SELECT TOP ' +STR(@PageSize) + ' ' + @FieldList + ' FROM '

?????????????????????????????? + @TableName +@new_where2 + @PrimaryKey + ' > '

?????????????????????????????? + '(SELECT MAX('+ @PrimaryKey + ') FROM (SELECT TOP '

?????????????????????????????? +STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey

?????????????????????????????? + ' FROM ' +@TableName

?????????????????????????????? + @new_where1 +@new_order +' ) AS TMP) '+ @new_order

??????????????? END

???????????IF @SortType = 2

??????????????? BEGIN

??????????????????? SET @Sql = 'SELECT TOP ' +STR(@PageSize) + ' ' + @FieldList + ' FROM '

?????????????????????????????? + @TableName +@new_where2 + @PrimaryKey + ' < '

?????????????????????????????? + '(SELECT MIN('+ @PrimaryKey + ') FROM (SELECT TOP '

?????????????????? ????????????+ STR(@PageSize*(@PageIndex-1)) + '' + @PrimaryKey

?????????????????????????????? +' FROM '+@TableName

?????????????????????????????? + @new_where1 +@new_order + ') AS TMP) '+ @new_order??????????????????????????????

??????????????? END?? ????

???????????IF @SortType = 3

??????????????? BEGIN

??????????????????? IF CHARINDEX(',',@Order) =0 BEGIN RETURN END

??????????????????? SET @Sql = 'SELECT TOP ' +STR(@PageSize) + ' ' + @FieldList + ' FROM '

?????????????????????????????? + @TableName +@new_where2 + @PrimaryKey + ' NOT IN (SELECT TOP '

?????????????????????????????? +STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey

?????????????????????????????? + ' FROM ' +@TableName + @new_where1 + @new_order + ')'

?????????????????????????????? + @new_order

??????????????? END

???????END

???EXEC(@Sql)

GO

?

?

?

CREATE PROC P_public_ViewPage_per

???/*

???????no_mIss 通用分页存储过程2007.3.1? QQ:34813284

???????适用于联合主键/单主键/存在能确定唯一行列/存在能确定唯一行的多列 (用英文,隔开)

???????调用：

???????????第一页查询时返回总记录和总页数及第一页记录：

???????????EXECUTE P_public_ViewPage_per'TableName','col1,col2,col3,col4','pk1,pk2,pk3',

??????????????? 'col5>0 and col7<9','pk1asc,pk2 asc,pk3 asc',10,1,

??????????????? @TotalCountOUTPUT,@TotalPageCount OUTPUT

???????????其它页调用，比如第89页(假设第一页查询时返回总记录为2000000)：

???????????EXECUTE P_public_ViewPage_per'TableName',2000000,89,@TotalPageCount OUTPUT

???*/

?

???@TableName VARCHAR(200),??? --单一主键或唯一值键或联合主键列表(用英文,隔开)或能确定唯一行的多列列表(用英文,隔开)

???@Where VARCHAR(1000),?? ?????--排序不含'order by'字符,用英文,隔开?

???@RecorderCount INT,????? --返回记录总数

???@TotalPageCount INT OUTPUT?? --返回总页数

AS

?

? ??SET NOCOUNT ON

???

???SET @FieldList = REPLACE(@FieldList,' ','')

???IF @FieldList = '*'

???????BEGIN SET @FieldList = 'A.*'END

???ELSE

???????BEGIN

???????????SET @FieldList = 'A.' + REPLACE(@FieldList,A.')

???????END

???

???WHILE CHARINDEX(',@Order)>0

???BEGIN

???????SET @Order = REPLACE(@Order,')

???END

?

???IF ISNULL(@TableName,'') = '' OR ISNULL(@PrimaryKey,'') = ''???????

???????OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0

???BEGIN???????

???????RETURN

???END

?

???DECLARE @new_where1 VARCHAR(1000)

???DECLARE @new_where2 VARCHAR(1000)

???DECLARE @new_where3 VARCHAR(1000)

???DECLARE @new_where4 VARCHAR(1000)

???DECLARE @new_order1 VARCHAR(1000)

???DECLARE @new_order2 VARCHAR(1000)

???DECLARE @Fields VARCHAR(1000)

???DECLARE @Sql VARCHAR(8000)

???DECLARE @SqlCount NVARCHAR(4000)

?

???SET @Fields = @PrimaryKey + ','

???SET @new_where2 = ''

???SET @new_where4 = ''

?

?

???IF ISNULL(@where,'') = ''

???????BEGIN

???????????SET @new_where1 = ' '

???? ???????SET @new_where3 = ' WHERE '

???????END

???ELSE

???????BEGIN???????????

???????????SET @new_where1 = ' WHERE ' + @where + ' '

???????????SET @new_where3 = ' WHERE 1=1 '

??????????????????? + REPLACE(' AND ' +@where,' AND ',' AND A.')+ ' AND '

???????END

???

???WHILE CHARINDEX(',@Fields)>0

???BEGIN

???????SET @new_where2 = @new_where2

???????????+ 'A.' + LTRIM(LEFT(@Fields,CHARINDEX(',@Fields)-1))

???????????+ ' = B.' + LTRIM(LEFT(@Fields,@Fields)-1)) + ' AND '

???????SET @new_where4 = @new_where4

???????????+ 'B.' + LTRIM(LEFT(@Fields,@Fields)-1)) + ' IS NULL AND '

???????SET @Fields = SUBSTRING(@Fields,@Fields)+1,LEN(@Fields))

???END

???SET @new_where2 = LEFT(@new_where2,LEN(@new_where2)-4)

???SET @new_where4 = LEFT(@new_where4,LEN(@new_where4)-4)

?

???IF ISNULL(@order,'') = ''

???????BEGIN

???????????SET @new_order1 = ''

???????????SET @new_order2 = ''

???????END

???ELSE

???????BEGIN

???????????SET @new_order1 = ' ORDER BY ' + @Order

???????????SET @new_order2 = ' ORDER BY '

??????????????????? + RIGHT(REPLACE(',' +@Order,A.' ),

??????????????????????????? LEN(REPLACE(',A.' ))-1)

???????END

?

???SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'

??????????????????? + CAST(@PageSize ASVARCHAR)+') FROM ' + @TableName

??????????????????? + ' A ' +? @new_where1

???

???IF @RecorderCount = 0

???????BEGIN

????????????EXEC SP_EXECUTESQL @SqlCount,@TotalPageCount OUTPUT

???????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 = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

?????????????????????? + @TableName + ' A'+@new_where1 + @new_order1

???????END

???ELSE

???????BEGIN

???????????SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

?????????????????????? + @TableName + ' A LEFTJOIN (SELECT TOP '

?????????????????????? + STR(@PageSize*(@PageIndex-1))

?????????????????????? + ' ' + @PrimaryKey + 'FROM ' + @TableName + @new_where1

?????????????????????? + @new_order1 + ' )B ON' + @new_where2 + @new_where3

?????????????????????? + @new_where4 +@new_order2

???????END

?

EXEC(@Sql)

GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!