SQL query returning null?

Question

When I run this query, it returns null value for ID. The code is supposed to get the max value in the id column.

2013-06-13-0001
2013-06-13-0002

It should get the 2013-06-13-0002 (because of the 0002 being a greater value than the 0001) for the id column.. the query is supposed to get the max id and add 1 to it.

SELECT 
   ID = LEFT(max(ID), 10) + '-' + 
        RIGHT('000' + CONVERT(VARCHAR, CONVERT(INT, RIGHT(max(ID), 4)) + 1), 4)
FROM John_IEP_Crossing_Dock_Shipment
WHERE
   LEFT(ID, 10) = CONVERT(VARCHAR(10), Getdate(), 20)

try use the column remaining... like this select field as new_field_name from table — SQL.injection
– SQL.injection, Commented Jun 14, 2013 at 19:43
@user2484066 Your WHERE clause is dependent on the date, so it's very possible there is no data with today's date but there was with yesterdays. — JNK
– JNK, Commented Jun 14, 2013 at 19:46
Ah so you are trying to create some date dependant IDENTITY scheme so ID is like 2013-06-140-0001? I wouldn't bother anyway as this is difficult to get right under conditions of concurrency but looks like you will get this problem every day unless you change the query to handle the first one of the day. (when MAX(ID) is NULL) — Martin Smith
– Martin Smith, Commented Jun 14, 2013 at 20:01
The whole concept looks fundamentally flawed unless this is a single user system. How are you protecting against two users simultaneously doing the SELECT and getting given the same "next" identity value? I would just have a separate column for create_date and have a normal IDENTITY column. — Martin Smith
– Martin Smith, Commented Jun 14, 2013 at 20:21

Tim Lehner · Accepted Answer · 2013-06-14 21:13:57Z

1

You shouldn't try to select a new custom ID like this before you actually insert the record, as someone else may insert that same value before you. So here's how you can potentially run your table inserts:

insert into John_IEP_Crossing_Dock_Shipment (MyCol1, MyCol2, ID)
select @MyFormValue1,
    @MyFormValue2,
    convert(varchar(10), current_timestamp, 20)
        + '-'
        + right('000' + cast(cast(right(coalesce(max(ID), '0'), 4) as smallint) + 1 as varchar(4)), 4)
from John_IEP_Crossing_Dock_Shipment
where ID like convert(varchar(10), current_timestamp, 20) + '%'; -- like may use an index in this case

We're coming up with the ID during the insert, not before. This should mostly solve the contention issue (heck, you can only have 9,999 inserts per day anyway with that 4-digit ID suffix) and coalesce will take care of the case where you have no data yet today (and insert ID "...-0001").

If you need to select this record immediately after the insert to display to the user, you could use the output keyword.

If it does not already have one, adding a unique constraint of some sort to this ID column is mandatory. At least then you could handle the exception and allow the user to try the insert again if contention is still an issue (I'm not expert enough to say that these inserts could never collide or if you should lock the table to prevent such a thing).

I agree with @MartinSmith and I would rather use a create date column with an identity column (or maybe 2012's sequence), and thus be able to calculate this ID column. I also understand that some part numbering schemes should just be stored, and sometimes skipped numbers aren't valid (unless there are deletes).

I welcome feedback from those that know more.

answered Jun 14, 2013 at 21:13

Tim Lehner

15.3k4 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2484066 Over a year ago

would I need to put anything in the mycol1, mycol2? and what do I need to put in myformvalue1 and 2?

Tim Lehner Over a year ago

Those are simply generic example placeholders of the other columns your table may have. I was imagining you were inserting from a web app. Only your non-null columns are necessary for an insert. You could even run the select by itself to insert later, but that's exactly against my first words of caution.

user2484066 Over a year ago

Oh right, I took out the placeholder for mycol. and just left it as ID. Yes, I'm inserting it from a web app. Still don't get what the @myform is though.. sorry D: could you explain what I can do with that?

Collectives™ on Stack Overflow

SQL query returning null?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related