using Switch like logic in T-SQL

Question

This looks like a noob T-SQL question but I want do switch like logic in a stored procedure and I was thinking that using a CASE would be the way to do this with something like

 SELECT CASE @Type
  WHEN 1 THEN
   INSERT INTO dbo.Credit (
    CompanyName,
    PhoneNumber,
    City,
    State
   ) VALUES ( 
    @CompanyName,
    @PhoneNumber,
    @City,
    @State) 
  WHEN 2 THEN  
   INSERT INTO dbo.Debit (
    CompanyName,
    PhoneNumber,
    City,
    State
   ) VALUES ( 
    @CompanyName,
    @PhoneNumber,
    @City,
    @State) 
  WHEN 3 THEN  
   --ETC
  END

but I keep getting errors, is there just a systax error or is what I'm doing out to lunch?

Answer 1

You need to use If/Else If structure, like this:

If @Type = 1
    Begin
     INSERT INTO dbo.Credit (
       CompanyName,
       PhoneNumber,
       City,
       State
     ) VALUES ( 
       @CompanyName,
       @PhoneNumber,
       @City,
       @State) 
    End
Else If @Type = 2
    Begin
     INSERT INTO dbo.Debit (
       CompanyName,
       PhoneNumber,
       City,
       State
     ) VALUES ( 
       @CompanyName,
       @PhoneNumber,
       @City,
       @State) 
    End
Else If @Type = 3
    Begin
     --ETC
    END

Answer 2

The CASE statement can only be certain clauses, not to control flow. You can use it in a SET or an UPDATE statement, but neither of those help when you're updating different tables. Without altering your database (e.g. creating a view or something), I don't think CASE is the right fit here.

Answer 3

Whilst there is nothing wrong with the answer by G Mastros, it may cause execution plan issues as the execution path will change each time the procedure is run. An alternative is to use the SELECT ... WHERE clause in the INSERT:

INSERT INTO dbo.Credit (
                CompanyName,
                PhoneNumber,
                City,
                State   ) 
SELECT 
                @CompanyName,
                @PhoneNumber,
                @City,
                @State
WHERE 
                @Type = 1

INSERT INTO dbo.Debit (
                CompanyName,
                PhoneNumber,
                City,
                State   ) 
SELECT 
                @CompanyName,
                @PhoneNumber,
                @City,
                @State
WHERE 
                @Type = 2

This way all the code is always executed, but only the one where the @Type matches will 'fire'