May 13, 2009 at 6:25 am
Hi,
I have a probleme with the statement "if/else"
In my stored procedure
I had written this statement:
________________________________________________________
ALTER PROCEDURE [dbo].[sp_construction_tb_diag2_TER_test]
AS
BEGIN
-- Déclaration de deux variables
DECLARE @Annee int, @mois int
-- Enrichissement des deux variables avec les
-- données 'date_ref' du fichier importé
select distinct @Annee=2007 ,@mois=12
FROM tb_import_diag
Update tb_diag
set
HDG=
CASE WHEN (MTDOMHEX < 91469 AND M1COLLEC >= 152449 AND M1COLLEC<300000) THEN 3
WHEN (MTDOMHEX >= 91469 AND MTDOMHEX<= 150000 ) OR
(M1COLLEC <= 1000000 AND M1COLLEC >= 300000 ) THEN 2
WHEN (MTDOMHEX > 150000 ) OR
(M1COLLEC > 1000000 ) THEN 1
ELSE 4
end
where YEAR(DATE_REF)=@annee AND MONTH(DATE_REF)=@mois
_________________________________________________________
But it does'nt give me the right result this is why i want to code this statement with if/else :
But the problem that SQL server it doesn't know on which table I study and it can't find the column names
This is my first probleme. Is there anyone who can help me please??
May 13, 2009 at 6:33 am
nothing wrong with the syntax of your case statement
One thing to note though, a Case Statement will stop as soon as it hits a condition which is true, so make sure you have the order of cases correct.
One other point, when you set the year and month variables in the select statement.
You don't need to use a table
select @year = 2007, @month=12
will suffice
May 13, 2009 at 6:42 am
I'm not sure with case statement if sql server compiler reads the code line by line. Because when i change the order of the case it gives me the same result. And for some value of my attributs ,it can be true in more than 1 case.
So I want to use if/else statement:(
May 13, 2009 at 6:50 am
if this is true
And for some value of my attributs ,it can be true in more than 1 case.
Then this cannot be the case
when i change the order of the case it gives me the same result.
Try the following example:
select
case
when col1 = 'a' then 'a'
when col1 = 'b' then 'b'
when col2 = 2 then '2'
end
from (
select 'a' as col1, 2 as col2
union all
select 'b' as col1, 1 as col2
union all
select 'c' as col1, 3 as col2) T1
Run it once, have a look at the output then put the third case condition to first place and run again
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply