[RESOLVED] Case Statement in Stored Procedures

[kishore.kr]

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

[pranoy]

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

[kishore.kr]

hi

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

[pranoy]

yes....

[kishore.kr]

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

[szlamany]

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

[kishore.kr]

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

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.

[szlamany]

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

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.

[kishore.kr]

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

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.

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

[szlamany]

First off...

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.

[kishore.kr]

hi,

thank you for the infomation