How to set NULL here - 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

SQL Server 2008 Forums

Transact-SQL (2008)

How to set NULL here

Author

Topic

prashantdighe
Starting Member

21 Posts

Posted - 2012-05-30 : 02:55:58

Hi friends,

I have below query....

set @val=(select top 1 Session_ID from Program_rate where PS_No =(select ps_no from #temp where id=@COUNT) and Program_Name=@P_Name and convert(varchar,User_Date,103)=convert(varchar,@sd,103))

It set @val Session_ID if it is in DB.

But if it does not exits then it set noting in @val.

I want if it does not exits then it set NULL in @val.
Please give suggenstion.

Thank you in advance....

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-30 : 03:28:15

set it to null before the query

select @val = null
set @val=(select top 1 Session_ID from Program_rate where PS_No =(select ps_no from #temp where id=@COUNT) and Program_Name=@P_Name and convert(varchar,User_Date,103)=convert(varchar,@sd,103))

You could also code that statement as
select top 1 @val= Session_ID
from Program_rate pr
join #temp t on pr.PS_No = t.ps_no and t.id=@COUNT
where pr.Program_Name=@P_Name
and convert(varchar,pr.User_Date,103)=convert(varchar,@sd,103)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

prashantdighe
Starting Member

21 Posts

Posted - 2012-05-30 : 03:35:12

You are correct but unfortunately I already tried it and it was not working!!!!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-30 : 03:40:21

What are you getting as a value? I would have thought your query would end up with null. If you are setting it to null before the query and it ends up with a value then your query must return something.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

prashantdighe
Starting Member

21 Posts

Posted - 2012-05-30 : 03:42:26

Yes of course!!!!
It return nothing so it replace NULL to ''.
But I want it remain NULL if there is no value......

prashantdighe
Starting Member

21 Posts

Posted - 2012-05-30 : 03:57:31

Please give suggenstion friends it urgent to solve!!!!!!!!!!!

prashantdighe
Starting Member

21 Posts

Posted - 2012-05-30 : 04:14:31

Could anyone solve my problem?????

When life gives you a hundred reasons to cry, show life that you have a thousand reasons to smile.

prashantdighe
Starting Member

21 Posts

Posted - 2012-05-30 : 05:13:46

Please do something friends!!!!!!!!!!!!!!!!

================================================
When life gives you a hundred reasons to cry,
show life that you have a thousand reasons to smile.

mani_12345
Starting Member

35 Posts

Posted - 2012-05-30 : 07:13:18

use
isnull(@var,'Null') : when ever there is no data in @var then 'Null 'is set

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-30 : 07:26:13

>> It return nothing so it replace NULL to ''.
No it should set the value to null rather than ''.

The null you are talking about - is it a null or the text 'NULL'? They are very different things.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources