- 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
- 存储过程中的获取方法:
- 3.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] — 删除临时表
- 3.2使用openrowset(不过就是速度不怎么样)
- SELECT * from openrowset(’provider_name','Trusted_Connection=yes’,'exec nb_order_select’)
本文转自sucre03 51CTO博客,原文链接:http://blog.51cto.com/sucre/380219,如需转载请自行联系原作者