常用存儲過程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 用户或组在当前数据库中添加一个安全帐户，并

使其能够被授予在数据库中执行活动的权限（授予默认的“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函数取汉字拼音首字母

 

 

 

create function comm_getpy

 

( 

 

    @str nvarchar(4000) 

 

) 

 

returns nvarchar(4000) 

 

as 

 

begin

 

 

 

declare @word nchar(1),@PY nvarchar(4000)

 

 

 

set @PY=''

 

 

 

while len(@str)>0 

 

begin 

 

    set @word=left(@str,1)

 

 

 

    --如果非汉字字符，返回原字符 

 

    set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 

 

               then ( 

 

                            select top 1 PY 

 

                            from 

 

                            ( 

 

                             select 'A' as PY,N'驁' as word 

 

                             union all select 'B',N'簿' 

 

                             union all select 'C',N'錯' 

 

                     union all select 'D',N'鵽' 

 

                     union all select 'E',N'樲' 

 

                     union all select 'F',N'鰒' 

 

                     union all select 'G',N'腂' 

 

                     union all select 'H',N'夻' 

 

                     union all select 'J',N'攈' 

 

                     union all select 'K',N'穒' 

 

                     union all select 'L',N'鱳' 

 

                     union all select 'M',N'旀' 

 

                     union all select 'N',N'桛' 

 

                     union all select 'O',N'漚' 

 

                     union all select 'P',N'曝' 

 

                     union all select 'Q',N'囕' 

 

                     union all select 'R',N'鶸' 

 

                     union all select 'S',N'蜶' 

 

                     union all select 'T',N'籜' 

 

                     union all select 'W',N'鶩' 

 

                     union all select 'X',N'鑂' 

 

                     union all select 'Y',N'韻' 

 

                     union all select 'Z',N'咗' 

 

                      ) T 

 

                   where word>=@word collate Chinese_PRC_CS_AS_KS_WS 

 

                   order by PY ASC 

 

                          ) 

 

                      else @word 

 

                 end) 

 

    set @str=right(@str,len(@str)-1) 

 

end 

 

return @PY 

 

end

 

 

 

--------------------------------------------

 

 --获得所有非系统数据库名字

----------------------------------------------------------------------------

--- 

Select name FROM Master.. SysDatabases where dbid>4 

 

 

--获得数据库当前连接数

----------------------------------------------------------------------------

------ 

use master 

select * from sysprocesses where dbid in 

(select dbid from sysdatabases where name='数据库名字')

 

-------------------------------------------------------------------------

 

====================数据库还原的存储过程============ 

 

 

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 

 

 

 

 

 

 

作用:杀掉传入数据库中的活动进程以进行备份还原等独占操作

-------------------------------------------------------------------

 

SQL code

 

 

 

 

 

 

SELECT     

 

  表名=case   when   a.colorder=1   then   d.name   else   ''   end,   

 

  表说明=case   when   a.colorder=1   then   isnull(f.value,'')   else   ''

end,   

 

  字段序号=a.colorder,   

 

  字段名=a.name,   

 

  标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then

'√'else   ''   end,   

 

  主键=case   when   exists(SELECT   1   FROM   sysobjects   where

xtype='PK'   and   name   in   (   

 

  SELECT   name   FROM   sysindexes   WHERE   indid   in(   

 

  SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND

colid=a.colid   

 

  )))   then   '√'   else   ''   end,   

 

  类型=b.name,   

 

  占用字节数=a.length,   

 

  长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),   

 

  小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),   

 

  允许空=case   when   a.isnullable=1   then   '√'else   ''   end,   

 

  默认值=isnull(e.text,''),   

 

  字段说明=isnull(g.[value],'')   

 

  FROM   syscolumns   a   

 

  left   join   systypes   b   on   a.xusertype=b.xusertype   

 

  inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and

d.name<>'dtproperties'   

 

  left   join   syscomments   e   on   a.cdefault=e.id   

 

  left   join   sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid

 

 

  left   join   sysproperties   f   on   d.id=f.id   and   f.smallid=0   

 

where   d.name='表名'       

 

  order   by   a.id,a.colorder

 

 

 

 

----------------------------------------------------------------------------

-

楼主研究研究 ROW_NUMBER() 

-- Create date: 2008-09-27  

-- Description: 参数：用户ID，页索引，页大小，记录总数  

-- =============================================  

ALTER PROCEDURE [dbo].[WTC_SP_GetUserConfMember]  

  @ConfRoom      varchar(50),  

  @PageIndex    int,  

  @PageSize      int,  

  @RecordCount  int output  

AS  

BEGIN  

SET NOCOUNT ON  

 

select @RecordCount=count(*) from WTC_TB_USERCONFMEB  

                              where CONFROOM=@ConfRoom  

 

select * from (  

                    select

MebID,PHONENO,PHONENOTE,ADDCONFTIME,MEMBERTYPE,ADDORDER,  

                          ROW_NUMBER() OVER(ORDER BY MebID) as ROW_NUMBER

 

                            from WTC_TB_USERCONFMEB where CONFROOM=@ConfRoom

 

                  ) as a  

            where ROW_NUMBER between (@PageSize*(@PageIndex-1)+1) and

(@PageSize*(@PageIndex))  

 

END