CODEWITHSUNDEEP
sql-serverstored-procedurescase
b4tm4n
b4tm4n

Reputation: 1

SQL Server stored procedure with case

I am trying to create a stored procedure in SQL Server 2019 where you should insert an employee number and an amount of salary increase by the keyboard.

You should add the amount to the salary if the employee number does not have any commission.

If he has some commission then you should show an error message. You should also show an error message if the employee number does not exist in the table

I am trying to solve this exercise with a case structure but I am not able to do it

Can someone help me?

Thanks in advance

CREATE PROCEDURE subir_sueldo
    @emp int,
    @com int
AS
BEGIN
    SELECT EMP_NO
    FROM EMPLE

    CASE
        WHEN COMISION IS NULL 
            THEN 
                 UPDATE EMPLE 
                 SET SALARIO = SALARIO + @com 
                 WHERE EMP_NO = @emp  
        WHEN COMISION IS NOT NULL 
            THEN
                PRINT 'the comission is not null '
        WHEN @emp NOT IN (SELECT @emp FROM EMPLE WHERE DEPT_NO = @emp) 
            THEN
                PRINT 'user does not exist'
        ELSE
            BREAK
    END 
END

Upvotes: 0

Views: 460

Answers (2)

Thom A
Thom A

Reputation: 95554

As an alternative idea, you could do something like this, that tries to UPDATE the table, and then if it reports no rows were updated, let's the user know; though this will not give different errors for if the user does not exist or if they have a commission (though sometimes ambiguity is good).

CREATE PROCEDURE dbo.subir_sueldo @emp int, @com int AS
BEGIN

    SET NOCOUNT ON;

    UPDATE dbo.EMPLE
    SET SALARIO = SALARIO +@Com
    WHERE EMP_NO = @emp
      AND COMISION IS NULL;

    IF @@ROWCOUNT = 0
        --I use THROW as it seems like you actually want an error
        --Use an error number appropriate for your environment.
        THROW 65489, N'User does not exist, or Commission is not NULL.',10;
END;

Upvotes: 1

marc_s
marc_s

Reputation: 754298

CASE in T-SQL/SQL Server is an expression that returns one of several possible, atomic, single values - it is NOT a flow control statement ..... for that, you need to use IF ... ELSE ... statements - something like this:

CREATE PROCEDURE subir_sueldo
    @emp INT,
    @com INT
AS
BEGIN
    -- you didn't show or tell what datatype "Commission" 
    -- (should be with TWO "m" and "s" in English) is - just guessing here!
    DECLARE @Commission DECIMAL(20,4);  

    SELECT @Commission = COMMISSION
    FROM EMPLE
    WHERE EMP_NO = @emp;

    IF (@Commission IS NULL)
        UPDATE EMPLE 
        SET SALARIO = SALARIO + @com 
        WHERE EMP_NO = @emp  
    ELSE 
        PRINT 'The commission is not null '

    -- you would probably want to check this *FIRST* and stop the
    -- procedure if your @emp doesn't match an existing user.....        
    IF @emp NOT IN (SELECT @emp FROM EMPLE WHERE DEPT_NO = @emp) 
        PRINT 'user does not exist'
    ELSE
        BREAK
END

Upvotes: 2

Related Questions