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’)
