Not sure why SQL Query is returning this value?

If there is no record matching the where condition, @seqplusOne will be null and you cannot insert a 'Null' into a non nullable column

So the condition after the 'SELECT' clause should be

IF ISNULL(@seqplusOne, 0 ) = 0

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.

I did what you said and still got the same result...see screenshot!
Screenshot.jpg

		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	

Select allOpen in new window

Thank you!

Try:

SELECT @sqlPlusOne = MAX(seqNo) FROM TEST_Comments Where main_ID = 127992 AND comment_ID = 2
SET @sqlPlusOne = ISNULL(@sqlPlusOne, 0) + 1

Select allOpen in new window

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

Yep, they main_ID and comment_ID are the 2 of the 3 columns that comprise the PK...

Thanks for your help!

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.

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

How is "default_seqNO" not being flagged as an error since it's not in the TEST_Comments table?

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

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

Select allOpen in new window

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.?!

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		

Select allOpen in new window

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.

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

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

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.

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

Thanks PortletPaul...your suggestion worked. I to prefer to use LEFT to RIGHT joins. They just make more sense.

You're welcome.  Good luck with future qs.

Thank you Scott!