syntax some where wrong confused ...

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

General SQL Server Forums

New to SQL Server Programming

syntax some where wrong confused ...

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-09-25 : 12:36:14

Hi All,

I need your help on urgent basis pls..

i m executing sql query but i am getting error

like

Msg 8114, Level 16, State 5, Line 20
Error converting data type varchar to numeric.

my syntax is

declare @SecMaxCodeID numeric (3,0)
declare @AccountID numeric (3,0)

set @AccountID = 187
set @SecMaxCodeID = 0

set @SecMaxCodeID = convert(decimal, convert (varchar(10), @AccountID) +
convert(varchar(25), max(convert(decimal, substring(convert(varchar(50), @SecMaxCodeID),
4,datalength(convert(varchar(50),@SecMaxCodeID))))) + 1))

select @SecMaxCodeID

T.I.A

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-25 : 12:56:32

Given these inputs:
set @AccountID = 187
set @SecMaxCodeID = 0

What should the result be?

Be One with the Optimizer
TG

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-09-25 : 12:59:09

I am getting error for 0 as input for @SecMaxCodeID

but for rest of values I m getting no err

T.I.A

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-25 : 13:04:21

I'm just trying to see what you expect from this expression. What is the expected result (for any inputs?)

Be One with the Optimizer
TG

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-09-25 : 13:07:47

its 1871

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 13:15:22

quote:
Originally posted by under2811

its 1871

what's the logic that you're trying to apply?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-25 : 13:17:24

Are you just trying to add 1 to the two concatenated values?


select convert(int, 
              convert(varchar(3), @AccountID) 
              + convert(varchar(3), @SecMaxCodeID)
            ) + 1

Be One with the Optimizer
TG

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-09-25 : 13:19:08

if it is SecMaxCodeID = 1871 then by this
it sud give SecMaxCodeID = 1872...like that but in some cases i m getting SecMaxCodeID null so i set it as isnull(SecMaxCodeID ,0)

that time i getting this error

T.I.A

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-09-25 : 13:25:19

select convert(int,
convert(varchar(50), @AccountID)
+ convert(varchar(50), @SecMaxCodeID)
) + 1

i tried for AccountID = 187
and SecMaxCodeID = 187254

I am getting err like
Msg 8115, Level 16, State 8, Line 13
Arithmetic overflow error converting int to data type numeric.

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-09-25 : 13:27:20

for value of SecMaxCodeID other than 0 (zero) no problem for me it works fine but when it comes to 0 then i m getting that err

T.I.A

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-25 : 13:56:59

The overflow is because you have your variables declared as numeric(3,0)

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-25 : 14:00:58

[code]
declare @SecMaxCodeID numeric (18,0)
declare @AccountID numeric (18,0)

set @AccountID = 187
set @SecMaxCodeID = 187254

select convert(int,
convert(varchar(50), @AccountID)
+ convert(varchar(50), coalesce(@SecMaxCodeID, 0))
) + 1

--output: 187187255

set @AccountID = 187
set @SecMaxCodeID = null

--output: 1871
[/code]

Be One with the Optimizer
TG

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-09-26 : 02:52:37

Thanks!!!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 02:57:01

What are the rules when @sexmaxcodeid equals 999?

set @AccountID = 187
set @SecMaxCodeID = 999

should the result be "1871000" ?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources