SQL Server常用存储过程及示例

分页:复制代码
代码如下:/*分页查找数据*/ CREATE PROCEDURE [dbo].[GetRecordSet]
@strSql varchar(8000),–查询sql,如select * from [user] @PageIndex
int,–查询当页号 @PageSize int–每页显示记录 AS set nocount on declare
@p1 int declare @currentPage int set @currentPage = 0 declare @RowCount
int set @RowCount = 0 declare @PageCount int set @PageCount = 0 exec
sp_cursoropen @p1
output,@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 where email
= @email) begin return -2; end –注册成功 else begin select @userID =
isnull(max(userID),100000)+1 from tableName 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’, ‘user’,
dbo, ‘table’, T2, ‘column’, 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
用户或组在当前数据库中添加一个安全帐户,并使其能够被授予在数据库中执行活动的权限。
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

===================阿拉伯数字转大写中文=============
例:输入12345,程序给出:壹万贰仟叁佰肆拾伍
例:输入10023040,程序给出:壹仟另贰万叁仟另肆拾 解决方案之一:复制代码 代码如下:SQL code CREATE FUNCTION
fun_cgnum (@num INT) RETURNS VARCHAR(100) AS BEGIN DECLARE @temp
INT,@res INT,@i TINYINT DECLARE @str VARCHAR(100),@no VARCHAR(20),@unit
VARCHAR(16) SELECT
@str=”,@no=’另壹贰叁肆伍陆柒捌玖’,@unit=’拾佰仟万拾佰仟亿’ SET
@temp=@num SELECT @i=0,@res=@temp%10,@temp=@temp/10 WHILE @temp0 BEGIN
IF @i=0 SET @str=SUBSTRING(@no,@res+1,1) ELSE SET
@str=SUBSTRING(@no,@res+1,1)+SUBSTRING(@unit,@i,1)+@str SELECT
@res=@temp%10,@temp=@temp/10 SET @i=@i+1 END SET
@str=SUBSTRING(@no,@res+1,1)+SUBSTRING(@unit,@i,1)+@str SET
@str=REPLACE(@str,’另拾’,’另’) SET @str=REPLACE(@str,’另佰’,’另’) SET
@str=REPLACE(@str,’另仟’,’另’) SET @str=REPLACE(@str,’另拾’,’另’) SET
@str=REPLACE(@str,’另万’,’万’) WHILE @i0 BEGIN SET
@str=REPLACE(@str,’另另’,’另’) SET @i=CHARINDEX(‘另另’,@str) END SET
@str=REPLACE(@str,’另万’,’万’) SET @str=REPLACE(@str,’亿万’,’亿’) IF
RIGHT(@str,1)=’另’ SET @str=LEFT(@str,LEN(@str)-1) RETURN @str END GO
–测试:有0和没有0的情况 SELECT
dbo.fun_cgnum(900000000),dbo.fun_cgnum(903002051),dbo.fun_cgnum(903002050)
PS:有兴趣的朋友可以继续考虑有小数点以及添加单位的情况

数据库还原的存储过程复制代码
代码如下:SQL code create proc killspid (@dbname varchar(20)) as begin
declare @sql nvarchar(500) declare @spid int set @sql=’declare getspid
cursor for select spid from sysprocesses where
dbid=db_id(”’+@dbname+”’)’ exec (@sql) open getspid fetch next from
getspid into @spid while @@fetch_status -1 begin exec(‘kill ‘+@spid)
fetch next from getspid into @spid end close getspid deallocate getspid
end GO 作用: 杀掉传入数据库中的活动以进行备份还原等独占操作

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图