Add output with condition to a stored procedure in SQL?-CodePudding

When I execute the procedure I fill in the ID of the reserve part (mechanic shop), number of parts, and casenumber.

I'm trying to add an output somewhere which triggers if the number of parts I select are >= to the number of parts in stock, and outputs a message "reorder part"

The script works, it just doesn't do everything I need it to, yet.

resid = reserve part id
antal = number of parts
sagsnummer = case number
Reservedele = spare parts


CREATE PROCEDURE sp_opret_forbrug
    @resid      int, 
    @antal      int,
    @sagsnummer int
AS 
BEGIN  
    INSERT INTO dbo.forbrug (resid, antal, sagsnummer)
    VALUES (@resid, @antal, @sagsnummer) 

    UPDATE [dbo].[Reservedele]
    SET antal = Reservedele.antal - @antal
    WHERE reservedele.resid = @resid;
END 

--exec sp_opret_forbrug

--@resid = '49',

--@antal = '2',

--@sagsnummer ='11'

--drop procedure sp_opret_forbrug

CodePudding user response：

You could throw an error using THROW

CREATE OR ALTER PROCEDURE sp_opret_forbrug
    @resid      int, 
    @antal      int,
    @sagsnummer int
AS 

SET XACT_ABORT ON;

BEGIN TRAN;

IF EXISTS (SELECT 1
    FROM [dbo].[Reservedele]
    WHERE reservedele.resid = @resid
      AND Reservedele.antal < @antal)
  THROW 50000, 'Not enough stock, reorder', 0;

INSERT INTO dbo.forbrug (resid, antal, sagsnummer)
    VALUES (@resid, @antal, @sagsnummer) 

UPDATE [dbo].[Reservedele]
    SET antal = Reservedele.antal - @antal
    WHERE reservedele.resid = @resid;

COMMIT;