0

I have a value E101 as my empId in my employees table and now i need to increment this value, i.e. E102, E103...etc. How can i get this to work? I've used the procedure but it's not working what am i doing wrong?

ALTER PROCEDURE CalcPrimaryKey
AS
DECLARE @numValue int, @charOldValue varchar(4), @charNewValue varchar(4)
BEGIN
SELECT @numValue = MAX(CAST(SUBSTRING(CustID, 2, 3)AS Integer))FROM Customer
@charOldValue = CustId
SET @numValue = @numValue + 1


SET @charNewValue = LEFT(CustID,1) + RIGHT('000' + CAST(@numValue AS varchar,3))


GO


and i get this error
Server: Msg 170, Level 15, State 1, Procedure CalcPrimaryKey, Line 6
Line 6: Incorrect syntax near '@charOldValue'.
Server: Msg 170, Level 15, State 1, Procedure CalcPrimaryKey, Line 10
Line 10: Incorrect syntax near ','.

Help needed urgently, Please :sad:

2
Contributors
4
Replies
6
Views
12 Years
Discussion Span
Last Post by campkev
0

Select 'e' + cast(cast(substring(max(CustID),2,3) as int)+1 as varchar) from customer

0

of course, this will cause problems once you hit user e999 so I would do this

declare @length int
select @length =  len(max(CustID)) from customer 
Select 'e' + cast(cast(substring(max(CustID),2,@length-1) as int)+1 as varchar) from  customer
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.