How to use a variable in a query with sp_send_dbmail?

Question

I have a problem when trying to send a variable from the @ query, this variable is sent by a trigger, the variable goes well with the procedure, the print and show what I send, the problem occurs when I try to take that variable to select not I take the variable, just take the value of what I write.

The mail is working fine without problems if you only write text. I also take data from a normal query, but if I include a variable fails.

I chose to put the variable in the select so '+ @ q +' and displays the error

Msg 102, Level 15, State 1, Procedure Facturacion_Tope, Line 29
Incorrect syntax near '+'.

and remove the single quote symbol + and is not a fixed value and variable

USE [sistemas]
GO
/****** Object:  StoredProcedure [dbo].[Facturacion_Tope]    Script Date: 11/15/2012 07:32:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Facturacion_Tope]
    @ENVIO INT 
AS
print @ENVIO
BEGIN

    SET NOCOUNT ON;
    Declare @q INT
    --here shows the value of consultation 
    print @ENVIO

    set @q = (select OID from sistemas..DATFACTUR_DINAMICA WHERE OID = @ENVIO)

    --here shows the value of consultation
    print @q

    EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'correo_sistemas',
      @recipients = '[email protected]',
      -- @execute_query_database = 'sistemas',
      @query = 'select * from sistemas..DATFACTUR_DINAMICA WHERE OID = "@q"',                       
      @query_attachment_filename = 'Consulta.txt',      
      -- @body = 'Caida en: '@query+'', 
      @body_format = 'HTML',
      @subject = 'Numero de factura';
END

This is the output of the stored procedure.

209 --prints well
209 - prints well
HAY VA --prints well
209 --prints well
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Doomed Error 14661 on line 504, Query execution failed: Msg 137, Level 15, State 2, Server CDPALWIN01\CDPALSQL02, Line 1
Must declare the scalar variable "@q".
Msg 3903, Level 16, State 1, Procedure DEVDATFAC, Line 180
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The statement has been terminated.

RichardTheKiwi · Accepted Answer · 2012-11-15 13:19:26Z

5

Set the @query parameter beforehand, and then use that as a simple variable rather than concatenating strings in the procedure parameter assignment.

 DECLARE @thequery nvarchar(max) = 'select * from sistemas..DATFACTUR_DINAMICA
                                    WHERE OID = ' + cast(@q as nvarchar(max));
 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'correo_sistemas',
    @recipients = '[email protected]',
   -- @execute_query_database = 'sistemas',
    @query = @thequery,                     
    @query_attachment_filename = 'Consulta.txt',      
  -- @body = 'Caida en: '@query+'', 
   @body_format = 'HTML',
    @subject = 'Numero de factura';

answered Nov 15, 2012 at 13:19

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1415633 Over a year ago

I have another problem regarding the issue

user1415633 Over a year ago

Msg 22050, Level 16, State 1, Line 0 Error formatting query, probably invalid parameters Doomed Error 14661 on line 504, Query execution failed: CONSECUTIVO PERIODO VALOR_NETO CONTRATO FECHA_INGRESO DOCUME_ALTERNO -------------- -------- ----------------- ----------- ----------------------- -------------- SGPR0000127661 201212 259153978.00 4 2012-11-20 17:25:55.600 SGPR127661 Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted. –

user1415633 Over a year ago

there is a process that remains stuck -- Session ID User Process Login Database Task State Command Application Wait Time (ms) Wait Type Wait Resource Blocked By Head Blocker Memory Use (KB) Host Name Workload Group 203 1 DgEmpresal master SUSPENDED SELECT SQLCMD 52734 LCK_M_S keylock hobtid=72057594045726720 dbid=7 id=lockae0df100 mode=X associatedObjectId=72057594045726720 287 16 CDPALWIN01 default –

Sergey Kalinichenko · Accepted Answer · 2017-10-18 18:46:01Z

-1

use single quotes to to print variable as static value.

declare @s_dt nvarchar(max)

set @s_dt = 'select * from database.dbo.table_name where col_name = ''' + CAST(cast(getdate() as DATE) as nvarchar(max)) + ''''

edited Oct 18, 2017 at 18:46

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

answered Oct 18, 2017 at 18:09

Adil

1

Collectives™ on Stack Overflow

How to use a variable in a query with sp_send_dbmail?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related