Stored Procedure that takes two inputs STORE CODE and EMPLOYEE CODE and displays message if correspo-CodePudding

Like question said, I have to create a stored procedure in SQL that takes two parameters, a store code and an employee code (I have a table EMPLOYEE that has columns STORE_CODE and EMP_CODE) and I need to print a message that tells if that employee works in that specific store or not.

Here is what I have so far:

CREATE OR ALTER PROCEDURE EmployeeVerify @STORE_CODE int, @EMP_CODE int
AS
DECLARE @store int
SELECT @store = STORE_CODE FROM EMPLOYEE WHERE STORE_CODE = @STORE_CODE

DECLARE @emp int
SELECT @emp = EMP_CODE FROM EMPLOYEE WHERE EMP_CODE = @EMP_CODE

IF @emp != @store
BEGIN
PRINT 'EMPLOYEE DOES NOT WORK AT THIS STORE'
END

ELSE
BEGIN
PRINT 'EMPLOYEE WORKS AT THIS STORE'
END

I tested this to see with an employee code 1 that should match store code 3, yet it says employee does not work at this store. Not sure how to go about connecting the inputs with the actual columns.

CodePudding user response：

Based on your sample data image it looks like you just need a simple exists check

if exists (
    select *
    from employee 
    where emp_code = @emp_code and store_code = @store_code
)
  print 'employee works at store'
else
  print 'employee does not work at store'