using Switch like logic in T-SQL

问题:

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?

回答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

回答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

回答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'

回答4:

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.