June 8, 2005 at 12:23 pm
Hi all,
I'm trying to convert string to int, increment the number by 1 then convert the number back to string. But it's not working w/ the syntax that I have. Could someone provide a hand?
Error: Syntax error converting the varchar value ‘123456-00’ to a column of data type int.
CREATE PROCEDURE dbo.sptest
@work_ord_num char(9),
--@w_o char(9) out
@w_o_n int Out
AS
--set @work_ord_num =
SELECT @w_o_n = CONVERT(int, @work_ord_num)
set @w_o_n = @w_o_n + 1
return(@w_o_n);
GO
Thanks!
June 8, 2005 at 12:27 pm
the dash cannot be converted to an int...
are you trying to increment like this 123457-00 or like this 123456-01?
June 8, 2005 at 12:38 pm
Hi Remi,
THanks for the quick reply.
I'm trying to increment the last digit by 1: 123456-01
How and what is the best way to accomplish this?
Thanks!
June 8, 2005 at 12:40 pm
I'm assuming that this is the id of the table.
I would split the id in 2 columns like :
order id = 123456
item number = 1
Am I understanding your situation correctly?
June 8, 2005 at 12:55 pm
Hi Remi,
I said adding one to string field. What I really want is subtract one from the field to get previous work order.
This field stores work order& release #(123456-00) in the table. When I call the sproc & pass in the current work order/release #(123456-01) I need the sproc to return the previous release on the work order(123456-00). I was hoping to use CONVERT,CAST of some other FUNCTIONS to solve my problem. Will I have to split fields in order to manipulate the last two digits?
Thanks!
June 8, 2005 at 1:09 pm
Not really, the simple version :
Declare @Par as varchar(10)
Declare @Return as varchar(10)
Declare @Int as int
set @Par = '123456-01'
set @Int = CAST(right(@Par, 2) AS INT)
set @Int = @Int - 1
set @Return = LEFT(@Par, 6) + '-' + RIGHT('0' + CAST(@Int as varchar(2)), 2)
select @Return
--123456-00
if the last 2 digits can go over 99, then you'll need to recode this in another way.
June 8, 2005 at 1:39 pm
Hi Remi,
Thanks for the code.
Now the error saids it can't convert '123456-00' to int data type. I don't need it to be int. It needs to be char. Why am I getting this error?
Thanks!
June 8, 2005 at 1:42 pm
What code gives you the error?
June 8, 2005 at 1:53 pm
Hi,
when I run the sproc below.
Am I missing CAST or CONVERT function in the code?
CREATE PROCEDURE dbo.sptest
AS
Declare @Par as varchar(10)
Declare @Return as varchar(10)
Declare @Int as int
set @Par = '123456-01'
set @Int = CAST(right(@Par, 2) AS INT)
set @Int = @Int - 1
set @Return = LEFT(@Par, 6) + '-' + RIGHT('0' + CAST(@Int as varchar(2)), 2)
--select @Return
return(@return)
Error: Syntax error converting the varchar value ‘123456-00’ to a column of data type int.
Thanks again!
June 8, 2005 at 1:56 pm
You can't return something else than an interger in a stored proc. You'll have to use either a function or an output parameter in the proc.
June 8, 2005 at 2:11 pm
I also tried outputting to @w_o variable still get the same error message. The error occurred during executing sproc in vb on line below
.Execute , , adExecuteNoRecords
the @w_o is char & @return is varchar. that shouldn't cause the problem. It is returning right value but can't understand why it is looking to convert to int.
CREATE PROCEDURE dbo.sptest
@w_o char(9) out
AS
Declare @Par as varchar(10)
Declare @Return as varchar(10)
Declare @Int as int
set @Par = '123456-01'
set @Int = CAST(right(@Par, 2) AS INT)
set @Int = @Int - 1
set @Return =LEFT(@Par, 6) + '-' + RIGHT('0' + CAST(@Int as varchar(2)), 2)
set @w_o = @return
return(@w_o)
June 8, 2005 at 2:17 pm
Return must be an int
once you set the output parameter, it "returns" to the caller automatically
CREATE PROCEDURE dbo.sptest
@Par as varchar(9),
@w_o char(9) out
AS
Declare @Int as int
set @Int = CAST(right(@Par, 2) AS INT)
set @Int = @Int - 1
set w_o = LEFT(@Par, 6) + '-' + RIGHT('0' + CAST(@Int as varchar(2)), 2)
that's all you need.
June 8, 2005 at 2:40 pm
Yes! It works now.
Thank you for your time and patience.
Have a great day!
June 8, 2005 at 2:47 pm
HTH.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply