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
BlakeMcKennaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.