SQL集合

来源：互联网发布：帝王三国破解版无网络编辑：程序博客网时间：2024/06/18 14:39

管理员专用连接　DAC
一、什么是DAC
SQL Server 2005 为管理员提供了一种特殊的诊断连接，以供在无法与服务器建立标准连接时使用。即使在 SQL Server 不响应标准连接请求时，管理员也可以使用这种连接访问 SQL Server，以便执行诊断查询并解决问题。命令行界面 (sqlcmd) 通过使用特殊的管理员开关 (-A)，提供并支持这种专用管理员连接 (DAC)。

二、如何使用DAC登录到服务器
1 本机DAC登录
命令行方式下执行 sqlcmd -A -S sql服务器名
2 远程DAC登录
1)打开远程DAC选项
EXEC sp_configure ‘remote admin connections’, 1;
RECONFIGURE;
2)登录到远程服务器
sqlcmd -A -S 192.168.0.1 -U sa -P 123456
3 登录后, 可以通过查询动态管理视图来诊断问题

三、SQL Server Management Studio 与 DAC 配合使用

1、在 SQL Server Management Studio 中，不打开其他 DAC，单击工具栏上的“数据库引擎查询”。

2、在“连接到数据库引擎”对话框的“服务器名称”框中，键入 ADMIN:，并在其后继续键入服务器实例的名称。例如，若要连接到名为 ACCT\PAYABLE 的服务器实例，请键入 ADMIN:ACCT\PAYABLE。

3、完成“身份验证”部分（提供 sysadmin 组成员的凭据），然后单击“连接”。将建立连接。

如果已在使用 DAC，则连接将失败，并出现错误指示无法连接。

目前这个存储过程只能解密存储过程，

至于解密函数、触发器、视图的存储过程本网站会进一步关注，调用形式为：

exec dbo.sp__windbi$decrypt @procedure,0

如果第二个参数使用的话，会给出该存储过程的一些提示。

–版本.0 修正存储过程过长引起的问题

警告：该存储过程会删除并重建原始的存储过程。’

在运行该存储过程之前确保你的数据库有一个备份。’

该存储过程通常应该运行在产品环境的一个备份的非产品环境下。

/**********************************************************************/

print ‘creating stored procedure sp__windbi$decrypt……’
go
if exists(select name from sysobjects where name = ’sp__windbi$decrypt’ and type = ‘P’)
drop procedure sp__windbi$decrypt
go

create PROCEDURE [dbo].[sp__windbi$decrypt]
(@procedure sysname = NULL)
AS
SET NOCOUNT ON
DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@procNameLength int
select @maxColID = max(subobjid) FROM
sys.sysobjvalues WHERE objid = object_id(@procedure)
–select @maxColID as ‘Rows in sys.sysobjvalues’
select @procNameLength = datalength(@procedure) + 29
DECLARE @real_01 nvarchar(max)
DECLARE @fake_01 nvarchar(max)
DECLARE @fake_encrypt_01 nvarchar(max)
DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)
declare @objtype varchar(2),@parentname nvarchar(max)
select @real_decrypt_01a = ”
–提取对象的类型如是存储过程还是函数，如果是触发器，还要得到其父对象的名称
select @objtype=type,@parentname=object_name(parent_object_id)
from sys.objects where [object_id]=object_id(@procedure)
– 从sys.sysobjvalues里提出加密的imageval记录
SET @real_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 order by subobjid)
–创建一个临时表
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX) )
–开始一个事务，稍后回滚
BEGIN TRAN
–更改原始的存储过程，用短横线替换
if @objtype=’P'
SET @fake_01=’ALTER PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(cast(‘-’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)
else if @objtype=’FN’
SET @fake_01=’ALTER FUNCTION ‘+ @procedure +’() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
/*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+’*/ END’
else if @objtype=’V'
SET @fake_01=’ALTER view ‘+ @procedure +’ WITH ENCRYPTION AS select 1 as col
/*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+’*/’
else if @objtype=’TR’
SET @fake_01=’ALTER trigger ‘+ @procedure +’ ON ‘+@parentname+’WITHENCRYPTION AFTER INSERT AS RAISERROR (”N”,16,10)
/*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+’*/’
EXECUTE (@fake_01)
–从sys.sysobjvalues里提出加密的假的
SET @fake_encrypt_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 order by subobjid )
if @objtype=’P'
SET @fake_01=’Create PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(cast(‘-’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)
else if @objtype=’FN’
SET @fake_01=’CREATE FUNCTION ‘+ @procedure +’() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
/*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+’*/ END’
else if @objtype=’V'
SET @fake_01=’Create view ‘+ @procedure +’ WITH ENCRYPTION AS select 1 as col
/*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+’*/’
else if @objtype=’TR’
SET @fake_01=’Create trigger ‘+ @procedure +’ ON ‘+@parentname+’WITHENCRYPTION AFTER INSERT AS RAISERROR (”N”,16,10)
/*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+’*/’
–开始计数
SET @intProcSpace=1
–使用字符填充临时变量
SET @real_decrypt_01 = replicate(cast(‘A’as nvarchar(max)), (datalength(@real_01) /2 ))
–循环设置每一个变量，创建真正的变量
–每次一个字节
SET @intProcSpace=1
–如有必要，遍历每个@real_xx变量并解密
WHILE @intProcSpace <=(datalength(@real_01)/2)
BEGIN
–真的和假的和加密的假的进行异或处理
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END
–通过sp_helptext逻辑向表#output里插入变量
insert #output (real_decrypt) select @real_decrypt_01
– select real_decrypt AS ‘#output chek’ from #output –测试
– ————————————-
–开始从sp_helptext提取
– ————————————-
declare @dbname sysname
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int –回车换行的长度
,@DefinedLength int
,@SyscomText nvarchar(max)
,@Line nvarchar(255)
Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0 –跟踪行结束的空格。注意Len函数忽略了多余的空格
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate database_default)
–使用#output代替sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT real_decrypt from #output
ORDER BY ident
FOR READ ONLY
–获取文本
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT FROM ms_crs_syscom into @SyscomText
WHILE @@fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
–通过回车查找行的结束
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,
@BasePos)
–如果找到回车
IF @CurrentPos != 0
BEGIN
–如果@Lines的长度的新值比设置的大就插入@Lines目前的内容并继续
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +
@BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N”)
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
–如果回车没找到
BEGIN
IF @BasePos <= @TextLength
BEGIN
–如果@Lines长度的新值大于定义的长度
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength -
(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N”)
if LEN(@Line) < @DefinedLength and charindex(‘ ‘,
@SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ‘ ‘, @BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT FROM ms_crs_syscom into @SyscomText
END
IF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line )
select Text from #CommentText order by LineId
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText
– ————————————-
–结束从sp_helptext提取
– ————————————-
–删除用短横线创建的存储过程并重建原始的存储过程
ROLLBACK TRAN
DROP TABLE #output

×××××××××××××××××××××××××××××××××××××××××××××1××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

描述
返回两个日期之间的时间间隔。
语法
DateDiff(interval, date1, date2 [,firstdayofweek[, firstweekofyear]])
DateDiff 函数的语法有以下参数：

参数描述
interval 必选。字符串表达式，表示用于计算 date1 和 date2 之间的时间间隔。
有关数值，请参阅“设置”部分。
date1, date2 必选。日期表达式。用于计算的两个日期。
firstdayofweek 可选。指定星期中第一天的常数。如果没有指定，则默认为星期日。
有关数值，请参阅“设置”部分。
firstweekofyear 可选。指定一年中第一周的常数。如果没有指定，则默认为 1 月 1 日所在的星期。
有关数值，请参阅“设置”部分。

设置
interval 参数可以有以下值：
设置描述
yyyy 年
q 季度
m 月
y 一年的日数
d 日
w 一周的日数
ww 周
h 小时
m 分钟
s 秒

firstdayofweek 参数可以有以下值：

常数值描述
vbUseSystem 0 使用区域语言支持 (NLS) API 设置。
vbSunday 1 星期日（默认）
vbMonday 2 星期一
vbTuesday 3 星期二
vbWednesday 4 星期三
vbThursday 5 星期四
vbFriday 6 星期五
vbSaturday 7 星期六

firstweekofyear 参数可以有以下值：

常数值描述
vbUseSystem 0 使用区域语言支持 (NLS) API 设置。
vbFirstJan1 1 由 1 月 1 日所在的星期开始（默认）。
vbFirstFourDays 2 由在新年中至少有四天的第一周开始。
vbFirstFullWeek 3 由在新的一年中第一个完整的周开始。

说明
DateDiff 函数用于判断在两个日期之间存在的指定时间间隔的数目。
例如可以使用 DateDiff 计算两个日期相差的天数，或者当天到当年最后一天之间的星期数。
要计算 date1 和 date2 相差的天数，可以使用“一年的日数”（“y”）或“日”（“d”）。
当 interval 为“一周的日数”（“w”）时，DateDiff 返回两个日期之间的星期数。
如果 date1 是星期一，则 DateDiff 计算到 date2 之前星期一的数目。
此结果包含 date2 而不包含 date1。
如果 interval 是“周”（“ww”），则 DateDiff 函数返回日历表中两个日期之间的星期数。
函数计算 date1 和 date2 之间星期日的数目。
如果 date2 是星期日，DateDiff 将计算 date2，但即使 date1 是星期日，也不会计算 date1。

如果 date1 晚于 date2，则 DateDiff 函数返回负数。

firstdayofweek 参数会对使用“w”和“ww”间隔符号的计算产生影响。

如果 date1 或 date2 是日期文字，则指定的年度会成为日期的固定部分。
但是如果 date1 或 date2 被包括在引号 (" ") 中并且省略年份，则在代码中每次计算 date1 或 date2 表达式时，将插入当前年份。
这样就可以编写适用于不同年份的程序代码。

在 interval 为“年”（“yyyy”）时，比较 12 月 31 日和来年的 1 月 1 日，虽然实际上只相差一天，DateDiff 返回 1 表示相差一个年份。

×××××××××××××××××××××××××××××××××××××××××××××2××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

excel导入到sql时的数据类型问题

这里介绍其他方法实现你的要求，

导入excel时，让系统自动创建表，自动选择数据类型，导入为一张临时表，
正常导入到数据库后，在数据库中实现把临时表的数据插入到目标表，

这样做的好处有2点，
能够迅速从excel数据转为数据库数据，
第二，可以用SQL脚本控制从临时表到目标表的数据迁移，操作更加灵活。

×××××××××××××××××××××××××××××××××××××××××××××3××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

inner join(等值连接) 只返回两个表中联结字段相等的行
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
INNER JOIN 语法：
INNER JOIN 连接两个数据表的用法：
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

INNER JOIN 连接三个数据表的用法：
SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

INNER JOIN 连接四个数据表的用法：
SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号)
INNER JOIN 表4 ON Member.字段号=表4.字段号

INNER JOIN 连接五个数据表的用法：
SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号)
INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号

连接六个数据表的用法：略，与上述联接方法类似，大家举一反三吧：）

注意事项：
?在输入字母过程中，一定要用英文半角标点符号，单词之间留一半角空格；
?在建立数据表时，如果一个表与多个表联接，那么这一个表中的字段必须是“数字”数据类型，而多个表中的相同字段必须是主键，而且是“自动编号”数据类型。
否则，很难联接成功。
?代码嵌套快速方法：如，想连接五个表，则只要在连接四个表的代码上加一个前后括号（前括号加在FROM的后面，后括号加在代码的末尾即可），
然后在后括号后面继续添加“INNER JOIN 表名X ON 表1.字段号=表X.字段号”代码即可，这样就可以无限联接数据表了：）

×××××××××××××××××××××××××××××××××××××××××××××4××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

SQL INNER JOIN 关键字
在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
INNER JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释：INNER JOIN 与 JOIN 是相同的。
原始的表 (用在例子中的)：
"Persons" 表：
Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing
"Orders" 表：
Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65
内连接（INNER JOIN）实例
现在，我们希望列出所有人的定购。
您可以使用下面的 SELECT 语句：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果集：
LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "Persons" 中的行在 "Orders" 中没有匹配，就不会列出这些行。

×××××××××××××××××××××××××××××××××××××××××××××5××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
LEFT JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释：在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。
原始的表 (用在例子中的)：
"Persons" 表：
Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing
"Orders" 表：
Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65
左连接（LEFT JOIN）实例
现在，我们希望列出所有的人，以及他们的定购 - 如果有的话。
您可以使用下面的 SELECT 语句：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果集：
LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
Bush George
LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。

×××××××××××××××××××××××××××××××××××××××××××××6××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；
而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大，用not exists都比not in要快。

----------------
not in 逻辑上不完全等同于not exists

not (不) exists (存在)
select * from a where not exists (select 1 from b where b.id = a.id)
exists 子查询和 in 子查询的区别之一就在于不直接返回结果集，而是判断结果集条数是否大于零返回true/false，
因此 select 1,select 2,select 什么都可以。

------------------------
就是不在这个范围的意思
select idfrom tablewhere name not exists(select 1
    from table
     where name = 'aaa')
也许你看不懂这个1的意思，不过在使用EXISTS的时候，通常都会使用1，它代表所查询出来的集合，等同于select name。
整个例子的意思是：查询name不等于aaa的所有ID。
--------------
注意in只允许有一个字段返回

--------------------
EXISTS：

系统要求进行SQL优化，对效率比较低的SQL进行优化，使其运行效率更高，其中要求对SQL中的部分in/not in修改为exists/not exists

修改方法如下：

in的SQL语句

SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
FROM tab_oa_pub WHERE is_check=1 and
category_id in (select id from tab_oa_pub_cate where no='1')
order by begintime desc

修改为exists的SQL语句
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
FROM tab_oa_pub WHERE is_check=1 and
exists (select id from tab_oa_pub_cate where tab_oa_pub.category_id=convert(int,no) and no='1')
order by begintime desc
--------------------------------

×××××××××××××××××××××××××××××××××××××××××××××7××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

一.存储过程中判别记录是否存在

1.--判断用户的ID和密码是否存在
if(exists(select * from UserInfo where UserId=@userId and UserPwd=@oldPwd))
begin
update UserInfo set UserPwd=@newPwd whereUserId=@userId
set @result=1
end
else
begin
set @result=-1
end

2.--设置新的密码
declare @password nvarchar(50)
select @password = userpwd from userinfo where userID = @userId
if @password = @oldpassword
begin
update userinfo set userPwd = @newPassword where userId = @userId
set @result = 1
end
else
begin
set @result = -1
end

3.--由用户名称获取用户ID
select userId from UserInfo where UserName=@userName
通过类型转换后判断userId的值，若等于0就表示不存在。

二。程序中写SQL语句时，变量的使用。
如cmd.ComandText="select * from NewsDetail where NewsTypeId="+this.newsTypeId;
或着cmd.ComandText="select * from NewsDetail where NewsTypeId='"+this.newsTypeId+"'";(两单引号内加两双引号)

三.存储过程中如何获取自动增加的值
select @orderId=@@identity

四.isnull的使用。
ISNULL
使用指定的替换值替换 NULL。
语法
ISNULL ( check_expression , replacement_value )

参数
check_expression

将被检查是否为 NULL的表达式。check_expression 可以是任何类型的。

replacement_value

在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。

返回类型
返回与 check_expression 相同的类型。

注释
如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value。

示例
A. 将 ISNULL 与 AVG 一起使用
下面的示例查找所有书的平均价格，用值 $10.00 替换 titles 表的 price 列中的所有 NULL 条目。

USE pubs
GO
SELECT AVG(ISNULL(price, $10.00))
FROM titles
GO
下面是结果集：
--------------------------
14.24

(1 row(s) affected)

B. 使用 ISNULL
下面的示例为 titles 表中的所有书选择书名、类型及价格。如果一个书名的价格是 NULL，那么在结果集中显示的价格为 0.00。
USE pubs
GO
SELECT SUBSTRING(title, 1, 15) AS Title, type AS Type,
ISNULL(price, 0.00) AS Price
FROM titles
GO
下面是结果集：

Title Type Price
--------------- ------------ --------------------------
The Busy Execut business 19.99
Cooking with Co business 11.95

The Psychology UNDECIDED

c.在存储过程中获取新闻与新闻的前一条和后一条的标题
create procedure GetNews
(
     @newsId int
)
as
declare
     @priorId int,
     @nextId int,
     @priorTitle nvarchar(50),
     @nextTitle nvarchar(50)
select @priorId=isnull(max(newsId),0) from NewsDetail where NewsId< @newsId
select @priorTitle=NewsTitle from NewsDetail where NewsId= @priorId
select @nextId=isnull(min(newsId),0) from NewsDetail where NewsId> @newsId
select @nextTitle=NewsTitle from NewsDetail where NewsId= @nextId
select
     NewsTitle,NewsTime,NewsContent,Hit,@priorTitle as priorTitle,@nextTitle as nextTitle
from
     NewsDetail
where
     NewsId=@newsId

五.inner join 的使用
INNER JOIN 运算
组合两个表中的记录，只要在公共字段之中有相符的值。
语法
FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2
table1, table2 记录被组合的表的名称。
field1, field2 被联接的字段的名称。若它们不是由数字构成的，则这些字段必须为相同的数据类型并包含同类数据，但它们无须具有相同的名称。
compopr 任何的关系比较运算子："=," "<," ">," "<=," ">=," 或 "<>."

说明
可以在 FROM 子句中使用INNER JOIN运算。.这是最普通的联接类型。只要在这两个表的公共字段之中有相符值，内部联接将组合两个表中的记录。

可以使用 INNER JOIN 与部门表及员工表选择每一个部门中的全部员工。反之，可以使用 LEFT JOIN或 RIGHT JOIN运算创建 outer join，从而选择所有部门（即使有些并没有员工）或所有员工（即使有些尚未分配到部门）。

若试图联接包含 Memo或 OLE Object数据的字段，会导致错误。

可以联接任何两个相同类型的数值字段。例如，可以联接 AutoNumber和 Long字段，因为它们类型相似。但不能联接 Single 和 Double 类型的字段。

下列示例显示如何在类标识符字段联接类表及产品表：

SELECT CategoryName, ProductName

FROM Categories INNER JOIN Products

ON Categories.CategoryID = Products.CategoryID;

在上面的示例中，类标识符是已被联接的字段，但是它并不包含在查询输出中，因它并非被包含在 SELECT 语句之中。在这个示例中，若要包含联接字段，将字段名包含在 SELECT 语句中， Categories.CategoryID.

也可以使用下列语法，在一个 JOIN 语句中链接多个 ON 子句：

SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2) OR
ON table1.field3 compopr table2.field3)];

也可以使用下列语法，嵌套 JOIN 语句：

SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)]
ON table3.field3 compopr tablex.fieldx)]
ON table2.field2 compopr table3.field3)
ON table1.field1 compopr table2.field2;

在一个 INNER JOIN 之中，可以嵌套 LEFT JOIN 或 RIGHT JOIN，但是在 LEFT JOIN 或 RIGHT JOIN 中不能嵌套 INNER JOIN。

六.在存储过程中的模糊查询

string sql="select * from Article where pass=1 and deleted=0 and Title like '%"+@keyword+"%' order by UpdateTime desc"
把'%"+@keyword+"%'看成一起
直接写在存储过程中 select * from ProductInfowhere ProductName like '%'+@keyWords+'%'

七.使用SQL存储过程返回多结果集怎么处理
如果有多个结果集返回,那么客户端一定要用一个DataSet来接收才行,这样可以通过DataSet的.Tables来分别访问它返回的数据集了.
也可以用output
DataTable dt = ds.tables[2]
dt.rows[0]["newsId"].tostring

八.如何一次插入多个记录
insert into
     OrderDetail
select
     @orderId,ShoppingCart.ProductId,ShoppingCart.Quantity,Product.ProductPrice
from
     ShoppingCart inner join
     Product on ShoppingCart.ProductId=Product.ProductId
where
     cartId=@cartId

九.求两个字段的值的积，然后再求和
cast(sum(OrderDetail.Quantity*OrderDetail.UnitCost) as money) as OrderTotal

十.定义为char类型的缺点：有空字符串
解决方法：
经过高手指点，才知道原来红色的代码有问题，interbase数据库如果某个字段是char类型的数据，才存放数据后会自动在字符串后面补上空格，以满足位数需要，因此表面上看，取到的密码和我的输入密码一样，实际上是不一样的。
解决办法：
办法1 if ibt_user_pass.FieldByName('passwd').AsString=edit2.Text then 这句话变成if trim（ibt_user_pass.FieldByName('passwd').AsString）=edit2.Text then
trim函数作用是去掉字符串中多加上去的空格
办法2 数据库的char类型改成varchar类型

十一。在程序中使用存储过程
SqlCommand cmd =new SqlCommand("proGetEmpPassword", cn);
   cmd.CommandType = CommandType.StoredProcedure;

   SqlParameter paramLoginName=new SqlParameter("@LoginName", SqlDbType.Char, 20);
   paramLoginName.Value = strLoginName;
   cmd.Parameters.Add(paramLoginName);

   SqlParameter paramPassword=new SqlParameter("@Pword", SqlDbType.VarChar, 50);
   paramPassword.Direction = ParameterDirection.Output;
   cmd.Parameters.Add(paramPassword);

   SqlParameter paramReturn=new SqlParameter("@ReturnValue", SqlDbType.Int);
   paramReturn.Direction = ParameterDirection.ReturnValue;
   cmd.Parameters.Add(paramReturn);//在存储过程中为return @@rowcount,用                                                        来判断,区别select @orderId=@@identity

string arPword ;

   try
   {
    cn.Open();

    cmd.ExecuteNonQuery();

    if (Convert.ToInt32(paramReturn.Value) == 1)

     arPword = (paramPassword.Value).ToString();
    else

     return null;

   }
   catch
   {
    return null;
   }
   finally
   {
    if (cn.State == ConnectionState.Open)
     cn.Close();
   }

   return arPword;

}

×××××××××××××××××××××××××××××××××××××××××××××8××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
///是 SQL-92 设置语句，使 SQL Server 2000/2005 遵从 SQL-92 规则。
///当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。
///当 SET QUOTED_IDENTIFIER 为 OFF 时，标识符不可加引号，且必须符合所有 Transact-SQL 标识符规则。
/// SQL-92 标准要求在对空值进行等于 (=) 或不等于 (<>) 比较时取值为 FALSE。
///当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。
///即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。
/// 当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。
///使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。
///使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。
///此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。

/*
执行语句范例：
SELECT * FROM ARTICLE w1
    WHERE ID in
        (
            SELECT top 30 ID FROM
            (
                SELECT top 1030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
            ) w ORDER BY w.YEAR ASC, w.ID ASC
        )
    ORDER BY w1.YEAR DESC, w1.ID DESC

执行操作：
declare @a int,@b int
EXECUTE PageResult @cAction='do',@PageIndex='1',@PageSize='10',@sys_Table='tb_newall_product',@sys_Key='idno',@sys_Fields='*',@sys_Where='',@sys_OrderFields='dtordertime',@sys_OrderFields2='dtaddtime',@sys_Order='1',@OutTotalPage=@a output,@OutTotalRecord=@b output

EXECUTE PageResult @cAction='do',@PageIndex='1',@PageSize='10',@sys_Table=' tb_newall_product',@sys_JoinSql='left join d_company on iUserID=d_company.id',@sys_Key='idno',@sys_Fields='dtaddtime,*',@sys_Where=' cUsertype=''c'' and d_company.id=''15839'' ',@sys_OrderFields='dtaddtime',@sys_Order='1'

declare @a int,@b int
EXECUTE PageResult @cAction='do',@PageIndex=-1,@PageSize=1,@sys_Table=' Tb_Log_ck',@sys_JoinSql=' left join tb_xj_info on tb_xj_info.id=Tb_Log_ck.iRecordID',@sys_Key='id',@sys_Fields='tb_xj_info.*',@sys_Where='',@sys_OrderFields='',@sys_Order='2',@OutTotalPage=@a output,@OutTotalRecord=@b output
select @a,@b
*/

ALTER PROCEDURE [dbo].[PageResult]

@cAction varchar(30),   --1：主键排序 2：主键与排序字段不相同
@PageIndex int,        --当前页数 -PageIndex：-1 显示全部记录
@PageSize int,        --页大小
@sys_Table nvarchar(1000),    --查询表名
@sys_Key varchar(50),        --主键
@sys_Fields nvarchar(3000),    --查询字段
@sys_Where nvarchar(3000)='',    --查询条件
@sys_OrderFields nvarchar(500)='',    --排序字段
@sys_OrderFields2 nvarchar(500)='',    --排序字段
@sys_OrderFields3 nvarchar(500)='',    --排序字段
@sys_Order nvarchar(100)='1',    --排序字段
@sys_JoinSql nvarchar(500)='', --关联表语句格式：left join d_company on iUserID=d_company.id
@sys_JoinSql2 nvarchar(500)='',
@sys_JoinSql3 nvarchar(500)='',
@OutTotalPage int=0 Output,
@OutTotalRecord int=0 Output

AS
SET NOCOUNT ON

DECLARE @Sql NVARCHAR(4000)
DECLARE @Sql_order1 NVARCHAR(100)
DECLARE @Sql_order2 NVARCHAR(100)
DECLARE @Sql_order3 NVARCHAR(100)
DECLARE @Sql_Count NVARCHAR(4000)

DECLARE @Sql_1 NVARCHAR(4000),@Sql_2 NVARCHAR(4000)

if @cAction='do'
Begin
set @Sql_order1=''
set @Sql_order2=''
set @Sql_order3=''

if @sys_Where<>'' set @sys_Where=' where '+@sys_Where

if @sys_Order='1'
Begin
  if @sys_OrderFields<>''
  Begin
   set @Sql_order1=@Sql_order1+@sys_OrderFields+' DESC,'
   set @Sql_order2=@Sql_order2+'w.'+@sys_OrderFields+' ASC,'
   set @Sql_order3=@Sql_order3+@sys_Table+'.'+@sys_OrderFields+' DESC,'
  End

  if @sys_OrderFields2<>''
  Begin
   set @Sql_order1=@Sql_order1+@sys_OrderFields2+' DESC,'
   set @Sql_order2=@Sql_order2+'w.'+@sys_OrderFields2+' ASC,'
   set @Sql_order3=@Sql_order3+@sys_Table+'.'+@sys_OrderFields2+' DESC,'
  End

  if @sys_OrderFields3<>''
  Begin
   set @Sql_order1=@Sql_order1+@sys_OrderFields3+' DESC,'
   set @Sql_order2=@Sql_order2+'w.'+@sys_OrderFields3+' ASC,'
   set @Sql_order3=@Sql_order3+@sys_Table+'.'+@sys_OrderFields3+' DESC,'
  End

  set @Sql_order1=' ORDER BY '+@Sql_order1+@sys_Table+'.'+@sys_Key+' DESC'
  set @Sql_order2=' ORDER BY '+@Sql_order2+'w.'+@sys_Key+' ASC'
  set @Sql_order3=' ORDER BY '+@Sql_order3+@sys_Table+'.'+@sys_Key+' DESC'

End

if @sys_Order='2'
Begin

  if @sys_OrderFields<>''
  Begin
   set @Sql_order1=@Sql_order1+@sys_OrderFields+' ASC,'
   set @Sql_order2=@Sql_order2+'w.'+@sys_OrderFields+' DESC,'
   set @Sql_order3=@Sql_order3+@sys_Table+'.'+@sys_OrderFields+' ASC,'
  End

  if @sys_OrderFields2<>''
  Begin
   set @Sql_order1=@Sql_order1+@sys_OrderFields2+' ASC,'
   set @Sql_order2=@Sql_order2+'w.'+@sys_OrderFields2+' DESC,'
   set @Sql_order3=@Sql_order3+@sys_Table+'.'+@sys_OrderFields2+' ASC,'
  End

  if @sys_OrderFields3<>''
  Begin
   set @Sql_order1=@Sql_order1+@sys_OrderFields3+' ASC,'
   set @Sql_order2=@Sql_order2+'w.'+@sys_OrderFields3+' DESC,'
   set @Sql_order3=@Sql_order3+@sys_Table+'.'+@sys_OrderFields3+' ASC,'
  End

  set @Sql_order1=' ORDER BY '+@Sql_order1+@sys_Table+'.'+@sys_Key+' ASC'
  set @Sql_order2=' ORDER BY '+@Sql_order2+'w.'+@sys_Key+' DESC'
  set @Sql_order3=' ORDER BY '+@Sql_order3+@sys_Table+'.'+@sys_Key+' ASC'

End

--输出总记录数、总页数
SET @Sql_Count = 'SELECT @OutTotalRecord=COUNT(1),@OutTotalPage=CEILING((COUNT(1)+0.0)/'
+ CAST(@PageSize AS NVARCHAR)+') FROM ' + @sys_Table +' '+ @sys_JoinSql+''+@sys_JoinSql2+' '+@sys_JoinSql3+@sys_Where

EXEC SP_EXECUTESQL @Sql_Count,N'@OutTotalRecord INT OUTPUT,@OutTotalPage INT OUTPUT', @OutTotalRecord OUTPUT,@OutTotalPage OUTPUT

--输出记录集
if @PageIndex=-1
set @Sql='SELECT '+@sys_Fields+' FROM'+@sys_Table+' '+@sys_JoinSql+' '+@sys_JoinSql2+''+@sys_JoinSql3+@sys_Where+@Sql_order1
else
Begin

  if @sys_OrderFields<>'' set @sys_OrderFields=','+@sys_OrderFields
  if @sys_OrderFields2<>'' set @sys_OrderFields2=','+@sys_OrderFields2
  if @sys_OrderFields3<>'' set @sys_OrderFields3=','+@sys_OrderFields3

  set @Sql_1='SELECT top '+ CAST(@PageSize * @PageIndex AS NVARCHAR)+' '+@sys_Table+'.'+@sys_Key+@sys_OrderFields+@sys_OrderFields2+@sys_OrderFields3+' FROM'+@sys_Table+' '+@sys_JoinSql+' '+@sys_JoinSql2+''+@sys_JoinSql3+@sys_Where+@Sql_order1
  if @PageIndex=@OutTotalPage and (@OutTotalRecord % @PageSize!=0)
   set @Sql_2='SELECT top '+ CAST(@OutTotalRecord % @PageSize AS NVARCHAR)+' '+@sys_Key+' FROM ('+@Sql_1+') w'+@Sql_order2
  else
   set @Sql_2='SELECT top '+ CAST(@PageSize AS NVARCHAR)+' '+@sys_Key+' FROM ('+@Sql_1+') w'+@Sql_order2

  set @Sql='SELECT top '+ CAST(@PageSize AS NVARCHAR)+' '+@sys_Fields+' FROM '+@sys_Table+''+@sys_JoinSql+' '+@sys_JoinSql2+' '+@sys_JoinSql3+' WHERE'+@sys_Table+'.'+@sys_Key+' in ('+@Sql_2+' )'+@Sql_order3

End
--print @Sql
Exec(@Sql)
End

SET NOCOUNT OFF

×××××××××××××××××××××××××××××××××××××××××××××9××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

首先，在SQL Server中创建存储过程：
--创建添加数据的存储过程
if exists (select * from sysobjects where name='proc_AddUser')
drop proc proc_AddUser
go

create procedure proc_AddUser
@uId int output;
@uName nvarchar(20);
@uAge int;
@uPass nvarchar(200)
with encryption --对存储过程进行加密
as
insert into UserInfo (uName,uAge,password) values (@uName,@uAge,@uPass)
select @uId=@@identity
go

其次，在C#项目中搭建三层架构，并且在数据访问层创建DBService类（数据库操作通用类，
可以连接数据库，对数据库数据进行增删改查的操作）
DBService.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration; //配置文件

namespace Ch3DemoDAL
{
public class DBService
{
       #region 私有构造
       ///<summary>
       ///私有构造函数
       ///</summary>
       private DBService()
       {

}
#endregion

       #region 私有成员
       ///<summary>
       ///静态私有的数据库连接字符串
       ///</summary>

private static readonly string strConn = ConfigurationManager.ConnectionStrings
["Ch3Demo.Properties.Settings.Setting"].ToString(); //这里需要配置文件App.config才可用

       ///<summary>
       ///静态私有的数据库连接对象，封装字段
       ///</summary>
       private static SqlConnection conn = null;

       public static SqlConnection Conn
       {
            get
            {
     if ( conn == null)
     {
        conn = new SqlConnection(strConn);
        conn.Open();
     }
   else if(conn.State == ConnectionState.Closed)
   {
         conn.Open();
   }
   else if(conn.State == ConnectionState.Broken)
   {
        conn.Close();
        conn.Open();
   }
   return DBService.conn;
            }
       }
       #endregion

       #region 公共方法，静态公共的数据库访问方法（必须是静态的）
       ///<summary>
       ///执行数据库的增删改查操作
       ///</summary>
       ///<param name="sql"></param>
       ///<returns>受影响行数</returns>
       public static int ExecuteCommand(string sql)
       {
           using(SqlCommand.command = new SqlCommand(sql,Conn))
           {
                return command.ExecuteNonQuery();
           }
       }

       ///<summary>
       ///执行带参数的SQL语句
       ///</summary>
       ///<param name="sql"></param>
       ///<param name="values"></param>
       ///<returns></returns>
       public static int ExecuteCommand(string sql,params SqlParameter[] values)
       {
             using(SqlCommand command = new SqlCommand(sql,Conn))
             {
                  command.Parameters.AddRange(values);
                  return command.EndExecuteNonQuery();
             }
       }

       ///<summary>
       ///执行数据库的增删改查操作
       ///</summary>
       ///<param name="sql"></param>
       ///<returns>首行首列</returns>
       public static int ExecuteScalar(string sql)
       {
             using(SqlCommand.command = new SqlCommand(sql,Conn))
             {
                  return Convert.ToInt32(command.ExecuteScalar());
             }
       }

       ///<summary>
       ///执行带参数的SQL语句
       ///</summary>
       ///<param name="sql"></param>
       ///<param name="values"></param>
       ///<returns></returns>
       public static int ExecuteScalar(string sql,params SqlParameter[] values)
       {
             using(SqlCommand command = new SqlCommand(sql,Conn))
             {
                  command.Parameters.AddRange(values);
                  return Convert.ToInt32(command.ExecuteScalar());
             }
       }

       ///<summary>
       ///获得DataReader
       ///</summary>
       ///<param name="sql"></param>
       ///<returns></returns>
       public static SqlDataReader GetRerader(string sql)
       {
             SqlCommand command = new SqlCommand(sql,Conn);
             SqlDataReader dataReader = command .ExecuteReader(CommandBehavior.CloseConnection);
             return dataReader;
       }

       ///<summary>
       ///执行带参数的SQL语句
       ///</summary>
       ///<param name="sql"></param>
       ///<param name="values"></param>
       ///<returns></returns>
       public static SqlDataReader GetRerader(string sql,params SqlParameter[] values)
       {
             SqlCommand command = new SqlCommand(sql,Conn);
             command.Parameters.AddRange(values);
             SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
             return dataReader;
       }

       ///<summary>
       ///获得结果集
       ///</summary>
       ///<param name="sql"></param>
       ///<returns></returns>
       public static DataSet GetResult(string sql)
       {
             DataSet dataSet = new DataSet();
             using(SqlDataAdapter da = new SqlDataAdapter(sql,Conn))
             {
                      da.Fill(dataSet);
             }
             return dataSet;
       }
       #endregion
}
}

之后，再在数据访问层创建一个AddUserService类，用于添加用户；该类就运用存储过程对程序进行增加用户的
AddUserService.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Ch3DemoDAL
{
public class AddUserService
{
     public static int AddUser(entity UserInfo user)
    {
         int id = 0;
        //使用存储过程实现添加数据
       //proc_AddUser 为存储过程名称
       using(SqlCommand command = new SqlCommand(""proc_AddUser),DBService.Conn)
       {
           //指定command对象的执行方式
           command.CommandType = CommandType.StoredProcedure;
           //指定存储过程的参数并赋值
           command.Parameter.Add("@uName",SqlDbType.NVarChar,20).Value = user.Name;
           command.Parameter.Add("@uAge",SqlDbType.Int).Value = userAge;
           command.Parameter.Add("@uPass",SqlDbType.NVarChar,200).Value = user.Password;
           //设置输出参数
           command.Parameters.Add("@uId",SqlDbType.Int).Direction
= Parameter.Direction.Output; //@uId,@uName等参数必须与数据库存储过程中的参数一致
           //执行
           command.ExecuteNonQuery();
           //获取输出参数的值
           id=Convert.ToInt32(command.Parameters["@uId".Value]);
       }
       return id;
    }

}
}

--------------------------------------------------------------------------------------------------------------------
一个写出更好的分页代码的办法是使用存储过程。以下显示了一个示例存储过程，它为Northwind
数据库中的Orders数据表分页。总的来说，在这里所有需要做的就是传入页的索引和页的容量。
数据库会计算出适当的结果集然后返回它们。

CREATE PROCEDURE northwind_OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

--First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowToReturn

--Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

--Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY(1,1) NOT NULL,
OrderID int
)

--Insert into the temp table
INSERT INTO #PageIndex(OrderID)
SELECT
OrderID
FROM
Orders
ORDER BY
OrderID DESC

--Return total count
SELECT COUNT(OrderID) FROM Orders

--Return paged results
SELECT
    O.*
FROM
   Orders O,
   #PageIndex PageIndex
WHERE
   O.OrderID = PageIndex.OrderID AND
   PageIndex.IndexID>@PageLowerBound AND
   PageIndex.IndexID<@PageUpperBound
ORDER BY
   PageIndex.IndexID

END

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

×××××××××××××××××××××××××××××××××××××××××××××10××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

模版：Create procedure procedue_name [@parameter data_type][output]
[with]{recompile|encryption} as sql_statement

解释：output：表示此参数是可传回的

with {recompile|encryption} recompile:表示每次执行此存储过程时都重新编译一次;encryption:所创建的存储过程的内容会被加密。

×××××××××××××××××××××××××××××××××××××××××××××11××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

14.3.2 将所有的char、varchar改为nchar、nvarchar.sql

CREATE PROC p_ToUnicode
@type tinyint=0 --修改方式，0=仅查询可修改情况,1=仅所有列可修改时才修改,2=修改可修改列，报告不可修改列
AS
SET NOCOUNT ON
--查询非unicode列转换为unicode列的可行性
SELECT TableName=o.name,FieldName=c.name,
     FieldType=t.name+N'('+CAST(c.prec as varchar)+N')'
        +CASE WHEN c.isnullable=1 THEN N'' ELSE N' NOT' END
        +N' NULL',
     NoChangeCause=CAST(STUFF(
         CASE WHEN COLUMNPROPERTY(c.id,c.name,N'IsComputed')=1
             THEN N',计算列' ELSE N'' END
        +CASE WHEN c.cdefault=0 THEN N'' ELSE N',列具有默认值' END
        +CASE WHEN EXISTS(
                 SELECT * FROM sysindexkeys idxk,sysindexes idx
                 WHERE idxk.id=c.id
                      AND idxk.colid=c.colid
                     AND idxk.id=idx.id
                    AND idxk.indid=idx.indid
                    AND idx.indid NOT IN(0,255)
                     AND INDEXPROPERTY(idx.id,idx.name,N'IsAutoStatistics')=0)
             THEN N',列被主键、唯一键、索引、STATISTICS引用' ELSE N'' END
        +CASE WHEN EXISTS(
                 SELECT * FROM sysforeignkeys WHERE fkeyid=c.id AND fkey=c.colid)
             THEN N',列被外键约束引用' ELSE N'' END
        +CASE WHEN EXISTS(
                 SELECT * FROM sysobjects oc,sysdepends d
                 WHERE oc.parent_obj=o.id
                     AND OBJECTPROPERTY(oc.id,N'IsCheckCnst')=1
                     AND d.id=oc.id
                     AND d.depnumber=c.colid)
             THEN N',列被CHECK约束引用' ELSE N'' END,1,1,N'') as nvarchar(4000))
INTO # FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id and o.status>=0
     AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
     AND t.xusertype=c.xusertype
     AND t.name in('char','varchar')
IF @@ROWCOUNT=0 RETURN

--如果需要，修改非unicode列为unicode列
IF @type=2 OR NOT EXISTS(SELECT * FROM # WHERE NoChangeCause>'')
BEGIN
    SET XACT_ABORT ON
    BEGIN TRAN
         DECLARE tb CURSOR LOCAL
        FOR
        SELECT N'ALTER TABLE '+QUOTENAME(TableName)
             +N' ALTER COLUMN '+QUOTENAME(FieldName)
             +N' N'+FieldType
        FROM #
         WHERE NoChangeCause IS NULL
        DECLARE @sql nvarchar(4000)
         OPEN tb
         FETCH tb INTO @sql
         WHILE @@FETCH_STATUS=0
         BEGIN
            EXEC sp_executesql @sql
             FETCH tb INTO @sql
         END
        CLOSE tb
         DEALLOCATE tb
     COMMIT TRAN
END

--显示不能修改的列
SELECT TableName,FieldName,FieldType,
NoChangeCause=ISNULL(NoChangeCause,N'可以修改(或者已经修改成功)')
FROM #
ORDER BY CASE WHEN NoChangeCause IS NULL THEN 1 ELSE 0 END,TableName

×××××××××××××××××××××××××××××××××××××××××××××12××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

0 0