CODEWITHSUNDEEP
sqlsql-serversyntax-error
Nikhil
Nikhil

Reputation: 31

Pass query variable to a MSDB.dbo.sp_Send_DBMail

I have written a SQL query. But its giving me an error.

DECLARE @Delimiter Char(1)
SET @Delimiter = CHAR(9)
EXEC MSDB.dbo.sp_Send_DBMail
@profile_name = 'K2MailSetup',
@Recipients='[email protected]',
@Subject='Extraction Report',
@Body='Hi',
@Query='set nocount on;
Select Coalesce(replace(replace(A.[type], char(10), ''''), char(13), ''''),'''') as Type,
 try_convert(xml, col).value(''(/collection/object/fields/field/value)[1]'', ''varchar(3)'') 
from (select col= Coalesce(replace(replace(A.[business_line], char(10), ''''), char(13), ''''),'''')
    FROM [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS] A'

Error states

Msg 102, Level 15, State 1, Line
Incorrect syntax near '/'.

Upvotes: 0

Views: 599

Answers (2)

Dale K
Dale K

Reputation: 27202

The trick to dynamic SQL is to make sure the SQL works before trying to make it dynamic.

Your query needs multiple tweaks before it works as a basic SQL query:

set nocount on;

select coalesce(replace(replace(A.[type], char(10), ''''), char(13), ''''),'''') as Type
  , try_convert(xml, col).value('(/collection/object/fields/field/value)[1]', 'varchar(3)') 
from (
    select col = coalesce(replace(replace([business_line], char(10), ''''), char(13), ''''),''''), Type
    FROM [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS]
) A;

Then and only then, convert to dynamic SQL by doubling all single quotes:

declare @Query as nvarchar(1000) = 'set nocount on;

select coalesce(replace(replace(A.[type], char(10), ''''), char(13), ''''),'''') as Type
  , try_convert(xml, col).value(''(/collection/object/fields/field/value)[1]'', ''varchar(3)'') 
from (
    select col = coalesce(replace(replace([business_line], char(10), ''''), char(13), ''''),''''), Type
    FROM [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS]
) A;'

exec(@Query);

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

I found 2 issues here.

  1. your subquery is not properly closed
  2. your quotes are not property terminated
Declare @Query as nvarchar(1000) 
Set @Query='set nocount on; 
            set QUOTED_IDENTIFIER on;
            Select try_convert(xml, Coalesce(replace(replace(A.[business_line], char(10), '''''''')
                        , char(13), ''''''''),''''''''))
                        .value(''(/collection/object/fields/field/value)[1]'', ''varchar(3)'') 
                , Coalesce(replace(replace(A.[type], char(10), ''''''''), char(13), ''''''''),'''''''') as [Type]
                from [EU_OTH_REG].[dbo].[TBL_EU_OTH_TXN_REG_RSDS] A;'

Upvotes: 1

Related Questions