•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 391,952 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,836 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser:
Views: 10779 | Replies: 4
 |
•
•
Join Date: Nov 2005
Posts: 3
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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
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
•
•
Join Date: Jul 2005
Location: Dallas, TX
Posts: 481
Reputation:
Rep Power: 4
Solved Threads: 19
Posting Pro in Training
Select 'e' + cast(cast(substring(max(CustID),2,3) as int)+1 as varchar) from customer
•
•
Join Date: Jul 2005
Location: Dallas, TX
Posts: 481
Reputation:
Rep Power: 4
Solved Threads: 19
Posting Pro in Training
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
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
Linear Mode
- Custom error checking (C#)
- Converting String to Integer help (C++)
- Please help............ (VB.NET)
- hop over decimal and straight to business (C++)
Other Threads in the MS SQL Forum
- Previous Thread: Get All Rows In Column In A Where Statment
- Next Thread: hi,urgent-jdbc doubt
