Help with a simple stored procedure (check if nvarchar is empty)
-
Hello all!
I'm trying to create a stored procedure that needs to return only persons that do not have one or more of these fields null or empty.
Here you can see the 3 tables that will be involved in the query:
Persons - This table contains persons, and it has a PersonID field, this field is a foreign key in all the tables in this solution
Passport - This table has 2 of the columns I need to check if it has values or not
Tax - This table has 1 field I also need to check if it has a value or not
The values I need to check is:
Passport.ExpiryDate - DateTime - This will be NULL if no value is available
Passport.DuffNr - Nvarchar(50) - This will be empty (not null) if no value is available
Tax.Dnr - Nvarchar(50) - This will be empty (not null) if no value is available
So, I need to create a stored procedure that will return * from persons that have one or more of these fields null or empty.
Can someone please kick me in the right direction? I have all the code for querying and receiving the query ready, it's only the sql part I'm not sure on..
-
An empty string check is simply STRING=''
A Null check is STRING is NULL
But to check for either you can use ISNULL(STRING,'')=''
From your description something along these lines may work.
CREATE PROCEDURE dbo.ProcedureNameASSELECT P.*FROM Persons AS PJOIN Passport AS PP ON P.PersonID = PP.fk_PersonIDJOIN Tax AS T ON P.PersonID = T.fk_PersonIDWHERE PP.ExpiryDate is NULLOR PP.DuffNr = ''OR T.Dnr = ''But - will every person have a passport record AND a tax record? e.g if someone has no tax record, do you want to count it as an empty Tax.Dnr, or ignore it completely.
-
The thing is, that it might be persons that still have no Passport or Tax records. How can I handle that in the sql query?
-
Well what output is required if a person has no passport record, or no tax record. - Do you include them in the results - i.e. treat it the same as if they had a record with a null date, or empty nvarchar ? or not?
-
The background for this query is: I have a gridview that is going to show persons that lack one or more of these 3 fields. This is because many persons that get created, is not "finished" until 1-2 weeks later. Then I have to be able to check this gridview, and all the persons lacking one of these fields will appear.
So, I only need the data from the persons table as output, no need for anything else.
I'm sorry if it is bad explained, do you understand?
-
Actually, it's working as you wrote it, as long as the records are made in the 2 tables (so that the foreign key is in place in a row), passport and tax. But I also need to get them in the resultset if they have not yet been created in these tables. I have no idea on how to check for this..
-
I made it!
I just had to add LEFT OUTER JOIN instead of only JOIN.
Thank you very much for your help Tom, very nice of you!
-
Good!
I nearly suggested the OUTER JOIN, but instead I ask questions to make you think.
If you realise if yourself, the knowledge sticks better.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply