Syntax error using XP_SENDMAIL 1

[jadams0173]

I have changed an xp_sendmail procedure and now I get an error that says:

Server: Msg 245, Level 16, State 1, Line 5
Syntax error converting the nvarchar value 'select PicklistID,SectorID,MO,Assembly,LineCount,DelPoint,Deltime
from WaitingForPicklist Where MyID = ' to a column of data type int.

I'm using the @qry to return the data in an email.

Declare @qry nVARCHAR(1000), @MyID int

set @myid = 1

set @qry = N'select PicklistID,SectorID,MO,Assembly,LineCount,DelPoint,Deltime
		 from WaitingForPicklist Where MyID = '+ @MyID 

exec @qry

 

[bborissov]

Declare @qry nVARCHAR(1000), @MyID int

set @myid = 1

set @qry = N'select PicklistID,SectorID,MO,Assembly,LineCount,DelPoint,Deltime
         from WaitingForPicklist Where MyID = '''+ CAST(@MyID as nvarchar(200))+''''

exec @qry

NOT TESTED!!!
You can't add an integer value (@MyID) to a nvarchar.
You must cast it first to appropriate type.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jadams0173]

Hey bborissov,

Thanks for the reply. I get this error with your method. I know it was untested by you. I'll work with it a little.

Server: Msg 2812, Level 16, State 62, Line 9
Could not find stored procedure 'select PicklistID,SectorID,MO,Assembly,LineCount,DelPoint,Deltime
from WaitingForPicklist Where MyID = '1''.

 

[bborissov]

OOPS!!!
There should be NO single quotes (and there SHOULD be brackets):

Declare @qry nVARCHAR(1000), @MyID int

set @myid = 1

set @qry = N'select PicklistID,SectorID,MO,Assembly,LineCount,DelPoint,Deltime
         from WaitingForPicklist Where MyID = '+ CAST(@MyID as nvarchar(200))

exec (@qry)

Again NOT TESTED!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jadams0173]

Yep. That was it. Thanks.

 

[markros]

Don't use such construct in your code.

See

Benefits of sp_ExecuteSQL
Avoid Conversions