XP_SENDMAIL and varibles 1

[roropacific]

Hello:
I am trying to construct an sql string that has varibles concatenated to it. I am running SQL 7.0 sp4 on windows 2000 server. Code is below
declare @yr as int
declare @mo as int
declare @dy as int
declare @total as int
declare @count as int
declare @pos as int
declare @sqlstr as varchar(2000)
set @yr =datepart(year,getdate())
set @mo =datepart(month,getdate())
set @dy =datepart(day,getdate())
if len(@dy)>1 set @count =2 else set @count=1
if @count =2 set @pos = 8 else set @pos = 7
--set @total = str(@yr,4,0) + str(@mo,2,0)+ str(@dy,@count,0)

set @total='20021220'
select upstrackingnbr from pciapp.dbo.samplessent where left(shipdate,@pos) =@total and shipperid like 'sam%dh%'
IF @@ROWCOUNT <> 0
set @sqlstr='select rtrim(name)as Customer ,rTrim(address)As Address,rtrim(city)as City,rtrim(zip)as Zip,rtrim(state)as State,left(rtrim(shipdate),'+ @pos +' )as Ship_Date,rtrim(upstrackingnbr)as Track_Number from pciapp.dbo.samplessent where left(shipdate,'+ @pos + ' ) = '+ @total +' and shipperid like &quot;sam%dh%&quot;'
exec master..xp_sendmail @recipients ='ron',
@query=@sqlstr,
@subject = 'Samples Sent For Dan',
@message= 'Open attachement in notepad and turn off word wrap. Do not reply to the message',
@attach_results = 'true' , @width = 250

however I get this error
Server: Msg 245, Level 16, State 1, Line 18
Syntax error converting the varchar value 'select rtrim(name)as Customer ,rTrim(address)As Address,rtrim(city)as City,rtrim(zip)as Zip,rtrim(state)as State,left(rtrim(shipdate),' to a column of data type int.
It seems that it is not concatenating properly

 

[tlbroadbent]

@pos and @total are INT variables and you are trying to concatenate with strings. I recommend the following.

Change the data type of @total to char(8). You use it as a string throughout the script so it should be a string. Convert @pos to an integer before concatenating. You can use the CAST, CONVERT or STR function. I've chosen to use STR.

set @sqlstr=
'select rtrim(name) As Customer, rtrim(address) As Address,
rtrim(city) As City, rtrim(zip) As Zip, rtrim(state) As State,
left(rtrim(shipdate),'+ str(@pos) +' ) As Ship_Date,
rtrim(upstrackingnbr) As Track_Number
from pciapp.dbo.samplessent
where left(shipdate,'+ str(@pos) + ') = '+ @total +' and shipperid like &quot;sam%dh%&quot;' Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[roropacific]

Thank you
My oversight.