WHILE loop not working in a stored procedure in SQL Server

Question

I have multiple code blocks in a script where each block is executed based on the input parameter.

I created the following stored procedure:

CREATE PROCEDURE [usp_Auto_Extraction_Parameterized] 
    @data nvarchar(30)
AS
BEGIN
    WHILE (@data = '1')
    BEGIN
        SELECT * FROM abc
    END;

    WHILE (@data = '2')
    BEGIN
        SELECT * FROM xyz
    END;
    
    WHILE (@data = '3')
    BEGIN
        SELECT * FROM def
    END 
END

The expected outcome after executing the stored procedure like this:

EXEC [usp_Auto_Extraction_Parameterized] @data = '3'

is that only the last code block should be executed, but here all the three blocks are executed.

What am I doing wrong? Please help!

Answer 1

You can try using IF-statements instead of WHILE. That should fix your problem:

CREATE PROCEDURE [usp_Auto_Extraction_Parameterized] @data nvarchar(30)
AS
BEGIN
  IF (@data = '1')
  BEGIN
      SELECT * FROM abc
  END
  ELSE IF (@data = '2')
  BEGIN
      SELECT * FROM xyz
  END
  ELSE IF (@data = '3')
  BEGIN
      SELECT * FROM def
  END
END