'How to set a value from an output statement within a if clause
I've this code in my stored procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[VerifyBasketIdToUse_DBO]
@Basket_IDD INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT MAX(Basket_Id) FROM [Order]) = (SELECT MAX(Basket_Id) FROM [Order] WHERE Order_PayState=1)
EXEC DBO.InsertBasket_DBO
SET @Basket_IDD = @Basket_Id OUTPUT
ELSE
SET @Basket_IDD = (SELECT MAX(Basket_Id)FROM [Order])
SELECT @Basket_IDD
END
and this one for the SP with the output value
ALTER PROCEDURE [dbo].[InsertBasket_DBO]
@Basket_Id INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[Basket]([Basket_Amount])
OUTPUT inserted.Basket_Id
values (0)
END
but Sql Server notices that my @Basket_Id OUTPUT from the second Stored Procedure isn't defined in the first Stored Procedure, I don't understand why ? could someone help me ?
ALTER PROCEDURE [dbo].[VerifyBasketIdToUse_DBO]
@Basket_IDD INT OUTPUT
AS
BEGIN
IF (SELECT MAX(Basket_Id) FROM [Order]) = (SELECT MAX(Basket_Id) FROM [Order] WHERE Order_PayState=1)
BEGIN
EXEC @Basket_IDD = dbo.InsertBasket_DBO
END
ELSE
BEGIN
SET @Basket_IDD = (SELECT MAX(Basket_Id)FROM [Order])
END
SELECT @Basket_IDD
END
Solution 1:[1]
Inside first procedure, instead of
EXEC DBO.InsertBasket_DBO
SET @Basket_IDD = @Basket_Id OUTPUT
use
EXEC DBO.InsertBasket_DBO @Basket_IDD OUTPUT
In second procedure, replace
INSERT INTO [dbo].[Basket]([Basket_Amount])
OUTPUT inserted.Basket_Id
values (0)
with
INSERT INTO [dbo].[Basket]([Basket_Amount])
values (0)
select @Basket_Id = scope_identity()
I can't check syntax and results just now, but I hope this gives you some directions :)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Arvo |
