using Switch like logic in T-SQL

user_55102 0
Viewed 80405

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?

tsql
5 Answers

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
user_14081290

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'

user_1143260

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.

Jon_Galloway_50

There is another way by using goto, although i dont suggest it.

DECLARE @Counter int;  
SET @Counter = 1;  
WHILE @Counter < 10  
BEGIN   
    SELECT @Counter  
    SET @Counter = @Counter + 1  
    IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.  
    IF @Counter = 5 GOTO Branch_Two  --This will never execute.  
END  
Branch_One:  
    SELECT 'Jumping To Branch One.'  
    GOTO Branch_Three; --This will prevent Branch_Two from executing.  
Branch_Two:  
    SELECT 'Jumping To Branch Two.'  
Branch_Three:  
    SELECT 'Jumping To Branch Three.';

user_44649270
Related