Ø T-SQL 编程是大多数程序员都会接触的，也是数据库编程必须掌握的技术。下面，是本人在工作或学习中积累的一些心得和技巧。主要包含以下内容：
1. waitfor延时执行
2. NOT 关键字的使用
3. 向变量赋值的 SELECT 语句，总是按照顺序赋值
4. SET 与 SELECT 赋值的区别
5. 获取变量的数据类型
6. 一条 SQL 语句将一个字段的数据转为字符串
7. 根据起始和结束字符串，提取子字符串
8. 查询某数据库的所有存储过程中，是否包含指定的字符串
1. waitfor延时执行，类似于定时器、休眠等
1) 延迟执行
WAITFOR DELAY 不在50~60之间
3. 向变量赋值的 SELECT 语句，总是按照顺序赋值
DECLARE
4. SET 与 SELECT 赋值的区别
1) SET 只能赋值一个变量，而 SELECT 可以赋值多个变量。
1. SQL：
DECLARE @SetId int, @SelectId int, @SelectName varchar(20);
SET @SetId = (SELECT Id FROM (SELECT 1 AS Id, 'zhangsan' AS Name) AS T);
SELECT @SelectId = Id, @SelectName = Name FROM (SELECT 1 AS Id, 'zhangsan' AS Name) AS T;
SELECT @SetId AS SetId, @SelectId AS SelectId, @SelectName AS SelectName;
2. 结果：
2) 当返回多个值时，SET 不支持并且会报错；而 SELECT 不会，将赋值为最后一个值。
1. SQL：
DECLARE @SetId int, @SelectId int, @SelectName varchar(20);
--SET @SetId = (SELECT Id FROM (SELECT 1 AS Id, 'zhangsan' AS Name UNION SELECT 2 AS Id, 'lisi' AS Name) AS T);
SELECT @SelectId = Id, @SelectName = Name FROM (SELECT 1 AS Id, 'zhangsan' AS Name UNION SELECT 2 AS Id, 'lisi' AS Name) AS T;
SELECT @SetId AS SetId, @SelectId AS SelectId, @SelectName AS SelectName;
2. 结果：
3) 没有记录返回时，SET 赋值为 NULL，而 SELECT 则保持原来的值。
1. SQL：
DECLARE @SetId int = 10, @SelectId int = 10, @SelectName varchar(20) = 'original value';
SET @SetId = (SELECT Id FROM (SELECT 1 AS Id, 'zhangsan' AS Name) AS T WHERE 1 <> 1);
SELECT @SelectId = Id, @SelectName = Name FROM (SELECT 1 AS Id, 'zhangsan' AS Name) AS T WHERE 1 <> 1;
SELECT @SetId AS SetId, @SelectId AS SelectId, @SelectName AS SelectName;
2. 结果：
5. 获取变量的数据类型
SELECT Sql_Variant_Property(20, 'BaseType') AS Column1, Sql_Variant_Property(0.1, 'BaseType') AS Column2,Sql_Variant_Property(20 / 0.1, 'BaseType') AS Column3;
6. SQL 语句将一个字段的数据转为字符串
DECLARE @Table1 TABLE(Name varchar(20));
INSERT INTO @Table1 VALUES('a'),('b'),('c'),('d'),('e');
DECLARE @Result varchar(max);
SELECT @Result = (SELECT CAST(Name AS varchar(20)) + ',' FROM @Table1 FOR XML PATH('')), @Result = SUBSTRING(@Result, 1, LEN(@Result) - 1);
SELECT @Result AS 字符串;
7. 根据起始和结束字符串，提取子字符串，代码如下：
1) 首先，创建一张临时表并写入数据
IF(OBJECT_ID('tempdb.dbo.#Table1', 'U') IS NOT NULL)
DROP TABLE #Table1;
CREATE TABLE #Table1(Id int, Value varchar(8000));
INSERT INTO #Table1 VALUES(1, '<id>1</id><name>爱变成</name><address>湖北</address>');
2) 检索位于起始字符与结束字符之间的字符串
DECLARE @start varchar(200) = '<name>', @end varchar(200) = '</name>';
SELECT SUBSTRING(Value, CHARINDEX(@start, Value) + LEN(@start), CHARINDEX(@end, Value) - (CHARINDEX(@start, Value) + LEN(@start))) AS Name, * FROM #Table1;
3) 执行结果
8. 查询某数据库的所有存储过程中，是否包含指定的字符串
--1. 申明变量
IF(OBJECT_ID('tempdb.dbo.#Temp_Proc', 'U') IS NOT NULL)
DROP TABLE #Temp_Proc;
GO
CREATE TABLE #Temp_Proc(ProcName varchar(100), SqlText text);
DECLARE @Temp1 TABLE(SqlText text);
DECLARE @ProcName varchar(100);
DECLARE cursor_proc CURSOR FOR SELECT name FROM sys.procedures;
--2. 使用游标遍历所有存储过程
OPEN cursor_proc;
FETCH NEXT FROM cursor_proc INTO @ProcName;
WHILE(@@FETCH_STATUS = 0)
BEGIN
--先写入表变量
DELETE @Temp1; --注意：这里最好不要 DECLARE @Temp1 TABLE(SqlText text); 这样效率极低，原因不详！
INSERT INTO @Temp1 EXEC sp_helptext @ProcName;
--再写入临时表
INSERT INTO #Temp_Proc
SELECT @ProcName, SqlText FROM @Temp1;
FETCH NEXT FROM cursor_proc INTO @ProcName;
END
CLOSE cursor_proc;
DEALLOCATE cursor_proc;
--3. 查询临时表
DECLARE @PName varchar(100) = '', @PText varchar(200) = '%QueryText%';
SELECT * FROM #Temp_Proc WHERE 1=1
AND (LEN(@PName) = 0 OR ProcName = @PName)
AND SqlText LIKE @PText;