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

You can do something like these:

SET @SQL = CASE @Type             WHEN 1 THEN                     @SQL1             WHEN 2 THEN                       @SQL2             ELSE                      @SQL3      END  EXEC(@SQL) 

UPDATE 9/18/2016

NOTE: This is a easy and quick solution, but keep in mind this is a not a long term solution to be implemented in production environments. I agree with @Jon Galloway: "I don't think CASE is the right fit here."

Another more professional implementation will be to create 3 different stored procedures that do their own job (Single Responsibility Principle), something like this:

If @Type = 1     EXEC InsertCredit @CompanyName, @PhoneNumber, @City, @State Else If @Type = 2     EXEC InsertDebit @CompanyName, @PhoneNumber, @City, @State Else If @Type = 3     EXEC OtherInsert @CompanyName, @PhoneNumber, @City, @State