can case only be used in select statement? 1

[mflancour]

I want to use a case statement to run varying commands, but even a simple case wont work unless i use "select case" but doing that wont let me run a command.

 

[gmmastros]

It might be a good idea to explain a little more.

Without knowing anything further, my best advice is... You may want to look in to the IF statement.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mflancour]

This is only an example and not what I want, but it gives you an idea of what I am trying to do. Yes the obvious alternative is a complicated if statement, but if possible I'd rather use a case. Also I'm just curious why the darn thing is not working. From the tests I've run it seems a case statement is only meant for inside a select statement.

      CASE 
         WHEN @stufff = 'R' THEN 
                  INSERT INTO TABL1 VALUES(1,2,3)
         WHEN @stufff = 'M' THEN 
                  INSERT INTO TABL2 VALUES(1,2,3)
         WHEN @stufff = 'T' THEN 
                  INSERT INTO TABL15 VALUES(1,2,3)
         WHEN @stufff = 'S' THEN 
         	IF @vc_url <> ''
         	BEGIN
	         	SET @v_external_cmd = 'del ' + @v_external_cmd
	         	EXEC master..xp_cmdshell @v_external_cmd, NO_OUTPUT
	         END

         ELSE 'Not for sale'
      END

 

[gmmastros]

In this case, you can't use case.


Instead, if's are the way to go. I agree with you that the CASE syntax looks a bit nicer, but what choice do you have. Here's how your code would look with if's.

[COLOR=blue]If[/color] @stufff = [COLOR=red]'R'[/color]
	[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] TABL1 [COLOR=blue]VALUES[/color](1,2,3)
[COLOR=blue]Else[/color] [COLOR=blue]If[/color] @stufff = [COLOR=red]'M'[/color]
	[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] TABL2 [COLOR=blue]VALUES[/color](1,2,3)
[COLOR=blue]Else[/color] [COLOR=blue]If[/color] @stufff = [COLOR=red]'T'[/color] 
	[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] TABL15 [COLOR=blue]VALUES[/color](1,2,3)
[COLOR=blue]Else[/color] [COLOR=blue]If[/color] @stufff = [COLOR=red]'S'[/color] 
    [COLOR=blue]IF[/color] @vc_url <> [COLOR=red]''[/color]
      [COLOR=blue]BEGIN[/color]
        [COLOR=blue]SET[/color] @v_external_cmd = [COLOR=red]'del '[/color] + @v_external_cmd
        [COLOR=blue]EXEC[/color] master..xp_cmdshell @v_external_cmd, NO_OUTPUT
      [COLOR=blue]END[/color]
[COLOR=blue]ELSE[/color] [COLOR=red]'Not for sale'[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

Case can be used in update or insert or delete statments as well but not for control flow outside the insert as you tried to do. Rememebr when converting to if that you must use begin and end if you intend to have more than one statment affected by the if branch. I use them always just to make it easier to maintain.

Questions about posting. See faq183-874