记不住,放这里使用的时候参考用
事务: 使用 RollBack Tran 滚回时,当事务中SQL语句出现异常,则事务中的SQL全部不执行.
--------------------------
Create Procedure PrintClerkForOneAndOne
As
Begin
Declare @ClerkID int
Declare @ClerkName varchar
Begin Tran --开始事务
Declare myCurrsor Cursor For --声明游标
Select ClerkID,ISNULL(ClerkName,'黄杰') as ClerkName from BM_Clerk
Open myCurrsor --打开游标
Fetch Next from myCurrsor into @ClerkID,@ClerkName --读取一条游标中数据存入变量中
While @@Fetch_Status = 0 --表示读取到数据,相当于 ADO 中 Not Eof
Begin
Print @ClerkID Print @ClerkName --打印出结果
Fetch Next from myCurrsor into @ClerkID,@ClerkName --下一条,相当 ADO 中 MoveNext
End
Close myCurrsor --关闭游标
Deallocate myCurrsor --删除游标
If @@ERROR > 0 RollBack Tran --回滚事务
Commit Tran --结束事务,在使用 Commit 后,就不可以回滚事务
End
*****************************
数据库自定义函数
Create Function SelNextClerkID
(@ClerkID int)
Returns int As
Begin
Declare @NextClerkID int
If Exists(Select 1 from BM_Clerk Where ClerkID > @ClerkID) --如果不存在比 @ClerkID 大的记录,则返回 @ClerkID
Select Top 1 @NextClerkID = ClerkID from BM_Clerk Where ClerkID > @ClerkID Order By ClerkID
Else
Set @NextClerkID = @ClerkID
Return @NextClerkID
End
调用: Select Dbo.SelNextClerkID(9)
Create function getAsciiLen
(
@Str varchar(800)
)
/*
返回字符串的长度,中文2个字符,英文1个字符
*/
Returns int AS
Begin
Declare @i int
Declare @Len int
Set @i=1
Set @Len=0
While @i <= Len(@Str)
Begin
if(ascii(SubString(@Str,@i,1))>128)
Set @Len = @Len + 2
else
Set @Len = @Len + 1
Set @i = @i+1
End
Return @Len
End
*****************************
--------------------
--打印出所有没有设置主键的表
Declare @Id int
Declare @name varchar(300)
Declare myCurrsor Cursor For --声明游标
Select id,[name] from Sysobjects where xtype='U'
Open myCurrsor
Fetch Next from myCurrsor into @Id,@name --读取一条游标中数据存入变量中
While @@Fetch_Status = 0 --表示读取到数据,相当于 ADO 中 Not Eof
Begin
If Not Exists(Select 1 from Syscolumns where id=@id and typestat=1)
Print @name --打印出所有没有设置主键的表
Fetch Next from myCurrsor into @Id,@name --下一条,相当 ADO 中 MoveNext
End
Close myCurrsor --关闭游标
Deallocate myCurrsor --删除游标
-----------------
--------------------
--打印出所有主键为递增的表
Declare @Id int
Declare @name varchar(300)
Declare myCurrsor Cursor For --声明游标
Select id,[name] from Sysobjects where xtype='U'
Open myCurrsor
Fetch Next from myCurrsor into @Id,@name --读取一条游标中数据存入变量中
While @@Fetch_Status = 0 --表示读取到数据,相当于 ADO 中 Not Eof
Begin
If Exists(Select 1 from Syscolumns where id=@id and colstat=1)
Print @name --打印出所有主键为递增的表
Fetch Next from myCurrsor into @Id,@name --下一条,相当 ADO 中 MoveNext
End
Close myCurrsor --关闭游标
Deallocate myCurrsor --删除游标
----------------------
***********************************************************
SQL 每个表的作用
syscomments 记录存储过程内容的表
sysaltfiles 主数据库 保存数据库的文件
syscharsets 主数据库 字符集与排序顺序
sysconfigures 主数据库 配置选项
syscurconfigs 主数据库 当前配置选项
sysdatabases 主数据库 服务器中的数据库
syslanguages 主数据库 语言
syslogins 主数据库 登陆帐号信息
sysoledbusers 主数据库 链接服务器登陆信息
sysprocesses 主数据库 进程
sysremotelogins主数据库 远程登录帐号
syscolumns 每个数据库 列
sysconstrains 每个数据库 限制
sysfilegroups 每个数据库 文件组
sysfiles 每个数据库 文件
sysforeignkeys 每个数据库 外部关键字
sysindexs 每个数据库 索引
sysmenbers 每个数据库 角色成员
sysobjects 每个数据库 所有数据库对象
syspermissions 每个数据库 权限
systypes 每个数据库 用户定义数据类型
sysusers 每个数据库 用户
***************************************************************
备份还原
backup database CTS TO DISK='E:\aaa'
Restore DataBase CTS From Disk='E:\aaa'
************************************
每个部门取前两条记录
Select * from BM_Clerk a where ClerkID in
(Select top 2 ClerkID from BM_Clerk where DeptID=a.DeptID) order BY DeptID
**********************************************************
exec sp_executesql N'update QT_Eletricisolate Set SampNo = @SampNo ,TrustNo = @TrustNo ,Resis1 = @Resis1 ,Resis2 = @Resis2 ,Resis3 = @Resis3 ,SampType = @SampType where ID= @ID ', N'@SampNo nvarchar(4000),@TrustNo nvarchar(4000),@Resis1 nvarchar(4000),@Resis2 nvarchar(4000),@Resis3 nvarchar(4000),@SampType nvarchar(4000),@ID nvarchar(4000)', @SampNo = N'G2007(81)01301', @TrustNo = N'G2007(81)0130', @Resis1 = NULL, @Resis2 = NULL, @Resis3 = NULL, @SampType = N'54', @ID = N'3666'
******************************************
exec sp_executesql N'insert into QT_EletricRemainValue(ID,TrustNo,SampNo,SampType,Value1,Value2,Value3,Value4,Value5,Value6,Value7,Value8,Value9,Value10,Value11,Value12,Value13,Value14,Value15) values ( @ID , @TrustNo , @SampNo , @SampType , @Value1 , @Value2 , @Value3 , @Value4 , @Value5 , @Value6 , @Value7 , @Value8 , @Value9 , @Value10 , @Value11 , @Value12 , @Value13 , @Value14 , @Value15 )', N'@ID nvarchar(4000),@TrustNo nvarchar(4000),@SampNo nvarchar(4000),@SampType nvarchar(4000),@Value1 nvarchar(4000),@Value2 nvarchar(4000),@Value3 nvarchar(4000),@Value4 nvarchar(4000),@Value5 nvarchar(4000),@Value6 nvarchar(4000),@Value7 nvarchar(4000),@Value8 nvarchar(4000),@Value9 nvarchar(4000),@Value10 nvarchar(4000),@Value11 nvarchar(4000),@Value12 nvarchar(4000),@Value13 nvarchar(4000),@Value14 nvarchar(4000),@Value15 nvarchar(4000)', @ID = N'202', @TrustNo = N'G2007(81)0130', @SampNo = N'G2007(81)01301', @SampType = N'54', @Value1 = NULL, @Value2 = NULL, @Value3 = NULL, @Value4 = NULL, @Value5 = NULL, @Value6 = NULL, @Value7 = NULL, @Value8 = NULL, @Value9 = NULL, @Value10 = NULL, @Value11 = NULL, @Value12 = NULL, @Value13 = NULL, @Value14 = NULL, @Value15 = NULL
**************************************
exec sp_executesql N'delete from QT_EletricDistClimp where TrustNo = @TrustNo and SampType = @SampType ', N'@TrustNo nvarchar(4000),@SampType nvarchar(4000)', @TrustNo = N'G2007(81)0130', @SampType = N'54'
索引的创建
-- 创建测试表
Create Table MyTest
(
ID int primary key,
UserName nvarchar(32)
)
-- 创建聚集索引
Create CLUSTERED Index MyTestIndex On MyTest(UserName) ON [PRIMARY]
-- 删除索引
Drop INDEX MyTestIndex On MyTest
-- 创建非聚集索引
Create Index MyTestIndex On MyTest(UserName) ON [PRIMARY]
-- 删除索引
Drop INDEX MyTestIndex On MyTest
-- 删除测试表
Drop Table MyTest
-- 导出所有列
Declare @Columns nvarchar(2000),
@TableName nvarchar(32)
Set @Columns = ''
Set @TableName = 'OT_User'
SELECT @Columns=@Columns+','+a.name FROM syscolumns a inner join sysobjects b ON a.id=b.id where b.name=@TableName
Print @Columns