Is there an easy way to have a non-IDENTITY, auto incrementing column based on another column? +----+-------------+-----------------+-------------+
| ID | ProductName | ProductCategory | ProductCode |
+----+-------------+-----------------+-------------+
|  1 | Item 1      |               1 |           1 |
|  2 | Item 2      |               1 |           2 |
|  3 | Item 3      |               2 |           1 |
|  4 | Item 4      |               3 |           1 |
|  5 | Item 5      |               1 |           3 |
|  6 | Item 6      |               2 |           2 |
+----+-------------+-----------------+-------------+


Essentially what I am trying to do is have a column autoincrement but based on the last row in the same ProductCategory.

Currently I have a stored procedure that does the following

SELECT TOP 1 @NextProductCode = ProductCode + 1 
  FROM Products 
 WHERE ProductCategory = @ProductCategory


This works, but despite the fact that this is a pretty low traffic database there's an obvious concurrency issue here. I could throw a TABLOCKX in the stored procedure I guess but I'm curious if there's a more simple way to accomplish this.

I'm also trying to avoid dynamic SQL otherwise I could create a sequence for each new ProductCategory.
If I am Understanding you correctly, you are looking to select a group of product that's already in a DB, and assign a new Product ID to each Product that is Unique to each Product Category, if this is correct then you can add this to your SELECT statement,

ROW_NUMBER() OVER (PARTITION BY ProductCategory ORDER BY ID) AS ProductCode

it should do what you are looking for.