How to Return Database Result based on CASE statements?

Question

I have an application right now that has special user roles hardwired into the executable. It is tamper proof, but is a bit of a mess when it comes to new hires, role changes, etc.

So, I want to create a stored procedure that can return the appropriate employee badge numbers for any given operation.

My expertise is in C# development, but I am also the guy who works on the SQL Server (2000) database.

Here is what I'm starting out with, but T-SQL does not like this at all!

CREATE PROCEDURE sp1_GetApprovalBadges(@operation varchar(50)) as
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;
  declare @op varchar(50); 
  declare @num varchar(50); 
  declare @table table (NUM varchar(50) null);

  select @op=upper(@operation);
  case
    when @op='CLERK' then
      insert into @table (NUM) values (@num) where @num in ('000988','001508','003790','007912') end
    when @op='HRMANAGER' then
      insert into @table (NUM) values (@num) where @num in ('003035') end
    when @op='HUMANRESOURCES' then
      insert into @table (NUM) values (@num) where @num in ('002864','005491') end
    when @op='INFORMATIONTECHNOLOGY' then
      insert into @table (NUM) values (@num) where @num in ('001258','003423','007135','007546') end
    end;
    SELECT NUM from @table order by NUM;
END
GO

I realize this is very much like code that I write and is not database related, but having the database there affords me a great way to store and execute some scripts that I can modify as needed to keep my application working.

Answer 1

I see at least two issues

1. Replace case with if elses
2. execute table variables using dynamic sql

for example

if @op='CLERK'
  begin
      exec 'insert into ' + @table + '(NUM) values (' + @num + ') where' + @num + 'in    (''000988'',''001508',''003790',''007912'')' 
  end
  else if @op='HRMANAGER'
  begin
      i-- see above
  end
  else if @op='HUMANRESOURCES'
  begin
    -- see above
  end
  else if @op='INFORMATIONTECHNOLOGY'
  begin
      -- see above
  end

  exec 'SELECT NUM from' + @table + 'order by NUM;'

Syntax may not be exact, but the idea will work