关于临时表和存储过程返回结果

临时表是在TempDB中创建的表。临时表的名称都以“#”开头。临时表的范围为创建临时表的连接。因为，临时表不能在两个连接之间共享，一旦连接关闭，临时表就会被丢弃。如果临时表被创建于存储过程之中，则临时表的范围在存储过程之中，或者被该存储过程调用的任何存储过程之中。如果需要在连接之间共享临时表，则需要使用全局的临时表。全局的临时表以“##”符号开头，它将一直存在于数据库中，直到SQL    Server重新启动。一旦这类临时表创建之后，所有的用户都可以访问到。在临时表上不能明确地指明权限。    临时表提供了存储中间结果的能力。有时候，临时表还能通过将一个复杂的查询分解成两个查询而获得性能的改善。这可以通过首先将第一个查询的结果存在临时表中，然后在第二个查询中使用临时表来实现。当一个大表中的某个子集在一个在座过程中使用多次时，建议使用临时表。在这种情况下，在临时表中保持数据的子集，以在随后的连接中使用，这样能大大改善性能。还可以在临时表中创建索引。     
    
    
   从存储过程中返回结果     
    
   从存储过程中返回结果有三种方式：     
    
   1、    返回结果集     
    
   这是客户端应用程序返回结果的最通用的方法。结果集是通过使用SELECT语句选择数据产生的。结果集可以从永久表、临时表或局部变量中产生。将结果返回到另一个存储过程不是一种有效的方法。存储过程不能访问另一个存储过程建立的结果集。     
    
   例如从永久表中返回结果集：     
    
   USE    pubs     
    
   GO     
    
   CREATE    PROCEDURE    ap_CreateResultFromPermtable     
    
   AS     
    
   SELECT    au_iname    FROM    authors     
    
   GO     
    
   例如从局部变量中创建结果集：     
    
   USE    pubs     
    
   GO     
    
   CREATE    PROCEDURE    ap_CreateResultFromVariable     
    
   AS     
    
   DECLARE    @au_iname    char(20)     
    
   SELECT    @au_iname    =    au_iname    FROM    authors     
    
   WHERE    au_id    =    ‘172-32-1176’     
    
   SELECT    @au_id     
    
   GO     
    
   2、    设置OUTPUT参数的值     
    
   输出参数经常用来从存储过程中检索出结果。如果某个参数在传输到存储过程中时被定义成OUTPUT，则对该参数的任何修改在退出存储之后仍然有效。     
    
   例如：     
    
   USE    pubs     
    
   GO     
    
   CREATE    PROCEDURE    ap_SetOutputVar    @count    integer    OUTPUT     
    
   AS     
    
   SELECT    @count    =    count(*)    FROM    authors     
    
   GO     
    
   从输出参数中检索出值：     
    
   USE    pubs     
    
   GO     
    
   CREATE    PROCEDURE    ap_GetOutputVar     
    
   AS     
    
   DECLARE    @num    integer     
    
   EXECUTE    ap_SetOutputVar    @num    OUTPUT     
    
   PRINT    “the    count    is”+convert(char,@num)     
    
   GO     
    
   ·    将游标使用成OUTPUT参数。游标可以使用OUTPUT（输出）参数，但不能使用成输入参数。也就是说，游标可以作为结果返回，但却不能传输到过程中去。当游标被用作参数时，需要限定其为OUTPUT和VARYING。VARYING关键字指出该结果集要用来支持输出参数。这样就提供了将结果集返回到调用过程的能力。     
    
   例如：     
    
   USE    pubs     
    
   GO     
    
   CREATE    PROCEDURE    GetTitleCount    @count_cursor    CURSOR    VARYING    OUTPUT     
    
   AS     
    
   SET    @count_cursor    =    CURSOR     
    
   FOR     
    
   SELECT    au_id,count(*)     
    
   FROM    titleauthors     
    
   GROUP    BY    au_id     
    
   OPEN    @count_cursor     
    
   GO     
    
   3、    通过RETURN参数返回状态     
    
   这是一种从存储过程返回错误码的方法。存储过程总是返回一个状态值，用户也可以使用RETURN语句返回自己的状态。     
    
   例如：     
    
   USE    pubs     
    
   GO     
    
   CREATE    PROCEDURE    ap_SetReturnStatus     
    
   AS     
    
   DECLARE    @count    integer     
    
   SELECT    @count    =    count(*)    FROM    authors     
    
   IF    @count    =    0     
    
   RETURN(1)     
    
   ELSE     
    
   RETURN    (0)     
    
   GO     
    
   例如检索出返回的状态：     
    
   USE    pubs     
    
   GO     
    
   CREATE    PROCEDURE    ap_GetReturnStatus     
    
   AS     
    
   DECLARE    @status    integer     
    
   EXECUTE    @status    =    ap_SetReturnStatus     
    
   IF    @status    =    1     
    
   PRINT    “No    rows    found”     
    
   ELSE     
    
   PRINT    “successful”     
    
   GO     
    
   在存储过程中进行错误处理     
    
   如同其它程序一样，在存储过程中进行错误处理是非常重要的。系统变更@@error在执行每一个Transact    SQL语句之后都会得到一个值。对于成功的执行，@@error的值为0，如果出现错误，则@@error中将包含错误信息。@@error系统变量对存储过程的错误处理是非常重要的。     
    
   注意：为了防止错误，@@error所能设置的值在sysmessages表的“error”中反映了出来。     
    
   在存储过程中的错误有两种类型：     
    
   1、    数据库相关的错误     
    
   这些错误是由数据库的不一致性引起的，系统使用非0的@@error值表示特定的数据库问题。在Transact    SQL执行之后，可以通过@@error获得所出现的错误。如果发现@@error不为0，则必须采取必要的行动，大多数情况下，存储将不再继续进行处理而返回。下面的示例展示了典型的获取数据库错误的方法。该过程将错误代码放置到输出变量中，这样，调用程序就能够访问到。     
    
   USE    pubs     
    
   GO     
    
   CREATE    PROCEDURE    ap_TrapDatabaseError    @return_code    integer    OUTPUT     
    
   AS     
    
   UPDATE    authors    SET    au_iname    =    “Jackson”     
    
   WHERE    au_iname    =    “Smith”     
    
   IF    @@error    <>    0     
    
   BEGIN     
    
   SELECT    @return_code    =    @@error     
    
   RETURN     
    
   END     
    
   ELSE     
    
   @return_code    =    0     
    
   GO     
    
   2、    业务逻辑错误     
    
   这些错误是由于违反了业务规则而引起的。要获取这些错误，首先需要定义业务规则，基于这些规则，需要在存储过程中增加必要的错误检测代码。人们经常使用RAISERROR语句通报这些错误。RAISERROR提供了返回用户定义错误及将@@error变量设置成用户定义错误号的能力。错误消息可以被动态地建立，或者基于错误号从“sysmessages”表中检索到。一旦出现了错误，错误就会以一种服务器错误消息的方式返回到客户机。下面是RAISERROR命令的语法：     
    
   RAISERROR    (msg_id    |    msg_str,    severity,    state     
    
   [,    argument    ][,…n]])     
    
   [WITH    options]     
    
   Msg_id指明用户定义消息的id，该消息存储在“sysmessages”系统表中。     
    
   Msg_str用于动态创建消息的消息字符串。这与C语言中的“printf”非常相似。     
    
   Severity定义用户赋值的错误消息严重程度。     
    
   State是从1到127的任意整数值，它表示错误的调用状态信息。负数的state值将缺省为1。     
    
   OPTIONS指明错误的定制选项。OPTIONS的有效值如下：     
    
   1）    LOG。     
    
   将错误记录到服务器错误日志和NT事件日志中。该选项需要消息带有从19到25的严重程度。而只有系统管理员才能发出这种消息。     
    
   2）    NOWAIT。     
    
   将消息立即发送到客户端服务器。     
    
   3）    SETERROR。     
    
   不管其严重级别如何，将@@error的值设置为msg_id或5000。     
    
    
   远程过程调用     
    
   SQL    Server提供了调用驻留在不同服务器上的存储过程的能力。调用这样的存储过程称谓远程存储过程调用。为了使得调用能从一个SQL    Server转移到另一个服务器，两个服务器应该相互定义成对方的有效远程服务器。     
    
   设置远程服务器的配置：     
    
   ·    扩展某个服务器的组。     
    
   ·    右击该服务器并点击“Properties”。     
    
   ·    设置选项“Allow    other    SQL    Servers    to    connect    remotely    to    this    SQL    server    via    RPC”。     
    
   ·    设置“Query    time    out”选项的值，该值指定从一个查询处理返回所能等待的秒数。缺省值为0，表示允许无限的等待时间。     
    
   ·    设置完成配置选项之后，点击“OK”。     
    
   ·    重新启动服务器之后，修改将会生效。     
    
   ·    在另一台远程服务器上重复相同的步骤。     
    
   调用远程存储过程需要指明服务器的名称，后带数据库的名称和拥有者的名称。下面是在不同的服务器（Server2）上调用一个存储过程的示例。     
    
   Exec    server2.pubs.dbo.myproc