November 1, 2018 at 12:05 pm
hello, I want to validate multiple fields and create a string listing the fields that are null or blank. can someone help me understand how to do this?
Here is my sample code:
-- DROP TABLE #t
CREATE TABLE #t (TID int IDENTITY (1,1), Name varchar(100), SubmitDt datetime, ActionDt datetime, JobCodeID int, DeptID int)
INSERT INTO #t (Name, SubmitDt, ActionDt, JobCodeID, DeptID) VALUES ('Bob', '11/01/2018', NULL, NULL, 1)
SELECT * FROM #t
-- I think what I am trying to do is wrong, or could be done better.
SELECT
ValName = CASE WHEN Name = NULL OR Name = '' THEN 'Name' ELSE Name END,
ValActionDt = CASE WHEN ActionDt = NULL OR ActionDt = '' THEN 'ActionDt' ELSE ActionDt END,
ValJobCodeID = CASE WHEN JobCodeID = NULL OR JobCodeID = '' THEN 'JobCodeID' ELSE JobCodeID END,
ValDeptID = CASE WHEN DeptID = NULL OR DeptID = '' THEN 'DeptID' ELSE DeptID END
FROM #t
-- Here is my desired result
-- Message: ValActionDt, ValJobCodeID cannot be NULL or Blank.
November 1, 2018 at 12:08 pm
And why not just use constraints to prevent entering invalid data such as nulls or blanks?
November 1, 2018 at 12:15 pm
Try some type of concatenation, like this:
SELECT
Message = NULLIF(STUFF(
CASE WHEN Name IS NULL OR Name = '' THEN ', Name' ELSE '' END +
CASE WHEN ActionDt IS NULL OR ActionDt = '' THEN ', ActionDt' ELSE '' END +
CASE WHEN JobCodeID IS NULL OR JobCodeID = '' THEN ', JobCodeID' ELSE '' END +
CASE WHEN DeptID IS NULL OR DeptID = '' THEN ', DeptID' ELSE '' END,
1, 2, ''), '') + ' cannot be NULL or Blank.'
FROM #t
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 1, 2018 at 12:16 pm
rjjh78 - Thursday, November 1, 2018 12:05 PMhello, I want to validate multiple fields and create a string listing the fields that are null or blank. can someone help me understand how to do this?
Here is my sample code:
-- DROP TABLE #t
CREATE TABLE #t (TID int IDENTITY (1,1), Name varchar(100), SubmitDt datetime, ActionDt datetime, JobCodeID int, DeptID int)
INSERT INTO #t (Name, SubmitDt, ActionDt, JobCodeID, DeptID) VALUES ('Bob', '11/01/2018', NULL, NULL, 1)
SELECT * FROM #t
-- I think what I am trying to do is wrong, or could be done better.
SELECT
ValName = CASE WHEN Name = NULL OR Name = '' THEN 'Name' ELSE Name END,
ValActionDt = CASE WHEN ActionDt = NULL OR ActionDt = '' THEN 'ActionDt' ELSE ActionDt END,
ValJobCodeID = CASE WHEN JobCodeID = NULL OR JobCodeID = '' THEN 'JobCodeID' ELSE JobCodeID END,
ValDeptID = CASE WHEN DeptID = NULL OR DeptID = '' THEN 'DeptID' ELSE DeptID END
FROM #t-- Here is my desired result
-- Message: ValActionDt, ValJobCodeID cannot be NULL or Blank.
You're using the wrong test for NULL values. You should be testing WHEN <field> IS NULL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 1, 2018 at 1:36 pm
drew.allen - Thursday, November 1, 2018 12:16 PMrjjh78 - Thursday, November 1, 2018 12:05 PMhello, I want to validate multiple fields and create a string listing the fields that are null or blank. can someone help me understand how to do this?
Here is my sample code:
-- DROP TABLE #t
CREATE TABLE #t (TID int IDENTITY (1,1), Name varchar(100), SubmitDt datetime, ActionDt datetime, JobCodeID int, DeptID int)
INSERT INTO #t (Name, SubmitDt, ActionDt, JobCodeID, DeptID) VALUES ('Bob', '11/01/2018', NULL, NULL, 1)
SELECT * FROM #t
-- I think what I am trying to do is wrong, or could be done better.
SELECT
ValName = CASE WHEN Name = NULL OR Name = '' THEN 'Name' ELSE Name END,
ValActionDt = CASE WHEN ActionDt = NULL OR ActionDt = '' THEN 'ActionDt' ELSE ActionDt END,
ValJobCodeID = CASE WHEN JobCodeID = NULL OR JobCodeID = '' THEN 'JobCodeID' ELSE JobCodeID END,
ValDeptID = CASE WHEN DeptID = NULL OR DeptID = '' THEN 'DeptID' ELSE DeptID END
FROM #t-- Here is my desired result
-- Message: ValActionDt, ValJobCodeID cannot be NULL or Blank.You're using the wrong test for NULL values. You should be testing WHEN <field> IS NULL.
Drew
If the OP has set ANSI_NULLS OFF then they can use '='
November 1, 2018 at 3:15 pm
Thank you all!
November 2, 2018 at 12:41 pm
Jonathan AC Roberts - Thursday, November 1, 2018 1:36 PMdrew.allen - Thursday, November 1, 2018 12:16 PMrjjh78 - Thursday, November 1, 2018 12:05 PMhello, I want to validate multiple fields and create a string listing the fields that are null or blank. can someone help me understand how to do this?
Here is my sample code:
-- DROP TABLE #t
CREATE TABLE #t (TID int IDENTITY (1,1), Name varchar(100), SubmitDt datetime, ActionDt datetime, JobCodeID int, DeptID int)
INSERT INTO #t (Name, SubmitDt, ActionDt, JobCodeID, DeptID) VALUES ('Bob', '11/01/2018', NULL, NULL, 1)
SELECT * FROM #t
-- I think what I am trying to do is wrong, or could be done better.
SELECT
ValName = CASE WHEN Name = NULL OR Name = '' THEN 'Name' ELSE Name END,
ValActionDt = CASE WHEN ActionDt = NULL OR ActionDt = '' THEN 'ActionDt' ELSE ActionDt END,
ValJobCodeID = CASE WHEN JobCodeID = NULL OR JobCodeID = '' THEN 'JobCodeID' ELSE JobCodeID END,
ValDeptID = CASE WHEN DeptID = NULL OR DeptID = '' THEN 'DeptID' ELSE DeptID END
FROM #t-- Here is my desired result
-- Message: ValActionDt, ValJobCodeID cannot be NULL or Blank.You're using the wrong test for NULL values. You should be testing WHEN <field> IS NULL.
Drew
If the OP has set ANSI_NULLS OFF then they can use '='
There is simply no good reason to set ANSI_NULLS OFF. This has been deprecated and should not be used.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply