Syntax error converting the varchar value '06-09-0

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Syntax error converting the varchar value '06-09-0

Author

Topic

alexdcosta
Starting Member

12 Posts

Posted - 2006-09-24 : 17:44:11

I get the foll. error when executing the SP :
Server: Msg 245, Level 16, State 1, Procedure GenerateDefaultValue, Line 18
Syntax error converting the varchar value '06-09-002' to a column of data type int.

Declare @TableName varchar(50), @final varchar(50)
Select @TableName = 'RFP'
Exec GenerateDefaultValue @TableName, @final
Select @final

My SP is as follows:
create procedure dbo.GenerateDefaultValue
@TableName nvarchar(50),
@final varchar(50) OUTPUT
AS
BEGIN
DECLARE @yearmonth varchar(50)
DECLARE @defaultvalue int
DECLARE @padded varchar(50)
DECLARE @finished varchar(50)
declare @nowyearmonth datetime
select @nowyearmonth=nowDate from gettodaydate
select @yearmonth= SUBSTRING(CONVERT(VARCHAR(10), @nowyearmonth, 120), 3, 5)
SELECT @defaultvalue=ValueNow FROM DefaultValues where tablename=@tablename
set @defaultvalue=@defaultvalue+1
select @padded=RIGHT ('000' + CAST(@defaultvalue AS varchar(3)),3)
set @final=convert(varchar(50),@yearmonth)+'-'+convert(varchar(50),@padded)
set @finished=convert(varchar(50),@final)
return @finished
END

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-24 : 18:34:19

You are trying to retrun @finished which is a varchar, so an implicite conversion to int is made, and since @finished contains '06-09-002' you get the mentioned error.

-- The Heisenberg uncertainty principle also applies when debugging

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-24 : 20:52:04

Also, can you tell us what you are trying to do with DATE?

Madhivanan

Failing to plan is Planning to fail

alexdcosta
Starting Member

12 Posts

Posted - 2006-09-25 : 01:08:53

Hi ,
The output i am getting " 06-09-002" is exactly what i want. I have to generate a number in form of "year-month-proposalno" which is exactly what i am getting.

So any workaround for the error ??

Alex

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-25 : 01:23:24

quote:


Declare @TableName varchar(50), @final varchar(50)
Select @TableName = 'RFP'
Exec GenerateDefaultValue @TableName, @final OUTPUT
Select @final

My SP is as follows:
create procedure dbo.GenerateDefaultValue
@TableName nvarchar(50),
@final varchar(50) OUTPUT
AS
BEGIN
DECLARE @yearmonth varchar(50)
DECLARE @defaultvalue int
DECLARE @padded varchar(50)
DECLARE @finished varchar(50)
declare @nowyearmonth datetime
select @nowyearmonth=nowDate from gettodaydate
select @yearmonth= SUBSTRING(CONVERT(VARCHAR(10), @nowyearmonth, 120), 3, 5)
SELECT @defaultvalue=ValueNow FROM DefaultValues where tablename=@tablename
set @defaultvalue=@defaultvalue+1
select @padded=RIGHT ('000' + CAST(@defaultvalue AS varchar(3)),3)
set @final=convert(varchar(50),@yearmonth)+'-'+convert(varchar(50),@padded)
set @finished=convert(varchar(50),@final)
return @finished
END

Don't pass the value to the calling application or SP via the return. Pass the value back via OUTPUT parameter

alexdcosta
Starting Member

12 Posts

Posted - 2006-09-25 : 18:47:11

Thanks khtan,
Your solution did do the job for me.

Alex

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources