Microsoft SQL Server/存储过程

介绍

一个存储过程 (sp) 是一组 SQL 请求，保存在数据库中。在 SSMS 中，它们可以与表格一起找到。

实际上，在软件架构方面，最好将 T-SQL 语言存储在数据库中，因为如果一个层发生变化，则不需要修改另一个层。

通常存储过程操作其数据库表，但它们也可以与其他数据库表进行交互，即使这些表位于另一个服务器（称为链接服务器）上。要创建链接服务器

• 在 SSMS 中，单击“服务器对象”菜单中的“链接服务器”，然后填写用于连接的帐户。
• 在 SQL 中，使用 sp_addlinkedserver。

跨两个服务器联接的示例

select *
from table1 t1
inner join [server2].[base2].[dbo].[table2] t2 on t2.id = t1.t2_id

语法

Microsoft T-SQL 语言提供了一些从 SQL 标准改进。

• 默认情况下，引号的作用不同于撇号，撇号用于创建字符字符串。要在同一方式下使用它们（例如嵌套它们），应该启动 SET QUOTED_IDENTIFIER ON。
• 在 SSMS 中，SQL 请求可以通过三种方式执行

1. 直接在空白窗口中，点击“新建查询”即可看到。然后可以将其保存为 .sql 文件，以便能够在同一个窗口中重新打开它。
2. 通过将其存储在字符串变量中，然后使用 sp_executesql 执行它。这样做的好处是可以包含变量（例如：数据库名称），但缺点是抑制了语法着色、自动完成（IntelliSense）和 SSMS 调试。例如

    DECLARE @Request1 NVARCHAR(MAX)
    DECLARE @MyTable1 NVARCHAR(MAX)
    SET @MyTable1 = 
    SET @Requst1 =  'SELECT * FROM ' + @MyTable1
    EXECUTE sp_executesql @Request1
   

3. 通过执行存储在数据库中的过程，该过程包含请求。例如

    EXEC [MaBase1].[dbo].[MyProcedure1]
   

此调用可以后跟参数，类似于命令式编程中的过程。

实际上，存储过程中有两种类型的变量

1. 私有变量，以 Declare 开头。
2. 参数

@StartDate varchar(8)       -- Mandatory argument
@EndDate varchar(8) = null  -- Optional argument
if @EndDate is null set @EndDate = convert(varchar,@StartDate + 1,112)
Declare @Name varchar(50)   -- Private variable

要创建新的存储过程

CREATE PROCEDURE [dbo].[MyProcedure1]

要保存现有存储过程

ALTER PROCEDURE [dbo].[MyProcedure1]

理想情况下，此指令应出现在 sp 的开头，后跟 AS + 它的名称，这样代码执行就会保存它（而不是启动它）。为了获得其结果，SSMS 提供了右键单击选项：“执行存储过程...”这将生成另一个 SQL 请求，该请求在结果上方打开一个新标签页，并使用其参数调用存储过程。

注意：SSMS 不允许备份包含编译错误的存储过程。因此，如果备份紧急，只需注释掉错误代码或创建临时 .sql 文件。

注意：错误消息会传达一个行号，该行号与 SSMS 行不匹配。它实际上是从最后一个 GO 偏移的。

然后，这些 sp 可以被任何提供 SQL Server 驱动程序的编程语言中的程序调用，例如 PHP 或 VB，并将从 recordset 变量中呈现结果。

PRINT

此命令在 消息 选项卡中显示内容，与填充 结果 选项卡的 SELECT 相反。

示例

print 'Hello World ! ' -- Displays "Hello World !"

declare @n int
set @n = 5

print 'the value is: ' + cast(@n as varchar)

条件

IF

if @x=1 begin
  print 'x = 1'
end else if @x=2 begin
  print 'x = 2'
end else begin
  print 'x <> 1 et 2'
end

备注：begin 和 end 是可选的。

CASE

set @Season = case 
 when @DayDate = '20110918' then 'summer'
 when @DayDate = '20110922' then 'autumn'
 else 'another season'
end

要仅在存在值时添加 WHERE 条件，技巧是在其他情况下设置始终为真的内容（例如：Field1 = Field1）

declare @Column int = null
select Field1
from Table1
where Field1 = case when isnull(@Column,'')<>'' then @Column else Field1 end

上面的示例可以使用 where Field1 = isnull(@Column, Field1) 更简单。

循环

WHILE

“while” 循环使用条件来停止，例如计数器

DECLARE @i int
WHILE @i <= 10
BEGIN
   UPDATE Table1
   SET Field2 = "petit" WHERE Field1 = @i
   SET @i = @i + 1
END

CURSOR

游标允许逐行处理记录集，每行都存储在 INTO 后面提到的变量中，并在 NEXT 之后重新初始化。但是，这种方法相对较慢，应该避免使用，只要有可能。

例如，如果一个记录处理依赖于前一个记录，或者要打印一些字符

USE Base1
declare @Name varchar(20)
DECLARE cursor1 CURSOR FOR SELECT FirstName FROM Table1
OPEN cursor1

/* First record from the selection */
FETCH NEXT FROM cursor1 into @Name
print 'Hello ' + @Name

/* Treatment of the other records in a loop */
while @@FETCH_STATUS = 0
  begin
    FETCH NEXT FROM cursor1 into @Name
    print 'Hello ' + @Name
  end

CLOSE cursor1;
DEALLOCATE cursor1;

从另一个存储过程执行存储过程

SSMS 还提供了一个逐步执行模式（类似于 Visual Basic），通过在每一步按 F11，可以跟踪左下角的变量值。

断点也可用，用于从一行跳转到另一行。

备注：在元编程中，任何 sp 修改都不会被过程在执行期间考虑在内。

要从另一个 sp 执行 sp

ALTER PROCEDURE [dbo].[MyProcedure1]
DECLARE	@result int
EXEC	@result = [dbo].[MyProcedure2] @Parameter1;
if @result = 0 begin
 ...
end

异常

从 SQL Server 2005 开始，异常处理看起来像这样

-- Transaction start
BEGIN TRAN
  BEGIN TRY
   -- Execution
   INSERT INTO Table1(Name1) VALUES ('ABC')
   INSERT INTO Table1(Name1) VALUES ('123')
   -- Transaction submission
   COMMIT TRAN
  END TRY
BEGIN CATCH
 -- Transaction cancellation if error
 ROLLBACK TRAN
END CATCH

研究

要获取包含特定字符串的 sp

SELECT name
FROM sysobjects syso
INNER JOIN syscomments sysc
ON syso.id = sysc.id
WHERE
(syso.xtype = 'P' or
syso.xtype = 'V')
AND
(syso.category = 0)
and text like '%String to search%'
group by name