[RESOLVED] Case Statement in Stored Procedures

hi

how can i use case statement in storedprocedures like if else statement. i know to use case-when-then-end statement in the SELECT statement. but in my stored procedure, i will be passing parameters and i have to execute a query according to the parameters. so i have to go for case statement. thank you

In SQL Server, I dont think case statement is supported except for within Select statement. You'll have to do with if..else statement.

hi

are you sure that there is no case statement in SQL Server except within Select statement..

yes....

ok. Thank you. i have to go to if-else statement itself..

Quote:

---

Originally Posted by kishore.kr

ok. Thank you. i have to go to if-else statement itself..

---

Show a bit of what you want to do with the CASE statement - this thread is a bit too vague to answer based on what's been said so far...

hi,

in the project there are more than 10 master tables like users, state, city etc.. instead of writing select stored procedure(for retrieving all records or a single based on criteria)., i thought to write in a single stored procedure using case (bcos options are more). then i tried case statement in stored procedure like if-else.
but the case only works in select statement. example of stored procedure i am using..

Code:

---

CREATE PROCEDURE Select_Masters
@Master as  nvarchar(100),
@SelectId as Integer
AS
if @SelectId = 0
begin
  if @Master = "City"
  begin
        select * from mCity
  end
  if @Master = "State"
  begin
        select * from mState
  end
  if @Master = "Country"
  begin
        select * from mCountry
  end
/*  ... */
end
else
begin
  if @Master = "City"
  begin
        select * from mCity where City_Code = @SelectId
  end
  if @Master = "State"
  begin
        select distinct state from mState where State_Code = @SelectId
  end
  if @Master = "Country"
  begin
        select distinct country from mCountry where Country_Code = @SelectId
/*    ...    */
  end
end

---

so instead going into if statement, i am willing to use switch-case statement.

In the situation you described, the CASE statement will not work - as was indicated earlier.

I personally would not recommend having a single SPROC process data from so many different tables. In a way it defeats the purpose of a stored execution plan, since the tables can differ so greatly in number of rows and index structure.

But you can do a single select statement for an "all row return" and a single "id return".

Code:

---

select * from mCity where City_Code = @SelectId or @SelectId=0

---

With that syntax you can at least have a single SELECT in each SPROC - doing the "specific ID" or "all id's" in one shot.

But if your tables are small and speed issues are not a problem, then what you have now will work.

hi,
szlamany : thanks for the tip. actually the database i am using will be dealing with huge amount of data online. so i will be going for your suggestion of having seperate stored procedures..

Quote:

---

select * from mCity where City_Code = @SelectId or @SelectId=0

---

i am using 0 as parameter if i wanted to select all the rows of the table.. so i guess this will not do for me.

Quote:

---

In a way it defeats the purpose of a stored execution plan, since the tables can differ so greatly in number of rows and index structure.

---

i am not getting clear picture of the above statement. if you find time then can you explain it clearly. thank you very much

First off...

Code:

---

select * from mCity where City_Code = @SelectId or @SelectId=0

---

This query will return all rows if the @SELECTID=0, or return just a single row matching the @SELECTID. That's what you had in the bigger IF/BLOCK - right?

As for execution plans. MS SQL Server stores execution plans for queries so that it knows the best attack method when a SPROC is called over and over again. That execution plan indicates whether a TABLE scan or INDEX scan is a better choice, along with other internals issues.

It would defeat the purpose of an EXECUTION plan to have a SPROC deal with a large number of different tables. MS SQL Server would never know if a subsequent call is for the same table as prior calls or not.

If you are having tables with large numbers of rows, then I would avoid having an IF/BLOCK for different tables in a single SPROC.

hi,

thank you for the infomation