[Resolved] CASE and SPROCS

[yrwyddfa]

I am trying to do this and that damn thing won't let me

select @Something
   case SomethingElse
      when 2 then proc_AProc2
      when 3 then proc_AProc3
   end

Is there something endemically wrong in this? I can't use a UDF because the proc has a transaction in it. I can't lift the transaction outside, because other places use the sproc on its own.

Is it just a syntactic balls-up on my part?

[edit] I am using SQL Server 2000 [/edit]

[Mark Gambo]

Take a look at this LINK

[yrwyddfa]

Take a look at this LINK

An interesting link, for sure, but it doesn't resolve my problem.

[jp140768]

The code you posted, is it VB or SQL Server?

Have you tried removing the second line i.e. CASE SomethingElse ?

If SQL SERVER, should you not say EXEC procedure?

[FunkyDexter]

I think Marks pointing you at Steve's post about needing to use an if instead of a Select.

In TSQL a select can be used as part of a SQL statement but not as a logic branching statement, you need to use Ifs for that.

[Mark Gambo]

I think Marks pointing you at Steve's post about needing to use an if instead of a Select.

In TSQL a select can be used as part of a SQL statement but not as a logic branching statement, you need to use Ifs for that.

Exactly, I didn't have a lot of time to elaborate when I posted earlier.

[yrwyddfa]

All of the procs I am using return a single value; are you saying that I cannot use a SPROC in this way with a CASE statement and have to use if/then's? I have about 20 conditions that go in this CASE statement and that's going to be awful mess if that's the case . . .

[szlamany]

If-blocks are really your only answer...

But what does:

select @Something
   case SomethingElse
      when 2 then proc_AProc2
      when 3 then proc_AProc3
   end

this really mean? You want to EXECUTE the PROC_APROC2 or PROC_APROC3 based on some condition?

If x=y Exec proc_AProc2
If x=z Exec proc_AProc3
.
.
.

Don't worry about the fact that the IF's are all checked - that's the least of the speed issue of a query...

If you find that there is danger put lots of ELSE statements:

If x=y Exec proc_AProc2
Else   If x=z Exec proc_AProc3
       Else If x=z and x=y Exec proc_AProc4
.
.
.

or use GOTO - it's truly allowed in T-SQL and unavoidable...

If x=y
Begin
   Exec Proc_AProc2
   Goto Bottom
End

If x=z
Begin
   Exec Proc_AProc2
   Goto Bottom
End

If x=y and x=z
Begin
   Exec Proc_AProc2
   Goto Bottom
End

Bottom:

[mabbas110]

I can not understand what u r trying to do. You are subistuting the proc name in the @something so that to run afterwards if this is the case then
select @Something
case SomethingElse
when 2 then "proc_AProc2"
when 3 then "proc_AProc3"
end
but i think so that its sql server code so it would be

select @Something
case SomethingElse
when 2 then exec proc_AProc2
when 3 then exec proc_AProc3
end
or

select @Something
case SomethingElse
when 2 then 'proc_AProc2'
when 3 then 'proc_AProc3'
end

exec @something

[yrwyddfa]

Cheers, Szlamany.

[Mark Gambo]

Don't forget to mark this thread as resolved.