April 20, 2010 at 9:03 am
Hello Everyone
a colleague & I are having a discussion about CASE Statement and an IF Statement. Which one has better performance?
There will be only 3 possible selections.
Please provide your 3 cents. I say using an IF Statement, and he like CASE Statements.
Andrew SQLDBA
April 20, 2010 at 9:05 am
I say "It depends". What is being done? IF is a control-flow statement used in stored procedures and multi-line TVF's. The CASE statement is actually a function used in DML statments.
April 20, 2010 at 9:14 am
Ooops, I forgot to add this little tid bit of info that would be helpful
One of the parameters will determine what query to fire off inside the sproc.
a little sample code:
DECLARE @TypeID int
SET @TypeID = 3
IF @TypeID = 1
BEGIN
SELECT 1
END
ELSE IF @TypeID = 2
BEGIN
SELECT 2
END
ELSE IF @TypeID = 3
BEGIN
SELECT 3
END
Thanks
Andrew SQLDBA
April 20, 2010 at 9:40 am
To elaborate on what Lynn mentioned, you can use either the IF or CASE statements as shown below:declare @var1 char(3),
@var2 char(3)
set @var1 = 'xyz'
if @var1 = 'xyz' set @var2 = '123'
select @var1, @var2
set @var2 = NULL
select @var1, @var2
set @var2 = case when @var1 = 'xyz' then '123' else CONVERT(char(3), NULL) end
select @var1, @var2
However, you cannot use the IF statement in select/update statements, but you can use the CASE statement in those:
declare @temp table (col1 char(3), col2 char(3))
insert into @temp values ('abc',NULL)
select * from @temp
update @temp set col2 = case when col1 = 'abc' then '999' else '123' end
select * from @temp
update @temp set col2 = case when col1 = 'xyz' then '999' else '123' end
select * from @temp
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 20, 2010 at 9:53 am
AndrewSQLDBA (4/20/2010)
Ooops, I forgot to add this little tid bit of info that would be helpfulOne of the parameters will determine what query to fire off inside the sproc.
a little sample code:
DECLARE @TypeID int
SET @TypeID = 3
IF @TypeID = 1
BEGIN
SELECT 1
END
ELSE IF @TypeID = 2
BEGIN
SELECT 2
END
ELSE IF @TypeID = 3
BEGIN
SELECT 3
END
Thanks
Andrew SQLDBA
I'd use IF in this particular case, but I'd probably use it to call other stored procedures depending on what the select statements were.
April 20, 2010 at 10:32 am
Thanks Everyone
Have a good one
Andrew SQLDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply