从sql server中的几个select语句返回特定值

如果选择中的选择有问题,以获得特定的结果,但我设法解决了这个问题,只是被另一个问题所阻止 .

我有一个代码,它是这样的:

IF EXISTS (SELECT * 
           FROM   bo 
           WHERE  nmdos LIKE '%preço%' 
                  AND datafinal = Dateadd(day, -6, CONVERT(DATE, Getdate()))) 
  EXEC msdb.dbo.Sp_send_dbmail 
    @profile_name = 'SQL Server - 2017', 
    @recipients = 'mail@mail.com', 
    @query = 'select * from bo     where nmdos like %preço%     and datafinal = DATEADD(day, -6, convert(date, GETDATE()))', 
    @subject = 'Encerramento Dossiert', 
    @attach_query_result_as_file = 1; 
ELSE 
  PRINT '0'

我得到的错误说:

消息22050,级别16,状态1,行0无法初始化sqlcmd库,错误号为-2147467259 .

我怎么能让这个工作,我哪里出错了?

回答(1)

2 years ago

你错过了@query变量的引用

@query = 'select * from bo where nmdos like %preço% and datafinal = DATEADD(day, -6,convert(date, GETDATE()))'

你需要这个

@query = 'select * from bo where nmdos like'+' ''%preço%'' ' +'and datafinal = DATEADD(day, -6, convert(date, GETDATE()))'

您可以看到此查询的不同之处:

DECLARE @query NVARCHAR(MAX)
SET @query = 'select * from bo where nmdos like'+' ''%preço%'' ' +'and datafinal = DATEADD(day, -6, convert(date, GETDATE()))' 

select @query