Auto increment values with alpha numeric

Question

Hi experts,
I am trying automatically increment the alpha numeric field (say for productid in product table).

But I am getting an error (see below). Could somebody please look into this error or any other approach to achieve this task?

My table details:

create table tblProduct
(
 id varchar(15)
)

create procedure spInsertInProduct
AS
Begin
  DECLARE @PId VARCHAR(15)
  DECLARE @NId INT
  DECLARE @COUNTER INT
  SET @PId = 'PRD00'
  SET @COUNTER = 0
  --This give u max numeric id from the alphanumeric id
  SELECT @NId = cast(substring(id, 3, len(id)) as int) FROM tblProduct group by left(id, 2) order by id
  --here u increse the vlaue to numeric id by 1
  SET @NId = @NId + 1
  --GENERATE ACTUAL APHANUMERIC ID HERE
  SET @PId = @PId + cast(@NId AS VARCHAR)
  INSERT INTO tblProduct(id)values (@PId)
END

I am gettin the following error:

Msg 8120, Level 16, State 1, Procedure spInsertInProduct, Line 10 Column 'tblProduct.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Msg 8120, Level 16, State 1, Procedure spInsertInProduct, Line 10 Column 'tblProduct.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.**

Answer 1

Your line

SELECT @NId = cast(substring(id, 3, len(id)) as int)
  FROM tblProduct
  group by left(id, 2)
  order by id

is not doing what you were wanting it to. It is failing because you can't include id in the select directly, because you're grouping by left(id, 2), not id itself. You cannot put something into the select statement when grouping, unless it is part of the Group By, or an aggregate (such as SUM and MAX.)

(Edit: Corrected, Left and Substring are not 0 based--to get the PRD tag and such, we need substring 4, and left 3.)

The correct way of doing this would be:

SELECT @NId = cast(substring(MAX(id), 4, len(MAX(id))) as int) 
  FROM tblProduct
  group by left(id, 3)
  order by left(id, 3)

Answer 2

 SELECT @NId = max(
                    cast(substring(id, 4, len(id)) as int)
                  ) 
FROM tblProduct;

This assumes your substring function is returning the numeric portion of your id. I made changes since in other examples your id's start with PRD.

Side note: There is no reason to have you Product ID's start with PRD in the database. If this were an identity field, you could set it to increment by 1 and in any display just have: 'PRD' + Cast(ID as varchar25)) as ProductID. Maybe it is not that simple of all ID's do not start with the same three letters.

Answer 3

I have tested your storeprocedure in SQL-2000 and Query Analyzer is works very Well Just I have removed create table code from that.