Check if all fields are not empty

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 2000 Forums

SQL Server Development (2000)

Check if all fields are not empty

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-04-28 : 06:26:33

Hello,

I want to know if a person has filled in alle fields in a table.
Because if he logins in I want to show him the options he did not filled in yet.

SELECT *
FROM tblpersoonlijk
WHERE idHaar = null (or = "")

This wont work. How can I check it?

Thanxx
Bjorn

Just let me know if you need more info!

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-04-28 : 06:42:39

Found it!!

SELECT *
FROM tblpersoonlijk
WHERE idHaar IS NULL

Cya
Bjorn

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-04-28 : 06:44:36

Oke one question,

I have 16 fields to check so I get AND idFace IS NULL AND idNose IS NULL AND idEyes IS NULL etc..
Is there maby a bether solution?

Thanxx
Bjorn

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-28 : 06:49:52

That will only tell you if all fields are null, not if a particular field is null...

Shouldn't you be checking this on the front end?

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-04-28 : 07:01:47

No Its like this:

The can enter his info, but its not required. But when the login again, I want to let them now "He look here, you did not enter all info". So If he login I want to check if there are still empty fields in the database.

Cya
Bjorn

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-28 : 07:33:41

I think you are still better off doing this in the front end... Do a select and then check each field in the recordset... If you try to do this in a select statement, how will you know which fields are null when it is returned to the application?

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-04-28 : 07:47:29

Oke thats also possible, I will check whats faster.

Thanxx
Bjorn

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-28 : 07:53:40

Ok, just to point out once more, a is null and b is null and .... will only give you records where all the fields are null...

Using OR will do the same kind of thing as it is a logical AND when used like this...

neutcomp
Posting Yak Master

111 Posts

Posted - 2004-04-28 : 08:08:59

I only want to know yes or no if the record is completly filled in where userId = 1
I dont want to know what field is empty.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-04-28 : 10:49:58

You could do it with a case statement

Declare @userid Int
Select @userid = 1

Select
Fieldmissing = Case when ((idFace Is null) OR (idFace = ‘ ‘))
Then 1
when ((ideyes Is null) OR (ideyes = ‘ ‘))
Then 1
when ((idnose Is null) OR (idnose = ‘ ‘))
Then 1
Else 0
End
FROM tblpersoonlijk
Where userId = @userid

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-28 : 13:11:24

I would too say this better handled in the application layer...

And I would think you'd want to know which control to give focus...


USE Northwind
GO

CREATE TABLE myTable99(Col1 char(10), Col2 char(10), Col3 char(10))
GO

INSERT INTO myTable99(Col2,Col1)
SELECT 'B','C'
GO

SELECT MIN(Ind) AS First_Missed 
FROM (
SELECT CASE WHEN Col1 IS NULL OR Col1 = '' OR Col1 = ' ' 
	    THEN 1 ELSE 0 END AS Ind 
  FROM myTable99
UNION ALL
SELECT CASE WHEN Col2 IS NULL OR Col2 = '' OR Col2 = ' ' 
	    THEN 2 ELSE 0 END AS Ind 
  FROM myTable99
UNION ALL
SELECT CASE WHEN Col3 IS NULL OR Col3 = '' OR Col3 = ' ' 
	    THEN 3 ELSE 0 END AS Ind 
  FROM myTable99
) AS XXX
WHERE Ind <> 0
GO

DROP TABLE myTable99
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources