Need help - SQL Server String Concatenation with Null

Question

Need help - SQL Server String Concatenation with Null

Qira

SSCommitted

Points: 1930
More actions
August 21, 2014 at 8:46 am

#397235

I've table and data as following,
CREATE TABLE [dbo].[t_A1](
[idx] [int] NULL,
[batch_Id] [uniqueidentifier] NULL,
[JobNoticeID] [int] NULL,
[LevelID] [int] NULL,
[StudyFieldID] [int] NULL,
[min_CGPA] [int] NULL,
[max_CGPA] [int] NULL,
[Description] [nvarchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[t_A1] ([idx], [batch_Id], [JobNoticeID], [LevelID], [StudyFieldID], [min_CGPA], [max_CGPA], [Description]) VALUES (-2147483635, N'df4771d6-84d3-4958-940d-aa54aa56cb0b', 76, 2, 36, 2, 4, N'OR')
INSERT [dbo].[t_A1] ([idx], [batch_Id], [JobNoticeID], [LevelID], [StudyFieldID], [min_CGPA], [max_CGPA], [Description]) VALUES (-2147483634, N'df4771d6-84d3-4958-940d-aa54aa56cb0b', 76, 1, 4, 2, 4, N'AND')
INSERT [dbo].[t_A1] ([idx], [batch_Id], [JobNoticeID], [LevelID], [StudyFieldID], [min_CGPA], [max_CGPA], [Description]) VALUES (-2147483591, N'df4771d6-84d3-4958-940d-aa54aa56cb0b', 76, 11, NULL, NULL, NULL, N'[END]')
My SQL as follow,
select idx,
'[LevelID]=' + Convert(varchar(20),LevelID) + ' AND [StudyField]='
+ Convert(varchar(20),StudyFieldID) + ' AND [CGPA] >='
+ Convert(varchar(20),min_CGPA) + ' AND [CGPA] <='
+ Convert(varchar(20),min_CGPA) + ' ' + Description as Expression
from t_A1
This is my result
idxExpression
-2147483635[LevelID]=2 AND [StudyField]=36 AND [CGPA] >=2 AND [CGPA] <=2 OR
-2147483634[LevelID]=1 AND [StudyField]=4 AND [CGPA] >=2 AND [CGPA] <=2 AND
-2147483591NULL
It's suppose to be,
idxExpression
-2147483635[LevelID]=2 AND [StudyField]=36 AND [CGPA] >=2 AND [CGPA] <=2 OR
-2147483634[LevelID]=1 AND [StudyField]=4 AND [CGPA] >=2 AND [CGPA] <=2 AND
-2147483591[LevelID]=11 AND [StudyField]=NULL AND [CGPA] >=NULL AND [CGPA] <=NULL [END]
How to fix my SQL? Please help

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

If you concatenate anything to NULL, it will return NULL. The same way as if you try to do an arithmetic operation with NULL.

You can validate for nulls.

select idx,

'[LevelID]=' + ISNULL( Convert(varchar(20),LevelID), 'NULL') + ' AND [StudyField]'

+ ISNULL( Convert(varchar(20),StudyFieldID), 'NULL') + ' AND [CGPA] >='

+ ISNULL( Convert(varchar(20),min_CGPA), 'NULL') + ' AND [CGPA] <='

+ ISNULL( Convert(varchar(20),min_CGPA), 'NULL') + ' ' + Description as Expression

from t_A1

Remember that nothing is equal to NULL, so you can go a little further:

select idx,

'[LevelID]' + ISNULL( ' = ' + Convert(varchar(20),LevelID), ' IS NULL') + ' AND [StudyField]='

+ ISNULL( ' = ' + Convert(varchar(20),StudyFieldID), ' IS NULL') + ' AND [CGPA] '

+ ISNULL( ' >= ' + Convert(varchar(20),min_CGPA), ' IS NULL') + ' AND [CGPA] '

+ ISNULL( ' <= ' + Convert(varchar(20),min_CGPA), ' IS NULL') + ' ' + Description as Expression

from t_A1

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2