Converting a string to number

[Dorothy.Maseko]

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:

[campkev]

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

[campkev]

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

[Dorothy.Maseko]

Thanx Campkev that helped a lot, u r a *

[campkev]

no problem