Error while dynamic sql - SQL Server Forums

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

Error while dynamic sql

Author

Topic

papillon2811
Starting Member

18 Posts

Posted - 2007-03-27 : 08:18:12

Hi while running my dynamic sql i am getting error

declare @SqlCmd varchar(8000)
declare @AID [numeric](3,0)
declare @MNumber [varchar](25)
declare @DOS [datetime]
declare @AccntName [varchar](25)
declare @FacName [varchar](25)

select @SqlCmd='select sl.TransactionID,sl.DateOfService,
sl.PerformingIN, sl.OrderI, sl.PerfI,
sl.Units
from ClTransaction ct inner join SLine sl
on ct.TransactionID=sl.TransactionID and ct.Seq=sl.Seq
inner join Acct at
on ct.AccountID = at.AccountID
inner join Facility fc
on fc.FacilityID = sl.FacilityID
where ct.AccountID = ('+@AID+')
AND ct.MedicareNo = ('+@MNumber+')
AND sl.DateOfService = ('+@DOS+')
AND at.Account = ('+@AccntName+')
AND fc.Facility = ('+@FacName+')
and ct.Seq = (select max(ct2.Seq) from ClTransaction ct2
where ct2.TransactionID=ct.TransactionID) order by ct.TransLogDate Desc'

exec (@SqlCmd)

Err:
Msg 8115, Level 16, State 6, Line 8
Arithmetic overflow error converting varchar to data type numeric.

Msg 241, Level 16, State 1, Line 8
Conversion failed when converting datetime from character string.

T.I.A

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-27 : 08:20:43

AND sl.DateOfService = ('+@DOS+')

What is the value in @DOS

suspect it should be

AND sl.DateOfService = '''+@DOS+'''

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-27 : 08:21:25

Follow these rules

1. Character and date values must be enclosed in a pair of single quotes
2. Numeric data should be converted to varchar when building dynamic sql text

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

papillon2811
Starting Member

18 Posts

Posted - 2007-03-27 : 08:24:34

hi,

where ct.AccountID = (CAST('+@AID+') as varchar(5))

and

In DOS it is date time 02/02/2006

how we present it in dynamic sql ??

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-27 : 08:29:21

No, it should be this way:

...
where ct.AccountID = (' + convert(varchar(10), @AID) + ')
...

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

papillon2811
Starting Member

18 Posts

Posted - 2007-03-27 : 08:32:05

hi... :)

what about datetime ??? :(

T.I.A

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-27 : 08:33:58

I think nr already answered it. Same goes for varchar columns.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-27 : 09:31:55

You do know that you have no need for dynamic sql here, right?

all you need is:


declare @AID [numeric](3,0)
declare @MNumber [varchar](25)
declare @DOS [datetime]
declare @AccntName [varchar](25)
declare @FacName [varchar](25)

select sl.TransactionID,sl.DateOfService,
  sl.PerformingIN, sl.OrderI, sl.PerfI,
  sl.Units
from 
  ClTransaction ct 
inner join 
  SLine sl on ct.TransactionID=sl.TransactionID and ct.Seq=sl.Seq
inner join 
  Acct at on ct.AccountID = at.AccountID
inner join 
  Facility fc on fc.FacilityID = sl.FacilityID
where 
  ct.AccountID = @AID AND 
  ct.MedicareNo = @MNumber AND
  sl.DateOfService = @DOS AND 
  at.Account = @AccntName AND 
  fc.Facility = @FacName and 
  ct.Seq = (select max(ct2.Seq) from ClTransaction ct2
            where ct2.TransactionID=ct.TransactionID) 
order by 
  ct.TransLogDate Desc

... and that's it. Why are you using dynamic sql when you can just execute the SELECT directly? it is making things more complicated, longer, and less efficient, and as you have seen, you have to deal with delimiting and converting the parameters and concatenating it all together.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources