CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Bhavesh
Bhavesh

Reputation: 9

How can I increase stored procedure performance with multiple if else statement?

I have a stored procedure with multiple if-elseif- else statements. When I try to remove this if elseif statement and execute only single portion of that query, it returns results very fast, but when use this query with multiple another queries using if-elseif case statement, then it takes too much time...

For example:

if @Status = 1
begin
    select .....
end
else if @Status = 2
begin
    select .....
end
else if @Status = 3
begin
    select .....
end
else if....

There are many more else if statements in this stored procedure..

Upvotes: 0

Views: 376

Answers (1)

Edgard
Edgard

Reputation: 71

Although it may be in some "don't do this" books, you can try to create a stored procedure for each status value and call it directly by building a dynamic TSQL statement ("exec yourproc_" + statusValue).

create proc youproc_1 as 
begin
select your_data_for_status1;
end;

create proc youproc_2 as 
begin
select your_data_for_status2;
end;

etc...

Upvotes: 0

Related Questions