IF ELSE Execution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IF ELSE Execution

Author

Topic

learntsql

524 Posts

Posted - 2009-12-30 : 04:13:41

Hi..
I wrote a sp with IF ELSE logic
i.e
IF(@Param<>Value1)
BEGIN
SELECT Statement goes here
END
ELSE
BEGIN
SELECT Satement1
UNION
SELECT Statement 2
END

When I see Display Estimated Execution plan for @Param Value as Value1
the statistics are
-->IF block is showing 4%
and ELSE block is showing 96%

My Query is as i passed value to execute only IF block
why its showing 96% for ELSE block ?
Is ELSE block also executing?
How IF ELSE logic executes?
Please guide me.
TIA.

Sachin.Nand

2937 Posts

Posted - 2009-12-30 : 04:34:28

What happens if you do this?

IF(@Param<>Value1)
BEGIN
SELECT Statement goes here
END

IF(@Param=Value1)
BEGIN
SELECT Satement1
UNION
SELECT Statement 2
END

PBUH

learntsql

524 Posts

Posted - 2009-12-30 : 04:42:31

Thanks Idera,
Even This is showing same statistics.
no difference.

divya.ce
Starting Member

16 Posts

Posted - 2009-12-30 : 04:57:35

I have found that Estimated Execution Plan works on both IF and ELSE part regardless of what is the value of parameter set. When you will look into the Actual execution plan it will give the results of the path which the query has used either IF or ELSE.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-30 : 05:13:07

don't base anything on the "ESTIMATED EXECUTION PLAN"

use the actual execution plan instead. Also -- the percentages are not often representative as they are based solely on one metric.

Try using

SET STATISTICS IO ON
SET STATISTICS TIME ON

at the start of your test harness to get some better metrics.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources