October 30, 2007 at 11:39 am
Hi I'm just trying to understand if...else statements. For instance can I use and if statement to set a parameter ie,
-----------------------------------------------
if (select count(*) MCommOpenOrders) > 1
Begin
declare @test-2 varchar(max)
set @test-2 = 'more than 1'
select 'test'
end
else
if (select count(*) from MCommOpenOrders) < 1
begin
declare @test-2 varchar(max)
set @test-2 = 'less than 1'
end
print @test-2
--------------------------------------------
the code above gives me this error message:
The variable name '@test' has already been declared. Variable names must be unique within a query batch or stored procedure.
If I can't use an if statement how else can I dynamically set a parameter?
Also I have a second question:
What is the difference between if and iif?
Thank you in advance for your help.
October 30, 2007 at 11:44 am
Declare your parameter once before the first IF statement
I dont think IIF is a valid SQL keyword.
October 30, 2007 at 11:47 am
Try DECLAREing your variable before your IF statements.
Then, SET in in the nested logic.
Try BOL for the difference between IF and IIF - it's a great resource.
Download here:
- Simon Doubt
October 30, 2007 at 11:53 am
I see references to an IIF function
IIF(expression, truevalue, falsevalue)
where if the expression is true, the true value will be returned, else it returns the false value.
you can acheive the same with a case statement:
declare @test-2 varchar(max)
SELECT @test-2 = CASE WHEN (select count(*)
FROM MCommOpenOrders) > 1 THEN 'more than 1'
WHEN (select count(*)
from MCommOpenOrders) < 1 THEN 'less than 1'
ELSE 'equals 1'
END
SELECT @test-2
October 30, 2007 at 11:54 am
A variable can only be declared once within a batch or in a loop. IF...ELSE is a branching statement whereas IIF not a valid statement in SQL but in VB it's the same as IF (Condition) THEN (do this) ELSE (do this).
Regarding the error, I want to point out few things. You are checking the no. of rows in a table multiple times which you do it in just one step using variable...
declare @rows int
declare @test-2 varchar(max)
select @rows = count(*) from MCommOpenOrders
if (@rows > 1 )
Begin
set @test-2 = 'more than 1'
select 'test'
end
else
if (@rows < 1 )
begin
set @test-2 = 'less than 1'
end
print @test-2
--Ramesh
October 30, 2007 at 1:02 pm
Marcus Farrugia (10/30/2007)
What is the difference between if and iif?Thank you in advance for your help.
IIF is an MDX expression used to build cubes. It corresponds to the immediate if statements used in other languages.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply