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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy