November 19, 2013 at 2:01 am
I have a select statement in my existing SP, which is not working:
select IsNull(St.alternateStudyCode, St.studyCode) AS StudyCode,* from Studies St
I hope it is not working because, alternateStudyCode and studyCode are nvarchar columns and ISNULL cannot be used on those.
Any solution on this?
November 19, 2013 at 2:17 am
IsNull can be used on NVarchar columns, it can be used on any data type. It works fine on nvarchar
DECLARE @SomeValue nvarchar(4000);
SELECT isnull(@SomeValue,'The variable is null');
What exactly is 'not working'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2013 at 2:20 am
Junglee_George (11/19/2013)
I have a select statement in my existing SP, which is not working:
select IsNull(St.alternateStudyCode, St.studyCode) AS StudyCode,* from Studies St
I hope it is not working because, alternateStudyCode and studyCode are nvarchar columns and ISNULL cannot be used on those.
Any solution on this?
What are you expecting to happen?
Would you be better looking at COALESCE; is this what you are trying to do?
select COALESCE(St.alternateStudyCode, St.studyCode, '') AS StudyCode,* from Studies St
November 19, 2013 at 2:22 am
By not working, I assume that you get null value from the isnull function. Most chances are that you have some records that both columns have null values. You can check it with this statement:
Select *
from Studies
where alternateStudyCode is null and studyCode is null
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 19, 2013 at 2:28 am
Ok..Thanks Gila for the information.
But If I change the code this way, it is not showing the message:
DECLARE @SomeValue nvarchar(4000) = ' '
SELECT isnull(@SomeValue,'The variable is null');
ie. When the record of the column is blank. How can I deal this?
November 19, 2013 at 2:34 am
Junglee_George (11/19/2013)
Ok..Thanks Gila for the information.But If I change the code this way, it is not showing the message:
DECLARE @SomeValue nvarchar(4000) = ' '
SELECT isnull(@SomeValue,'The variable is null');
Well, no, it wouldn't. ISNULL checks for NULL values, not empty strings. It's not expected to show the message in the above case because the variable is not null.
ie. When the record of the column is blank. How can I deal this?
Couple options. You can combine NULLIF, which returns null if two expressions have the same value or you can use a case statement to check for empty string or null.
DECLARE @SomeValue nvarchar(4000) = ' '
SELECT ISNULL(NULLIF(@SomeValue,''),'The variable is null or empty string');
SELECT CASE WHEN @SomeValue IS NULL OR @SomeValue = '' THEN 'The variable is null or empty string' ELSE @SomeValue END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2013 at 3:01 am
Yes, Gila, That's what I did. I took the second option.
select case when St.alternateStudyCode = '' OR St.alternateStudyCode IS NULL then St.studyCode END AS StudyCode,* from Studies St
Thank You all for the posts.
November 21, 2013 at 6:57 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply