[Resolved] CASE and SPROCS

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

VB Code:

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]

Take a look at this LINK

Quote:

---

Originally Posted by Mark Gambo

Take a look at this LINK

---

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

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?

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.

Quote:

---

Originally Posted by 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.

---

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

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 . . .

If-blocks are really your only answer...

But what does:

Code:

---

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?

Code:

---

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:

Code:

---

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...

Code:

---

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:

---

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

Cheers, Szlamany.

Don't forget to mark this thread as resolved.