存储过程返回值

CREATE
 
TABLE
 
[
dbo
]
.
[
Order
]
(
[
o_id
]
 
[
bigint
]
 
IDENTITY
(
1
,
1
) 
NOT
 
FOR
 
REPLICATION
 
NOT
 
NULL
,
[
o_buyerid
]
 
[
int
]
 
NOT
 
NULL

)

1.OUPUT参数返回值例： 向Order表插入一条记录,返回其标识

  

CREATE PROCEDURE [dbo].[nb_order_insert]
 (
@o_buyerid int
  ,
@o_id bigint
  OUTPUT
)
AS
 

 BEGIN
 

 SET NOCOUNT ON
 ;
BEGIN
 

 INSERT INTO [Order]
 (o_buyerid )
VALUES (@o_buyerid
  )
SET @o_id = @@IDENTITY
 

 END
 

 END
存储过程中获得方法：

  
DECLARE  
 @o_buyerid
  
 int
 
 DECLARE
  
 @o_id
  
 bigint
 
 EXEC
  
 [
 nb_order_insert
 ]
  
 @o_buyerid
  ,o_id 
 bigint 

2.RETURN过程返回值
 
  

CREATE PROCEDURE [dbo].[nb_order_insert]
 (
@o_buyerid int
  ,
@o_id bigint
  OUTPUT
)
AS
 

 BEGIN
 

 SET NOCOUNT ON
 ;
IF(EXISTS(SELECT * FROM [Shop] WHERE [s_id] = @o_shopid
 ))
BEGIN
 

 INSERT INTO [Order]
 (o_buyerid )
VALUES (@o_buyerid
  )
SET @o_id = @@IDENTITY
 

 RETURN 1
  — 插入成功返回1
END
 

 ELSE
 

 RETURN 0
  — 插入失败返回0
END 
 

存储过程中的获取方法
  
DECLARE   @o_buyerid
  
 int
 
 DECLARE
  
 @o_id
  
 bigint
 
 DECLARE
  
 @result
  
 bit
 
 EXEC
  
 @result
  
 =
  
 [
 nb_order_insert
 ]
  
 @o_buyerid
  ,o_id 
 bigint
   

3.SELECT 数据集返回值
 
  

CREATE PROCEDURE [dbo].[nb_order_select]
 (
@o_id int
 
)

 AS
 

 BEGIN
 

 SET NOCOUNT ON
 ;
SELECT o_id,o_buyerid FROM [Order]
 

 WHERE o_id = @o_id
 

 GO 

存储过程中的获取方法
（1）、使用临时表的方法

  
CREATE   TABLE  
 [
 dbo
 ]
 .
 [
 Temp
 ]
 (
 [
 o_id
 ]
  
 [
 bigint
 ]
  
 IDENTITY
 (
 1
 ,
 1
 ) 
 NOT
  
 FOR
  
 REPLICATION
  
 NOT
  
 NULL
 ,
 [
 o_buyerid
 ]
  
 [
 int
 ]
  
 NOT
  
 NULL
 
)
 INSERT
  
 [
 Temp
 ]
  
 EXEC
  
 [
 nb_order_select
 ]
  
 @o_id
 
– 这时  Temp  就是EXEC执行SELECT 后的结果集
 SELECT
  
 *
  
 FROM
  
 [
 Temp
 ]
 
 DROP
  
 [
 Temp
 ]
  — 删除临时表 

（2）、速度不怎么样.（不推荐）


  
SELECT   *   from
  
 openrowset
 (’provider_name
 '
 ,
 '
 Trusted_Connection
 =
 yes’,
 '
 exec nb_order_select’)