jvalescu
asked on
How do I increment a column string value in T-SQL?
Greetings,
I have a table that has a column called "Control". This is a nvarchar(25) data type. I want to write a stored procedure that will copy the latest record into a new record and add a "-xx" value. For example:
Control: A1234
A call to the SP would yield all of the data from Control 'A1234' and create a new record with 'A1234-1'
A subsequent call would yield 'A1234-2', etc.
Been struggling with this for awhile and its very urgent. Thank you.
I have a table that has a column called "Control". This is a nvarchar(25) data type. I want to write a stored procedure that will copy the latest record into a new record and add a "-xx" value. For example:
Control: A1234
A call to the SP would yield all of the data from Control 'A1234' and create a new record with 'A1234-1'
A subsequent call would yield 'A1234-2', etc.
Been struggling with this for awhile and its very urgent. Thank you.
ASKER
Thank you for the quick response. I put your suggestions into the code below, but I really need a solution to insert the new record with the new "-xx" suffix. Thank you!
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE SplitCoil
(@CoilNumber varchar(25) )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @nVchNewControlID AS NVARCHAR(25)
SELECT
@nVchNewControlID = MAX(RIGHT(@CoilNumber,LEN(@CoilNumber)-CHARINDEX('-',@CoilNumber))) + 1
FROM tblCoilLog
WHERE CHARINDEX('-',CoilNumber) > 0
AND @CoilNumber LIKE '%' + @nVchNewControlID + '%'
END
GO
try this
CREATE PROCEDURE SplitCoil
(@CoilNumber varchar(25) )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @intNewControlID AS INT
DECLARE @NewCoilNumber AS VARCHAR(25)
SELECT
@intNewControlID = MAX(RIGHT(CoilNumber,LEN(CoilNumber)-CHARINDEX('-',CoilNumber)))
FROM tblCoilLog
WHERE CHARINDEX('-',CoilNumber) > 0
AND CoilNumber LIKE '%' + @CoilNumber + '%'
SET @NewCoilNumber = @CoilNumber + '-' + ISNULL(REPLICATE('0',2-len(@intNewControlID)),'') + (ISNULL(@intNewControlID,0) + 1)
-- @NewCoilNumber - will get formated @CoilNumber-xx format
END
GO
ASKER
Thanks again. I get the following error when I use the parameter '1234':
Msg 245, Level 16, State 1, Procedure SplitCoil, Line 20
Conversion failed when converting the varchar value '1234-' to data type int.
Msg 245, Level 16, State 1, Procedure SplitCoil, Line 20
Conversion failed when converting the varchar value '1234-' to data type int.
Hmm.. sorry missed to cast
change to ..... CAST((ISNULL(@intNewContro lID,0) + 1) AS VARCHAR)
change to ..... CAST((ISNULL(@intNewContro
CREATE PROCEDURE SplitCoil
(@CoilNumber varchar(25) )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @intNewControlID AS INT
DECLARE @NewCoilNumber AS VARCHAR(25)
SELECT
@intNewControlID = MAX(RIGHT(CoilNumber,LEN(CoilNumber)-CHARINDEX('-',CoilNumber)))
FROM tblCoilLog
WHERE CHARINDEX('-',CoilNumber) > 0
AND CoilNumber LIKE '%' + @CoilNumber + '%'
SET @NewCoilNumber = @CoilNumber + '-' + ISNULL(REPLICATE('0',2-len(@intNewControlID)),'') + CAST((ISNULL(@intNewControlID,0) + 1) AS VARCHAR)
-- @NewCoilNumber - will get formated @CoilNumber-xx format
END
GO
ASKER
Binuth,
I know this is really close, but I get the same error. I've included the execute statement that I am testing with and this is the error result:
Msg 245, Level 16, State 1, Procedure SplitCoil, Line 23
Conversion failed when converting the varchar value '1234-' to data type int.
Thank you.
I know this is really close, but I get the same error. I've included the execute statement that I am testing with and this is the error result:
Msg 245, Level 16, State 1, Procedure SplitCoil, Line 23
Conversion failed when converting the varchar value '1234-' to data type int.
Thank you.
USE [OrderEntry_prodSQL]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[SplitCoil]
@CoilNumber = N'1234'
SELECT 'Return Value' = @return_value
GO
create a proc like this and execute.
exec sp_YourProc
exec sp_YourProc
create proc sp_YourProc as
declare @Control nvarchar(25),@int int
select @Control = max(case when CHARINDEX('-',Control) = 0 then Control else SUBSTRING(Control,1,CHARINDEX('-',Control)-1) end) from YourTable
select @int = max(convert(int,case when CHARINDEX('-',Control) = 0 then convert(nvarchar(10),0)
else SUBSTRING(Control,CHARINDEX('-',Control)+1,LEN(Control)) end))
from YourTable where Control like @Control+'%'
select @Control + '-' + convert(nvarchar(10),@int+1)
insert into YourTable (Control) values(@Control + '-' + convert(nvarchar(10),@int+1))
check this example
-- Created a temp table to test this
create table TempTable(Control nvarchar(25))
insert into TempTable values('A123'),('A1234'),('A2234-6')
go
-- create the proc
create proc sp_YourProc as
declare @Control nvarchar(25),@int int
select @Control = max(case when CHARINDEX('-',Control) = 0 then Control else SUBSTRING(Control,1,CHARINDEX('-',Control)-1) end) from TempTable
select @int = max(convert(int,case when CHARINDEX('-',Control) = 0 then convert(nvarchar(10),0)
else SUBSTRING(Control,CHARINDEX('-',Control)+1,LEN(Control)) end))
from TempTable where Control like @Control+'%'
select @Control + '-' + convert(nvarchar(10),@int+1)
insert into TempTable (Control) values(@Control + '-' + convert(nvarchar(10),@int+1))
go
-- execute the proc
exec sp_YourProc -- A2234-7
exec sp_YourProc -- A2234-8
exec sp_YourProc -- A2234-9
exec sp_YourProc -- A2234-10
exec sp_YourProc -- A2234-10
exec sp_YourProc -- A2234-11
go
-- drop the table to test with some other sample data
drop table TempTable
go
-- create the table again
create table TempTable(Control nvarchar(25))
insert into TempTable values('A123'),('A1234')
go
-- execute the proc
exec sp_YourProc -- A1234-1
exec sp_YourProc -- A1234-2
exec sp_YourProc -- A1234-3
exec sp_YourProc -- A1234-4
exec sp_YourProc -- A1234-5
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Binuth:
I entered the code you had above, and executed the following:
DECLARE @return_value nvarchar(25)
EXEC SplitCoil 'CN200',@return_value
SELECT @return_value
The resule is 'NULL' everytime even though I know this value is in my table. I've tried other values as well and they also return null. Thanks.
I entered the code you had above, and executed the following:
DECLARE @return_value nvarchar(25)
EXEC SplitCoil 'CN200',@return_value
SELECT @return_value
The resule is 'NULL' everytime even though I know this value is in my table. I've tried other values as well and they also return null. Thanks.
USE [OrderEntry_prodSQL]
GO
/****** Object: StoredProcedure [dbo].[SplitCoil] Script Date: 04/08/2009 10:35:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SplitCoil](@CoilNumber varchar(25) ,@return_value VARCHAR(25) OUTPUT)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @NewCoilNumber AS VARCHAR(25)
DECLARE @intNewControlID AS INT
SET @intNewControlID = 0
SELECT
@intNewControlID = ISNULL(MAX(RIGHT(CoilNumber,LEN(CoilNumber)-CHARINDEX('-',CoilNumber))),0)
FROM tblCoilLog
WHERE CHARINDEX('-',CoilNumber) > 0 AND CoilNumber LIKE '%' + @CoilNumber + '%'
SET @return_value = @CoilNumber + '-' + ISNULL(REPLICATE('0',2-len(@intNewControlID)),'') + CAST((ISNULL(@intNewControlID,0) + 1) AS VARCHAR)
END
Did you check my script?
ASKER
Sharath,
Yes, I did. And it worked very close to what I needed. One problem I found is if I entered a CoilNumber that did not exist, it would still return what I passed in along with a '-1'. I need it make sure the CoilNumber passed in exists. Is this possible?
Yes, I did. And it worked very close to what I needed. One problem I found is if I entered a CoilNumber that did not exist, it would still return what I passed in along with a '-1'. I need it make sure the CoilNumber passed in exists. Is this possible?
USE [OrderEntry_prodSQL]
GO
/****** Object: StoredProcedure [dbo].[sp_SplitCoil] Script Date: 04/08/2009 13:09:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_SplitCoil] (@CoilNumber nvarchar(25), @return_value nvarchar(25) OUTPUT ) as
--declare @CoilNumber nvarchar(25),
DECLARE @int int
select @CoilNumber = max(case when CHARINDEX('-',CoilNumber) = 0 then CoilNumber else SUBSTRING(CoilNumber,1,CHARINDEX('-',CoilNumber)-1) end) from TempTable
select @int = max(convert(int,case when CHARINDEX('-',CoilNumber) = 0 then convert(nvarchar(10),0)
else SUBSTRING(CoilNumber,CHARINDEX('-',CoilNumber)+1,LEN(CoilNumber)) end))
from TempTable where CoilNumber like @CoilNumber+'%'
select @CoilNumber + '-' + convert(nvarchar(10),@int+1)
PRINT @CoilNumber + '-' + convert(nvarchar(10),@int+1)
insert into TempTable (CoilNumber) values(@CoilNumber + '-' + convert(nvarchar(10),@int+1))
SET @return_value = @CoilNumber + '-' + convert(nvarchar(10),@int+1)
May be my understanding of your requirement is wrong. I am checking for the maximum CoilNumber from your table and started inserting -1, -2 ...rows. Do you want to pass the CoilNumber to the porc and insert -1, -2 ,... for that CoilNumber only?
ASKER
Binuth,
I must have been making some kind of mistake. You proc is working pretty well. I tested it with several examples and they worked fine. However, I have a coil #, 08102-0966 which produces this result: 08102-0966-967. Any ideas?
I must have been making some kind of mistake. You proc is working pretty well. I tested it with several examples and they worked fine. However, I have a coil #, 08102-0966 which produces this result: 08102-0966-967. Any ideas?
ASKER
Sharath, you are correct. Want to pass the coil number in for that coil only.
check this
USE [OrderEntry_prodSQL]
GO
/****** Object: StoredProcedure [dbo].[sp_SplitCoil] Script Date: 04/08/2009 13:09:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_SplitCoil] (@CoilNumber nvarchar(25), @return_value nvarchar(25) OUTPUT ) as
declare @Control nvarchar(25),@int int
select @Control = max(case when CHARINDEX('-',CoilNumber) = 0 then CoilNumber else SUBSTRING(CoilNumber,1,CHARINDEX('-',CoilNumber)-1) end)
from TempTable where CoilNumber = @CoilNumber
select @int = max(convert(int,case when CHARINDEX('-',CoilNumber) = 0 then convert(nvarchar(10),0)
else SUBSTRING(CoilNumber,CHARINDEX('-',CoilNumber)+1,LEN(CoilNumber)) end))
from TempTable where CoilNumber = @CoilNumber
select @return_value = (@Control + '-' + convert(nvarchar(10),@int+1))
if @return_value is not null insert into TempTable (CoilNumber) values(@return_value)
ASKER
Sharath,
Almost there. The first time I passed 'A123', it returned A123-1. Thats correct. The second time I passed 'A123', it returned A123-1. After several times I checked the database and it created multiple 'A123-1' records, where it should have incremented each. Thanks for your help.
Almost there. The first time I passed 'A123', it returned A123-1. Thats correct. The second time I passed 'A123', it returned A123-1. After several times I checked the database and it created multiple 'A123-1' records, where it should have incremented each. Thanks for your help.
try this.
USE [OrderEntry_prodSQL]
GO
/****** Object: StoredProcedure [dbo].[sp_SplitCoil] Script Date: 04/08/2009 13:09:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_SplitCoil] (@CoilNumber nvarchar(25), @return_value nvarchar(25) OUTPUT ) as
declare @Control nvarchar(25),@int int
select @Control = max(case when CHARINDEX('-',CoilNumber) = 0 then CoilNumber else SUBSTRING(CoilNumber,1,CHARINDEX('-',CoilNumber)-1) end)
from TempTable where CoilNumber = @CoilNumber
select @int = max(convert(int,case when CHARINDEX('-',CoilNumber) = 0 then convert(nvarchar(10),0)
else SUBSTRING(CoilNumber,CHARINDEX('-',CoilNumber)+1,LEN(CoilNumber)) end))
from TempTable where CoilNumber like @CoilNumber+'%'
select @return_value = (@Control + '-' + convert(nvarchar(10),@int+1))
if @return_value is not null insert into TempTable (CoilNumber) values(@return_value)
ASKER
Sharath,
Same result as before.
Same result as before.
No, i tested that and getting incrementing results. let me have a look again.
What is your table name?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Sharath and Binuth!
Open in new window