使用存储过程并返回值与及返回值的获得方法

<p><span style="color: #0000ff;">CREATE</span><span style="color: #000000;"></span><span style="color: #0000ff;">TABLE</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span><span style="color: #000000;">.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">Order</span><span style="color: #ff0000;">]</span><span style="color: #000000;">(<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">o_id</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">bigint</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #ff00ff;">IDENTITY</span><span style="color: #000000;">(</span><span style="font-weight: bold; color: #800000;">1</span><span style="color: #000000;">,</span><span style="font-weight: bold; color: #800000;">1</span><span style="color: #000000;">)</span><span style="color: #808080;">NOT</span><span style="color: #000000;"></span><span style="color: #0000ff;">FOR</span><span style="color: #000000;"></span><span style="color: #0000ff;">REPLICATION</span><span style="color: #000000;"></span><span style="color: #808080;">NOT</span><span style="color: #000000;"></span><span style="color: #0000ff;">NULL</span><span style="color: #000000;">,<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">o_buyerid</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">int</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #808080;">NOT</span><span style="color: #000000;"></span><span style="color: #0000ff;">NULL</span><span style="color: #000000;"><br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top">)</span><br><span style="color: red;"><strong>1.OUPUT参数返回值<br></strong><span style="font-family: Verdana;">例： 向Order表插入一条记录,返回其标识<br></span><br><div style="padding-right: 5px; padding-left: 4px; font-size: 13px; padding-bottom: 4px; width: 98%; padding-top: 4px; background-color: #eeeeee; border: #cccccc 1px solid;">
<strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"><span style="color: #0000ff;">CREATE</span><span style="color: #000000;"></span><span style="color: #0000ff;">PROCEDURE</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span><span style="color: #000000;">.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">nb_order_insert</span><span style="color: #ff0000;">]</span></strong><strong><span style="color: #000000;">(<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #008000;">@o_buyerid</span><span style="color: #000000;"></span><span style="color: #0000ff;">int</span></strong><strong><span style="color: #000000;">,<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #008000;">@o_id</span><span style="color: #000000;"></span><span style="color: #0000ff;">bigint</span></strong><strong><span style="color: #000000;">OUTPUT<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top">)<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">AS</span></strong><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><span style="color: #0000ff;"><strong>BEGIN</strong></span><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><strong><span style="color: #0000ff;">SET</span><span style="color: #000000;">NOCOUNT</span><span style="color: #0000ff;">ON</span></strong><strong><span style="color: #000000;">;<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">BEGIN</span></strong><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><strong><span style="color: #0000ff;">INSERT</span><span style="color: #000000;"></span><span style="color: #0000ff;">INTO</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">Order</span><span style="color: #ff0000;">]</span></strong><strong><span style="color: #000000;">(o_buyerid)<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">VALUES</span><span style="color: #000000;">(</span><span style="color: #008000;">@o_buyerid</span></strong><strong><span style="color: #000000;">)<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">SET</span><span style="color: #000000;"></span><span style="color: #008000;">@o_id</span><span style="color: #000000;"></span><span style="color: #808080;">=</span><span style="color: #000000;"></span><span style="font-weight: bold; color: #008000;">@@IDENTITY</span></strong><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><span style="color: #0000ff;"><strong>END</strong></span><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><span style="color: #0000ff;"><strong>END</strong></span>
</div>
</span><span style="font-family: Verdana;"><span style="font-family: Verdana;"><br>存储过程中获得方法：<br><br><div style="padding-right: 5px; padding-left: 4px; font-size: 13px; padding-bottom: 4px; width: 98%; padding-top: 4px; background-color: #eeeeee; border: #cccccc 1px solid;">
<img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"><span style="color: #0000ff;">DECLARE</span><span style="color: #000000;"></span><span style="color: #008000;">@o_buyerid</span><span style="color: #000000;"></span><span style="color: #0000ff;">int</span><span style="color: #000000;"><br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">DECLARE</span><span style="color: #000000;"></span><span style="color: #008000;">@o_id</span><span style="color: #000000;"></span><span style="color: #0000ff;">bigint</span><span style="color: #000000;"><br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">EXEC</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">nb_order_insert</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #008000;">@o_buyerid</span><span style="color: #000000;">,o_id</span><span style="color: #0000ff;">bigint</span>
</div>
<br><span style="font-family: Verdana;"><strong><span style="color: #ff0000;"><span style="font-family: Verdana;"><strong>2.RETURN过程返回值</strong></span></span><br><br></strong>
<div style="padding-right: 5px; padding-left: 4px; font-size: 13px; padding-bottom: 4px; width: 98%; padding-top: 4px; background-color: #eeeeee; border: #cccccc 1px solid;">
<strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"><span style="color: #0000ff;">CREATE</span><span style="color: #000000;"></span><span style="color: #0000ff;">PROCEDURE</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span><span style="color: #000000;">.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">nb_order_insert</span><span style="color: #ff0000;">]</span></strong><strong><span style="color: #000000;">(<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #008000;">@o_buyerid</span><span style="color: #000000;"></span><span style="color: #0000ff;">int</span></strong><strong><span style="color: #000000;">,<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #008000;">@o_id</span><span style="color: #000000;"></span><span style="color: #0000ff;">bigint</span></strong><strong><span style="color: #000000;">OUTPUT<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top">)<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">AS</span></strong><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><span style="color: #0000ff;"><strong>BEGIN</strong></span><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><strong><span style="color: #0000ff;">SET</span><span style="color: #000000;">NOCOUNT</span><span style="color: #0000ff;">ON</span></strong><strong><span style="color: #000000;">;<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">IF</span><span style="color: #000000;">(</span><span style="color: #808080;">EXISTS</span><span style="color: #000000;">(</span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;"></span><span style="color: #808080;">*</span><span style="color: #000000;"></span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">Shop</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">s_id</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #808080;">=</span><span style="color: #000000;"></span><span style="color: #008000;">@o_shopid</span></strong><strong><span style="color: #000000;">))<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">BEGIN</span></strong><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><strong><span style="color: #0000ff;">INSERT</span><span style="color: #000000;"></span><span style="color: #0000ff;">INTO</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">Order</span><span style="color: #ff0000;">]</span></strong><strong><span style="color: #000000;">(o_buyerid)<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">VALUES</span><span style="color: #000000;">(</span><span style="color: #008000;">@o_buyerid</span></strong><strong><span style="color: #000000;">)<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">SET</span><span style="color: #000000;"></span><span style="color: #008000;">@o_id</span><span style="color: #000000;"></span><span style="color: #808080;">=</span><span style="color: #000000;"></span><span style="font-weight: bold; color: #008000;">@@IDENTITY</span></strong><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><strong><span style="color: #0000ff;">RETURN</span><span style="color: #000000;"></span><span style="font-weight: bold; color: #800000;">1</span></strong><strong><span style="color: #000000;">—插入成功返回1<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">END</span></strong><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><span style="color: #0000ff;"><strong>ELSE</strong></span><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><strong><span style="color: #0000ff;">RETURN</span><span style="color: #000000;"></span><span style="font-weight: bold; color: #800000;">0</span></strong><strong><span style="color: #000000;">—插入失败返回0<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">END</span></strong>
</div>
<br><span style="font-family: Verdana;">存储过程中的获取方法<br><div style="padding-right: 5px; padding-left: 4px; font-size: 13px; padding-bottom: 4px; width: 98%; padding-top: 4px; background-color: #eeeeee; border: #cccccc 1px solid;">
<img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"><span style="color: #0000ff;">DECLARE</span><span style="color: #000000;"></span><span style="color: #008000;">@o_buyerid</span><span style="color: #000000;"></span><span style="color: #0000ff;">int</span><span style="color: #000000;"><br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">DECLARE</span><span style="color: #000000;"></span><span style="color: #008000;">@o_id</span><span style="color: #000000;"></span><span style="color: #0000ff;">bigint</span><span style="color: #000000;"><br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">DECLARE</span><span style="color: #000000;"></span><span style="color: #008000;">@result</span><span style="color: #000000;"></span><span style="color: #0000ff;">bit</span><span style="color: #000000;"><br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">EXEC</span><span style="color: #000000;"></span><span style="color: #008000;">@result</span><span style="color: #000000;"></span><span style="color: #808080;">=</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">nb_order_insert</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #008000;">@o_buyerid</span><span style="color: #000000;">,o_id</span><span style="color: #0000ff;">bigint</span><span style="color: #000000;"></span>
</div>
<br><span style="font-family: Verdana;"><strong>3.SELECT 数据集返回值<br><br></strong>
<div style="padding-right: 5px; padding-left: 4px; font-size: 13px; padding-bottom: 4px; width: 98%; padding-top: 4px; background-color: #eeeeee; border: #cccccc 1px solid;">
<strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"><span style="color: #0000ff;">CREATE</span><span style="color: #000000;"></span><span style="color: #0000ff;">PROCEDURE</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span><span style="color: #000000;">.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">nb_order_select</span><span style="color: #ff0000;">]</span></strong><strong><span style="color: #000000;">(<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #008000;">@o_id</span><span style="color: #000000;"></span><span style="color: #0000ff;">int</span></strong><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top">)<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><span style="color: #0000ff;"><strong>AS</strong></span><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><span style="color: #0000ff;"><strong>BEGIN</strong></span><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><strong><span style="color: #0000ff;">SET</span><span style="color: #000000;">NOCOUNT</span><span style="color: #0000ff;">ON</span></strong><strong><span style="color: #000000;">;<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;">o_id,o_buyerid</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">Order</span><span style="color: #ff0000;">]</span></strong><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><strong><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">o_id</span><span style="color: #808080;">=</span><span style="color: #000000;"></span><span style="color: #008000;">@o_id</span></strong><span style="color: #000000;"><br><strong><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></strong></span><span style="color: #0000ff;"><strong>GO</strong></span>
</div>
<br><span style="font-family: Verdana;"><span style="color: #0000ff;"><span style="font-family: Verdana;">存储过程中的获取方法<br></span>（1）、使用临时表的方法<br><br><div style="padding-right: 5px; padding-left: 4px; font-size: 13px; padding-bottom: 4px; width: 98%; padding-top: 4px; background-color: #eeeeee; border: #cccccc 1px solid;">
<img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"><span style="color: #0000ff;">CREATE</span><span style="color: #000000;"></span><span style="color: #0000ff;">TABLE</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span><span style="color: #000000;">.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">Temp</span><span style="color: #ff0000;">]</span><span style="color: #000000;">(<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">o_id</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">bigint</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #ff00ff;">IDENTITY</span><span style="color: #000000;">(</span><span style="font-weight: bold; color: #800000;">1</span><span style="color: #000000;">,</span><span style="font-weight: bold; color: #800000;">1</span><span style="color: #000000;">)</span><span style="color: #808080;">NOT</span><span style="color: #000000;"></span><span style="color: #0000ff;">FOR</span><span style="color: #000000;"></span><span style="color: #0000ff;">REPLICATION</span><span style="color: #000000;"></span><span style="color: #808080;">NOT</span><span style="color: #000000;"></span><span style="color: #0000ff;">NULL</span><span style="color: #000000;">,<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">o_buyerid</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">int</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #808080;">NOT</span><span style="color: #000000;"></span><span style="color: #0000ff;">NULL</span><span style="color: #000000;"><br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top">)<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">INSERT</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">Temp</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #0000ff;">EXEC</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">nb_order_select</span><span style="color: #ff0000;">]</span><span style="color: #000000;"></span><span style="color: #008000;">@o_id</span><span style="color: #000000;"><br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top">–这时</span><span style="color: #0000ff;">Temp</span><span style="color: #000000;">就是EXEC执行SELECT后的结果集<br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;"></span><span style="color: #808080;">*</span><span style="color: #000000;"></span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">Temp</span><span style="color: #ff0000;">]</span><span style="color: #000000;"><br><img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"></span><span style="color: #0000ff;">DROP</span><span style="color: #000000;"></span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">Temp</span><span style="color: #ff0000;">]</span><span style="color: #000000;">—删除临时表</span>
</div>
<br>（2）、速度不怎么样.（不推荐）<br><br><div style="padding-right: 5px; padding-left: 4px; font-size: 13px; padding-bottom: 4px; width: 98%; padding-top: 4px; background-color: #eeeeee; border: #cccccc 1px solid;">
<img src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" alt="" align="top"><span style="color: #0000ff;">SELECT</span><span style="color: #000000;"></span><span style="color: #808080;">*</span><span style="color: #000000;"></span><span style="color: #0000ff;">from</span><span style="color: #000000;"></span><span style="color: #ff00ff;">openrowset</span><span style="color: #000000;">(’provider_name</span><span style="color: #ff0000;">'</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">'</span><span style="color: #000000;">Trusted_Connection</span><span style="color: #808080;">=</span><span style="color: #000000;">yes’,</span><span style="color: #ff0000;">'</span><span style="color: #ff0000;">execnb_order_select’)</span>
</div>
原文出处：<a href="http://www.cnblogs.com/zm235/archive/2008/05/09/1189622.html">http://www.cnblogs.com/zm235/archive/2008/05/09/1189622.html</a></span></span></span></span></span></span></span></p>