MSSQL之try Catch的用法通俗讲解

try catch是sql用于出错异常处理的语句块，当我们在写sql语句的时候（通常写比较复杂的存储过程时），合理使用try catch 可以将sql代码运行时的错误信息捕获，从而可以让我们

不需要处处判断便可以更加简单地处理我们所遇到的问题。

例如： 我们的过程脚本里面经常要进行除法计算  如这段代码   declare @i int=0  ，@j = 100     select @j/@i

如果有一天我们要写很复杂的计算公式，经常通过变量加减乘除我们得每次都加个判断，而有了try catch ,我们便不用加这些判断了，直接捕获即可

3.try catch使用方法如下，读者朋友可以自己到SQL 2008或2005查询分析器执行（亲测无误）

<span style="color: #0000ff">Create</span> <span style="color: #0000ff">proc</span> up_TestCatch1(<span style="color: #008000">@iStatus</span> <span style="color: #0000ff">int</span> output,<span style="color: #008000">@StatusText</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">40</span><span style="color: #000000">) out)  
</span><span style="color: #0000ff">as</span>   
<span style="color: #0000ff">begin</span>  
<span style="color: #0000ff">select</span> <span style="color: #008000">@iStatus</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span>,<span style="color: #008000">@StatusText</span><span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">执行成功</span><span style="color: #ff0000">‘</span>
    <span style="color: #0000ff">begin</span><span style="color: #000000"> try  
        </span><span style="color: #0000ff">begin</span> <span style="color: #0000ff">tran</span>  
            <span style="color: #0000ff">select</span> 执行情况<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">无错时此处将执行</span><span style="color: #ff0000">‘</span> 
        <span style="color: #0000ff">commit</span> <span style="color: #0000ff">tran</span>
    <span style="color: #0000ff">end</span><span style="color: #000000"> try
    
    </span><span style="color: #0000ff">begin</span><span style="color: #000000"> catch
        </span><span style="color: #0000ff">rollback</span> <span style="color: #0000ff">tran</span>
    <span style="color: #0000ff">end</span><span style="color: #000000"> catch
</span><span style="color: #0000ff">end</span>
<span style="color: #0000ff">Create</span> <span style="color: #0000ff">proc</span> up_TestCatch2(<span style="color: #008000">@iStatus</span> <span style="color: #0000ff">int</span> output,<span style="color: #008000">@StatusText</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">40</span><span style="color: #000000">) out)  
</span><span style="color: #0000ff">as</span>   
<span style="color: #0000ff">begin</span>  
<span style="color: #0000ff">declare</span> <span style="color: #008000">@i</span> <span style="color: #0000ff">int</span> <span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span>
<span style="color: #0000ff">select</span> <span style="color: #008000">@iStatus</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span>,<span style="color: #008000">@StatusText</span><span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">执行成功</span><span style="color: #ff0000">‘</span>
    <span style="color: #0000ff">begin</span><span style="color: #000000"> try  
        </span><span style="color: #0000ff">begin</span> <span style="color: #0000ff">tran</span>  
            <span style="color: #0000ff">select</span> <span style="color: #008000">@i</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #808080">/</span><span style="color: #800000; font-weight: bold">0</span> <span style="color: #008080">--</span><span style="color: #008080">此处出错</span>
            <span style="color: #0000ff">select</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">出错时此处将不会执行跳过</span><span style="color: #ff0000">‘</span> 
        <span style="color: #0000ff">commit</span> <span style="color: #0000ff">tran</span>
    <span style="color: #0000ff">end</span><span style="color: #000000"> try
    
    </span><span style="color: #0000ff">begin</span><span style="color: #000000"> catch
        </span><span style="color: #008080">/*</span><span style="color: #008080">出错时才捕获</span><span style="color: #008080">*/</span>
            <span style="color: #0000ff">select</span> 执行情况<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">出错时此处将执行</span><span style="color: #ff0000">‘</span>
        <span style="color: #0000ff">rollback</span> <span style="color: #0000ff">tran</span>
    <span style="color: #0000ff">end</span><span style="color: #000000"> catch
</span><span style="color: #0000ff">end</span>
<span style="color: #0000ff">declare</span>  <span style="color: #008000">@iStatus</span> <span style="color: #0000ff">int</span> ,<span style="color: #008000">@StatusText</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">40</span><span style="color: #000000">) 
</span><span style="color: #0000ff">exec</span> up_TestCatch1 <span style="color: #008000">@iStatus</span> out ,<span style="color: #008000">@StatusText</span><span style="color: #000000"> out 
</span><span style="color: #0000ff">exec</span> up_TestCatch2 <span style="color: #008000">@iStatus</span> out ,<span style="color: #008000">@StatusText</span> out 

MSSQL之try Catch的用法通俗讲解

标签：计算   理解   weight   tca   跳过   status   arch   put   var