if else statement inside a where clause

[clear1140]

Good day!!

I was wondering if it is possible to put an if else statement inside a where clause of an sql statement... you see i would like my update statement to do this:


update trial_clear set num = @count2 /* @count2 is a integer passed*/
where
if (select top 1 def from trial_clear where num is NULL) is NULL
def is NULL
else
def = @def /*given value*/

please do help me... i would appreciate it much!!

[iburyak]

Try this:

update trial_clear set num = @count2 /* @count2 is a integer passed*/
where def =
case when (select top 1 def from trial_clear where num is NULL) is NULL Then null
else @def  end

Good Luck.

[clear1140]

Try this:

update trial_clear set num = @count2 /* @count2 is a integer passed*/
where def =
case when (select top 1 def from trial_clear where num is NULL) is NULL Then null
else @def  end

Good Luck.

thanks for the code but still it doesn't work, i should have made my message clear... you see i've got this table..
--table---
trial_clear(num ,def,def2,def3)
--these are the existing values--
1st row (NULL,q,NULL,NU LL)
2nd row (NULL,w,w,NULL)
3rd row (NULL,e,e,r)
4th row (t,NULL,y)
5th row (q,w,NULL)

you see what i was supposed to do is update this table, assigning the num value with int.. starting form 1 to so on...the table i presented is just a prototype of the real table

while making my procedure i noticed that it will only work if i would say 'def is NULL' not def = NULL and that was why i was wondering if there was and if else statement inside the where clause

[iburyak]

Try this:

set rowcount 1

update trial_clear set num = @count2 /* @count2 is a integer passed*/
where isnull(def, '') = ''

[clear1140]

Try this:

set rowcount 1

update trial_clear set num = @count2 /* @count2 is a integer passed*/
where isnull(def, '') = ''

thanks it really helped!!

But i too got another solution. I simply put the resulting rows into a cursor and it works even better than my previous code. Thanks for reminding me that there is a better way... THANK YOU!!!

[ck9663]

thanks it really helped!!

But i too got another solution. I simply put the resulting rows into a cursor and it works even better than my previous code. Thanks for reminding me that there is a better way... THANK YOU!!!

cursor should be your last resort...