Does “is NULL” in Where Clause Behave In A Special Way?
up vote
1
down vote
favorite
I am trying to debug a stored procedure that I did not write. Specifically focused on the third line beginning with AND (Citation_Main.User_Defined5, if I set a subset of records' User_Defined5 column to NULL, using an update query, then the stored procedure writes an export file that I am expecting. However, if the User_Defined5 field is set to 'N', a file is not written.
DECLARE Cit_Select_Cursor CURSOR FOR SELECT Citation_Main.Number,Citation_Main.Issue_Date,
Citation_Main.Issue_Time,Citation_Main.Officer_Comment1, Citation_Main.Officer_ID,
Citation_Main.Officer_Name,Citation_Main.License_Plate,Citation_Main.License_State,
Citation_Main.Location_Block,Citation_Main.Location_Street,Citation_Main.Location_Dir,
Citation_Main.Location_Suffix,Citation_Main.Amount_Due,Citation_Main.Meter,
Citation_Main.Make,Citation_Main.Color_Major,Citation_Main.Body_Style,
Citation_Main.License_Type,Citation_Main.VIN,Citation_Activity.ChargeCode,Citation_Activity.DebitAmount
,Citation_Main.Officer_Comment2
FROM Citation_Main --TTM_Bak
--ADDED 03/12/2016 Inner Join and check for void and warning
-- INNER JOIN Citation_Main On TTM_Bak.Number = Citation_Main.Number
INNER JOIN Citation_Activity On Citation_Main.Citation_Key = Citation_Activity.Citation_Key
Where --ExportViaDAT = 'N' And
Citation_Main.Void ='N' And Citation_Main.Warning ='N'
AND (Citation_Main.User_Defined5 ='N' or Citation_Main.User_Defined5 ='' or Citation_Main.User_Defined5 is NULL)
ORDER BY Citation_Main.Issue_Date DESC
I have been reading the suggested SO posts that appeared as I was entering this post, but could not see anything wrong with this part of the stored procedure.
My question is, is there something inherently wrong with checking for NULL in this part of the stored procedure?
sql
sql-server tsql stored-procedures isnull asked Nov 16 at 15:31
octopusgrabbus
6,517104297
|
show 3 more comments
up vote
1
down vote
favorite
I am trying to debug a stored procedure that I did not write. Specifically focused on the third line beginning with AND (Citation_Main.User_Defined5, if I set a subset of records' User_Defined5 column to NULL, using an update query, then the stored procedure writes an export file that I am expecting. However, if the User_Defined5 field is set to 'N', a file is not written.
DECLARE Cit_Select_Cursor CURSOR FOR SELECT Citation_Main.Number,Citation_Main.Issue_Date,
Citation_Main.Issue_Time,Citation_Main.Officer_Comment1, Citation_Main.Officer_ID,
Citation_Main.Officer_Name,Citation_Main.License_Plate,Citation_Main.License_State,
Citation_Main.Location_Block,Citation_Main.Location_Street,Citation_Main.Location_Dir,
Citation_Main.Location_Suffix,Citation_Main.Amount_Due,Citation_Main.Meter,
Citation_Main.Make,Citation_Main.Color_Major,Citation_Main.Body_Style,
Citation_Main.License_Type,Citation_Main.VIN,Citation_Activity.ChargeCode,Citation_Activity.DebitAmount
,Citation_Main.Officer_Comment2
FROM Citation_Main --TTM_Bak
--ADDED 03/12/2016 Inner Join and check for void and warning
-- INNER JOIN Citation_Main On TTM_Bak.Number = Citation_Main.Number
INNER JOIN Citation_Activity On Citation_Main.Citation_Key = Citation_Activity.Citation_Key
Where --ExportViaDAT = 'N' And
Citation_Main.Void ='N' And Citation_Main.Warning ='N'
AND (Citation_Main.User_Defined5 ='N' or Citation_Main.User_Defined5 ='' or Citation_Main.User_Defined5 is NULL)
ORDER BY Citation_Main.Issue_Date DESC
I have been reading the suggested SO posts that appeared as I was entering this post, but could not see anything wrong with this part of the stored procedure.
My question is, is there something inherently wrong with checking for NULL in this part of the stored procedure?
sql
sql-server tsql stored-procedures isnull asked Nov 16 at 15:31
octopusgrabbus
6,517104297
If I addselect * from (values ('N','N','N',GETDATE())) Citation_Main (Void, Warning, User_Defined5,Issue_Date)above what you've shown to make it a complete query, it returns a row. We can't reproduce what you're seeing just from this fragment.
– Damien_The_Unbeliever
Nov 16 at 15:35
Nothing wrong with checking it if you want to update rows where it's N, '', or NULL. If the file isn't written when the predicate = 'N' then another part of your where clause is preventing this, like the Void or Warning
– scsimon
Nov 16 at 15:35
1
Or with thatjointoCitation_Activityexcluding such rows. But you're the only one who can look at your data, unless or until you add some sample data to your question also that demonstrates the issue.
– Damien_The_Unbeliever
Nov 16 at 15:47
2
This is trivially easy to debug. Take the cursor declaration and copy it to a query window. Remove the cursor declare part and just make it a single select statement. Go find a PK value from citation_main that you know should be included in the resultset and add that value to the where clause as a condition. Then comment out the user_defined5 logic in the where clause and add that column to the resultset. What do you see?
– SMor
Nov 16 at 15:56
1
Are you using a case-sensitive collation? To find out, see here? If so, make sure the column contains a capital N in your testing.
– BoCoKeith
Nov 17 at 1:22
|
show 3 more comments
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I am trying to debug a stored procedure that I did not write. Specifically focused on the third line beginning with AND (Citation_Main.User_Defined5, if I set a subset of records' User_Defined5 column to NULL, using an update query, then the stored procedure writes an export file that I am expecting. However, if the User_Defined5 field is set to 'N', a file is not written.
DECLARE Cit_Select_Cursor CURSOR FOR SELECT Citation_Main.Number,Citation_Main.Issue_Date,
Citation_Main.Issue_Time,Citation_Main.Officer_Comment1, Citation_Main.Officer_ID,
Citation_Main.Officer_Name,Citation_Main.License_Plate,Citation_Main.License_State,
Citation_Main.Location_Block,Citation_Main.Location_Street,Citation_Main.Location_Dir,
Citation_Main.Location_Suffix,Citation_Main.Amount_Due,Citation_Main.Meter,
Citation_Main.Make,Citation_Main.Color_Major,Citation_Main.Body_Style,
Citation_Main.License_Type,Citation_Main.VIN,Citation_Activity.ChargeCode,Citation_Activity.DebitAmount
,Citation_Main.Officer_Comment2
FROM Citation_Main --TTM_Bak
--ADDED 03/12/2016 Inner Join and check for void and warning
-- INNER JOIN Citation_Main On TTM_Bak.Number = Citation_Main.Number
INNER JOIN Citation_Activity On Citation_Main.Citation_Key = Citation_Activity.Citation_Key
Where --ExportViaDAT = 'N' And
Citation_Main.Void ='N' And Citation_Main.Warning ='N'
AND (Citation_Main.User_Defined5 ='N' or Citation_Main.User_Defined5 ='' or Citation_Main.User_Defined5 is NULL)
ORDER BY Citation_Main.Issue_Date DESC
I have been reading the suggested SO posts that appeared as I was entering this post, but could not see anything wrong with this part of the stored procedure.
My question is, is there something inherently wrong with checking for NULL in this part of the stored procedure?
sql
sql-server tsql stored-procedures isnull asked Nov 16 at 15:31
octopusgrabbus
6,517104297
I am trying to debug a stored procedure that I did not write. Specifically focused on the third line beginning with AND (Citation_Main.User_Defined5, if I set a subset of records' User_Defined5 column to NULL, using an update query, then the stored procedure writes an export file that I am expecting. However, if the User_Defined5 field is set to 'N', a file is not written.
DECLARE Cit_Select_Cursor CURSOR FOR SELECT Citation_Main.Number,Citation_Main.Issue_Date,
Citation_Main.Issue_Time,Citation_Main.Officer_Comment1, Citation_Main.Officer_ID,
Citation_Main.Officer_Name,Citation_Main.License_Plate,Citation_Main.License_State,
Citation_Main.Location_Block,Citation_Main.Location_Street,Citation_Main.Location_Dir,
Citation_Main.Location_Suffix,Citation_Main.Amount_Due,Citation_Main.Meter,
Citation_Main.Make,Citation_Main.Color_Major,Citation_Main.Body_Style,
Citation_Main.License_Type,Citation_Main.VIN,Citation_Activity.ChargeCode,Citation_Activity.DebitAmount
,Citation_Main.Officer_Comment2
FROM Citation_Main --TTM_Bak
--ADDED 03/12/2016 Inner Join and check for void and warning
-- INNER JOIN Citation_Main On TTM_Bak.Number = Citation_Main.Number
INNER JOIN Citation_Activity On Citation_Main.Citation_Key = Citation_Activity.Citation_Key
Where --ExportViaDAT = 'N' And
Citation_Main.Void ='N' And Citation_Main.Warning ='N'
AND (Citation_Main.User_Defined5 ='N' or Citation_Main.User_Defined5 ='' or Citation_Main.User_Defined5 is NULL)
ORDER BY Citation_Main.Issue_Date DESC
I have been reading the suggested SO posts that appeared as I was entering this post, but could not see anything wrong with this part of the stored procedure.
My question is, is there something inherently wrong with checking for NULL in this part of the stored procedure?
sql
sql-server tsql stored-procedures isnull sql
sql-server tsql stored-procedures isnull asked Nov 16 at 15:31
octopusgrabbus
6,517104297
asked Nov 16 at 15:31
octopusgrabbus
6,517104297
edited Nov 19 at 23:03
asked Nov 16 at 15:31
octopusgrabbus
6,517104297
asked Nov 16 at 15:31
octopusgrabbus
6,517104297
asked Nov 16 at 15:31
octopusgrabbus
6,517104297
6,517104297
If I addselect * from (values ('N','N','N',GETDATE())) Citation_Main (Void, Warning, User_Defined5,Issue_Date)above what you've shown to make it a complete query, it returns a row. We can't reproduce what you're seeing just from this fragment.
– Damien_The_Unbeliever
Nov 16 at 15:35
Nothing wrong with checking it if you want to update rows where it's N, '', or NULL. If the file isn't written when the predicate = 'N' then another part of your where clause is preventing this, like the Void or Warning
– scsimon
Nov 16 at 15:35
1
Or with thatjointoCitation_Activityexcluding such rows. But you're the only one who can look at your data, unless or until you add some sample data to your question also that demonstrates the issue.
– Damien_The_Unbeliever
Nov 16 at 15:47
2
This is trivially easy to debug. Take the cursor declaration and copy it to a query window. Remove the cursor declare part and just make it a single select statement. Go find a PK value from citation_main that you know should be included in the resultset and add that value to the where clause as a condition. Then comment out the user_defined5 logic in the where clause and add that column to the resultset. What do you see?
– SMor
Nov 16 at 15:56
1
Are you using a case-sensitive collation? To find out, see here? If so, make sure the column contains a capital N in your testing.
– BoCoKeith
Nov 17 at 1:22
|
show 3 more comments
If I addselect * from (values ('N','N','N',GETDATE())) Citation_Main (Void, Warning, User_Defined5,Issue_Date)above what you've shown to make it a complete query, it returns a row. We can't reproduce what you're seeing just from this fragment.
– Damien_The_Unbeliever
Nov 16 at 15:35
Nothing wrong with checking it if you want to update rows where it's N, '', or NULL. If the file isn't written when the predicate = 'N' then another part of your where clause is preventing this, like the Void or Warning
– scsimon
Nov 16 at 15:35
1
Or with thatjointoCitation_Activityexcluding such rows. But you're the only one who can look at your data, unless or until you add some sample data to your question also that demonstrates the issue.
– Damien_The_Unbeliever
Nov 16 at 15:47
2
This is trivially easy to debug. Take the cursor declaration and copy it to a query window. Remove the cursor declare part and just make it a single select statement. Go find a PK value from citation_main that you know should be included in the resultset and add that value to the where clause as a condition. Then comment out the user_defined5 logic in the where clause and add that column to the resultset. What do you see?
– SMor
Nov 16 at 15:56
1
Are you using a case-sensitive collation? To find out, see here? If so, make sure the column contains a capital N in your testing.
– BoCoKeith
Nov 17 at 1:22
If I add
select * from (values ('N','N','N',GETDATE())) Citation_Main (Void, Warning, User_Defined5,Issue_Date) above what you've shown to make it a complete query, it returns a row. We can't reproduce what you're seeing just from this fragment.– Damien_The_Unbeliever
Nov 16 at 15:35
If I add
select * from (values ('N','N','N',GETDATE())) Citation_Main (Void, Warning, User_Defined5,Issue_Date) above what you've shown to make it a complete query, it returns a row. We can't reproduce what you're seeing just from this fragment.– Damien_The_Unbeliever
Nov 16 at 15:35
Nothing wrong with checking it if you want to update rows where it's N, '', or NULL. If the file isn't written when the predicate = 'N' then another part of your where clause is preventing this, like the Void or Warning
– scsimon
Nov 16 at 15:35
Nothing wrong with checking it if you want to update rows where it's N, '', or NULL. If the file isn't written when the predicate = 'N' then another part of your where clause is preventing this, like the Void or Warning
– scsimon
Nov 16 at 15:35
1
1
Or with that
join to Citation_Activity excluding such rows. But you're the only one who can look at your data, unless or until you add some sample data to your question also that demonstrates the issue.– Damien_The_Unbeliever
Nov 16 at 15:47
Or with that
join to Citation_Activity excluding such rows. But you're the only one who can look at your data, unless or until you add some sample data to your question also that demonstrates the issue.– Damien_The_Unbeliever
Nov 16 at 15:47
2
2
This is trivially easy to debug. Take the cursor declaration and copy it to a query window. Remove the cursor declare part and just make it a single select statement. Go find a PK value from citation_main that you know should be included in the resultset and add that value to the where clause as a condition. Then comment out the user_defined5 logic in the where clause and add that column to the resultset. What do you see?
– SMor
Nov 16 at 15:56
This is trivially easy to debug. Take the cursor declaration and copy it to a query window. Remove the cursor declare part and just make it a single select statement. Go find a PK value from citation_main that you know should be included in the resultset and add that value to the where clause as a condition. Then comment out the user_defined5 logic in the where clause and add that column to the resultset. What do you see?
– SMor
Nov 16 at 15:56
1
1
Are you using a case-sensitive collation? To find out, see here? If so, make sure the column contains a capital N in your testing.
– BoCoKeith
Nov 17 at 1:22
Are you using a case-sensitive collation? To find out, see here? If so, make sure the column contains a capital N in your testing.
– BoCoKeith
Nov 17 at 1:22
|
show 3 more comments
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53340891%2fdoes-is-null-in-where-clause-behave-in-a-special-way%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
If I add
select * from (values ('N','N','N',GETDATE())) Citation_Main (Void, Warning, User_Defined5,Issue_Date)above what you've shown to make it a complete query, it returns a row. We can't reproduce what you're seeing just from this fragment.– Damien_The_Unbeliever
Nov 16 at 15:35
Nothing wrong with checking it if you want to update rows where it's N, '', or NULL. If the file isn't written when the predicate = 'N' then another part of your where clause is preventing this, like the Void or Warning
– scsimon
Nov 16 at 15:35
1
Or with that
jointoCitation_Activityexcluding such rows. But you're the only one who can look at your data, unless or until you add some sample data to your question also that demonstrates the issue.– Damien_The_Unbeliever
Nov 16 at 15:47
2
This is trivially easy to debug. Take the cursor declaration and copy it to a query window. Remove the cursor declare part and just make it a single select statement. Go find a PK value from citation_main that you know should be included in the resultset and add that value to the where clause as a condition. Then comment out the user_defined5 logic in the where clause and add that column to the resultset. What do you see?
– SMor
Nov 16 at 15:56
1
Are you using a case-sensitive collation? To find out, see here? If so, make sure the column contains a capital N in your testing.
– BoCoKeith
Nov 17 at 1:22