• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

Not sure why SQL Query is returning this value?

I've attached a screenshot of a SQL Query from SSMS 2008. Based on my SELECT statement, it should not return anything because at this point, the "TEST_Comments" table is empty. So, my thought is since @seqPlusOne is initialized to 0, it should set it to 1 as the IF statement suggests....
Screenshot.jpg
0
BlakeMcKenna
Asked:
BlakeMcKenna
  • 11
  • 9
  • 2
  • +2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
If there is no record matching the where condition, @seqplusOne will be null and you cannot insert a 'Null' into a non nullable column
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
So the condition after the 'SELECT' clause should be

IF ISNULL(@seqplusOne, 0 ) = 0
0
 
Scott PletcherSenior DBACommented:
You should get the value directly in the INSERT statement; separate statements could allow additional row(s) to be inserted, perhaps causing a duplicate.

If you can paste the actual code, instead of a picture (ugh!) of it, I'll rewrite it for you.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
BlakeMcKennaAuthor Commented:
I did what you said and still got the same result...see screenshot!
Screenshot.jpg
0
 
BlakeMcKennaAuthor Commented:
		BEGIN
			SELECT @seqPlusOne = MAX(seqNO) + 1 FROM TEST_Comments WHERE main_ID = @main_ID AND comment_ID = @comment_ID
			
			IF ISNULL(@seqPlusOne, 0) = 0

			INSERT INTO TEST_Comments
				(main_ID
				 ,comment_ID
				 ,seqNO
				 ,comment
				 ,internalComment
				 ,certificateComment
				 ,active
				 ,createdBy_ID
				 ,dateCreated
				 ,updatedBy_ID
				 ,dateUpdated)
			 VALUES
				(@main_ID
				 ,@comment_ID
				 ,@seqPlusOne
				 ,@comment
				 ,@internalComment
				 ,@certificateComment
				 ,@active
				 ,@createdBy_ID
				 ,GETDATE()
				 ,@updatedBy_ID
				 ,GETDATE())
		END	

Open in new window


Thank you!
0
 
Habib PourfardSoftware DeveloperCommented:
Try:
SELECT @sqlPlusOne = MAX(seqNo) FROM TEST_Comments Where main_ID = 127992 AND comment_ID = 2
SET @sqlPlusOne = ISNULL(@sqlPlusOne, 0) + 1

Open in new window

0
 
Scott PletcherSenior DBACommented:
Btw, @main_ID and @comment_ID must be leading keys on an index containing seqNO for either code to run reasonably well at all (or the table itself has to be rather small).


            BEGIN

                  INSERT INTO TEST_Comments
                        (main_ID
                         ,comment_ID
                         ,seqNO
                         ,comment
                         ,internalComment
                         ,certificateComment
                         ,active
                         ,createdBy_ID
                         ,dateCreated
                         ,updatedBy_ID
                         ,dateUpdated)
                   SELECT
                         @main_ID
                         ,@comment_ID
                         ,ISNULL(MAX(seqNO) + 1, MAX(default_seqNo)) AS seqPlusOne
                         ,@comment
                         ,@internalComment
                         ,@certificateComment
                         ,@active
                         ,@createdBy_ID
                         ,GETDATE()
                         ,@updatedBy_ID
                         ,GETDATE()
                   FROM Test_Comments WITH (TABLOCKX) --briefly lock the table while getting the MAX(seqNO)
                   RIGHT OUTER JOIN (
                       SELECT 1 AS default_seqNO
                   ) AS In_Case_Test_Comments_Is_Empty ON 1 = 1
                   WHERE main_ID = @main_ID AND comment_ID = @comment_ID
            END
0
 
BlakeMcKennaAuthor Commented:
Yep, they main_ID and comment_ID are the 2 of the 3 columns that comprise the PK...

Thanks for your help!
0
 
BlakeMcKennaAuthor Commented:
Scott,

I tried your code and it doesn't work either. Bascially getting the same error: "Cannot insert the value NULL into column 'seqNO', table 'TEST_Comments'; column does not allow nulls."

Quite honestly I'm not quite understanding the code. What is the "ON 1 = 1". I never seen that before.
0
 
Scott PletcherSenior DBACommented:
Something else is going on, as "default_seqNO" can never be NULL or missing, assuming a RIGHT OUTER JOIN (as coded above).


USE tempdb

CREATE TABLE dbo.table1 ( column1 int )

SELECT ISNULL(MAX(column1) + 1, MAX(default_seqNO))
FROM dbo.table1 WITH (TABLOCKX)
RIGHT OUTER JOIN ( SELECT 1 AS default_seqNO ) AS In_Case_Main_Table_Is_Empty ON 1 = 1

INSERT INTO dbo.table1 VALUES ( 5 )

SELECT ISNULL(MAX(column1) + 1, MAX(default_seqNO))
FROM dbo.table1 WITH (TABLOCKX)
RIGHT OUTER JOIN ( SELECT 1 AS default_seqNO ) AS In_Case_Main_Table_Is_Empty ON 1 = 1

DROP TABLE dbo.table1
0
 
BlakeMcKennaAuthor Commented:
How is "default_seqNO" not being flagged as an error since it's not in the TEST_Comments table?
0
 
Scott PletcherSenior DBACommented:
It's joined to that table.  Joined table columns are available for use in the query.

Similar to:

SELECT a.col1, b.col2
FROM tablea a
INNER JOIN tableb b ON b.col1 = a.col1
0
 
BlakeMcKennaAuthor Commented:
Here is the whole SP...

ALTER PROCEDURE [dbo].[spAddUpdate_TEST_Comments]
	@main_ID				INT=NULL,
	@comment_ID				INT=NULL,
	@seqNO					INT=NULL,
	@internalComment		INT=NULL,
	@certificateComment		INT=NULL,
	@comment				VARCHAR(MAX)=NULL,
	@active					INT=NULL,
	@createdBy_ID			INT=NULL,
	@updatedBy_ID			INT=NULL
AS
BEGIN
	DECLARE @seqPlusOne		INT = 0
	
	IF EXISTS (SELECT main_ID FROM TEST_Comments WHERE main_ID = @main_ID AND comment_ID = @comment_ID AND seqNO = @seqNO)
		BEGIN
			UPDATE	TEST_Comments
			   SET	comment = @comment
					,internalComment = @internalComment
					,certificateComment = @certificateComment
					,updatedBy_ID = @updatedBy_ID
					,dateUpdated = GETDATE()
					,active = @active
			 WHERE main_ID = @main_ID AND comment_ID = @comment_ID AND seqNO = @seqNO
		END
	ELSE
		BEGIN
			INSERT INTO TEST_Comments
				(main_ID
                 ,comment_ID
                 ,seqNO
                 ,comment
                 ,internalComment
                 ,certificateComment
                 ,active
                 ,createdBy_ID
                 ,dateCreated
                 ,updatedBy_ID
                 ,dateUpdated)
			  SELECT 
                 @main_ID
                 ,@comment_ID
                 ,ISNULL(MAX(seqNO) + 1, MAX(default_seqNo)) AS seqPlusOne
                 ,@comment
                 ,@internalComment
                 ,@certificateComment
                 ,@active
                 ,@createdBy_ID
                 ,GETDATE()
                 ,@updatedBy_ID
                 ,GETDATE()
                FROM Test_Comments WITH (TABLOCKX) --briefly lock the table while getting the MAX(seqNO)
					 RIGHT OUTER JOIN (SELECT 1 AS default_seqNO) AS In_Case_Test_Comments_Is_Empty ON 1 = 1
               WHERE main_ID = @main_ID AND comment_ID = @comment_ID		
		END	
END

Open in new window

0
 
Scott PletcherSenior DBACommented:
Hmm, I don't see an issue.

You made sure you don't accidentally use a variable in the INSERT query, either @seqNO or @seqPlusOne, as those could contain NULL.

Are you sure that's the code that's actually being run to produce that error?  Maybe a temp proc name was used somewhere, or code not moved to the right environment, etc.?!
0
 
BlakeMcKennaAuthor Commented:
What I posted in the previous post is what the SP looks like that gets executed. It's funny because I ran the code below in SSMS and the "seqPlusOne" returned NULL.

DECLARE @main_ID		INT = 128000
DECLARE @comment_ID		INT = 2
DECLARE @comment		VARCHAR(50) = 'All Tests Successful!'

  SELECT 
     @main_ID
     ,@comment_ID
     ,ISNULL(MAX(seqNO) + 1, MAX(default_seqNo)) AS seqPlusOne
     ,@comment
    FROM Test_Comments WITH (TABLOCKX) --briefly lock the table while getting the MAX(seqNO)
		 RIGHT OUTER JOIN (SELECT 1 AS default_seqNO) AS In_Case_Test_Comments_Is_Empty ON 1 = 1
   WHERE main_ID = @main_ID AND comment_ID = @comment_ID		

Open in new window

0
 
Scott PletcherSenior DBACommented:
Sorry, I just don't see how.

The hint really should be "XLOCK" rather than "TABLOCKX" to increase concurrency, but that should only be an issue if the SQL is timing out waiting for the lock, and you'd see that in SSMS.
0
 
BlakeMcKennaAuthor Commented:
As I said, I ran the above code in a new query pane. I have attached the results as a screenshot. I have also attached the Table Design. The 3 columns that make up the PK have not been altered. None of them are Identity columns.
Screenshot.jpg
Screenshot2.jpg
0
 
Scott PletcherSenior DBACommented:
Wow, interesting.

Let's try it this way, just in case SQL optimizer is "confused".  And let's include the default_seqNO in the results, just to make sure it's there as well.


DECLARE @main_ID int
DECLARE @comment_ID int
DECLARE @comment varchar(30)

  SELECT
     @main_ID
     ,@comment_ID
     ,ISNULL(seqNO_from_table, default_seqNo) AS seqPlusOne
     ,default_seqNO
     ,@comment
    FROM (
        SELECT MAX(seqNO) + 1 AS seqNO_from_table
        FROM Test_Comments WITH (XLOCK) --briefly lock the table while getting the MAX(seqNO)
    ) AS main_table
             RIGHT OUTER JOIN (SELECT 1 AS default_seqNO) AS In_Case_Test_Comments_Is_Empty ON 1 = 1
   WHERE main_ID = @main_ID AND comment_ID = @comment_ID
0
 
BlakeMcKennaAuthor Commented:
It errors out on the very last Where clause because it doesn't recognize "main_ID" or "comment_ID". I'm assuming because "TEST_Comments" isn't explicitly defined in the main From clause.
0
 
Scott PletcherSenior DBACommented:
Yes, sorry, got that line out of order somehow:

SELECT
      @main_ID
      ,@comment_ID
      ,ISNULL(seqNO_from_table, default_seqNo) AS seqPlusOne
      ,default_seqNO
      ,@comment
     FROM (
         SELECT MAX(seqNO) + 1 AS seqNO_from_table
         FROM Test_Comments WITH (XLOCK) --briefly lock the table while getting the MAX(seqNO)
        WHERE main_ID = @main_ID AND comment_ID = @comment_ID      ) AS main_table
              RIGHT OUTER JOIN (SELECT 1 AS default_seqNO) AS In_Case_Test_Comments_Is_Empty ON 1 = 1
0
 
PortletPaulCommented:
You need join conditions otherwise you have the effect of an inner join


DECLARE @main_ID		INT = 20
DECLARE @comment_ID		INT = 2
DECLARE @comment		VARCHAR(50) = 'All Tests Successful!'

  SELECT 
         @main_ID
       , @comment_ID
       , ISNULL(MAX(seqNO) + 1, MAX(default_seqNo)) AS seqPlusOne
       , @comment
  FROM (SELECT 1 AS default_seqNO) AS In_Case
  LEFT JOIN Test_Comments WITH (TABLOCKX) ON 1=1
  AND main_ID = @main_ID AND comment_ID = @comment_ID
;

Open in new window

(and I dislike right outer joins, my preference is left to right :)

{+ edit}
changing the WHERE to AND works for the right join also
0
 
BlakeMcKennaAuthor Commented:
Thanks PortletPaul...your suggestion worked. I to prefer to use LEFT to RIGHT joins. They just make more sense.
0
 
Scott PletcherSenior DBACommented:
You're welcome.  Good luck with future qs.
0
 
BlakeMcKennaAuthor Commented:
Thank you Scott!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 11
  • 9
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now