使用存储过程并返回值与及返回值的获得方法
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’)