SQL Server 2008中,越来越柱早在输出参数
问题描述:
我有以下存储过程SQL Server 2008中,越来越柱早在输出参数
ALTER PROCEDURE [dbo].[sp_web_orders_insert]
(
@userId int = default,
@custId int = default,
@orderDate datetime = default,
@orderTotal money = default,
@statusId int = default,
@orderReference varchar(50) = default,
@custReference varchar(50) = default,
@order_ID INT output,
@orderReferenceOutput varchar(50) output
)
AS
SET NOCOUNT OFF;
INSERT INTO [web_orders] ([user_ID], [cust_ID], [orderDate], [orderTotal], [statusId], [orderReference], [custReference])
VALUES (@userId, @custId, @orderDate, @orderTotal, @statusId , 'PLC' + REPLICATE('0', (7 - LEN((select MAX(order_ID) from web_orders)))) + CAST((select(max(order_ID)+1) from web_orders) AS VARCHAR(5)), @custReference);
SET @order_ID = @@IDENTITY
SET @orderReferenceOutput = select top 1 orderReference from web_orders
但越来越语法问题近选择,我怎么能找回了orderReference
我刚才插入的值?
答
两点:
- 使用
SCOPE_IDENTITY()
代替@@IDENTITY
(见why this is here或谷歌它...) - 使用
SELECT
声明,让您的值返回
喜欢的东西:
SET @order_ID = SCOPE_IDENTITY()
SELECT
@orderReferenceOutput = SELECT orderReference
FROM dbo.web_orders
WHERE order_Id = @order_ID
答
试试下面的代码的一部分,希望这将有助于你
SET @orderReferenceOutput = select @orderReference=(top 1 orderReference) from web_orders
答
select @order_ID = order_ID, @orderReferenceOutput = orderReference
from dbo.web_orders
where order_ID = SCOPED_IDENTITIY();
或者摆脱输出参数,并直接返回插入的记录:
select * from dbo.web_orders where order_Id = SCOPED_IDENTITIY();
是。程序可以像查询一样返回数据。
如果您发布代码,XML或数据样本,**请**在文本编辑器中突出显示这些行,然后单击编辑器工具栏上的“代码示例”按钮(“{}”)以精确地设置格式和语法突出显示它! –