Query to convert query to HTML and send via email

Question

I have used a stored proc to convert a SQL Query to HTML formatted data. So that i can send that data in an email to someone using a sql job.

I use the following to send email: Query is the stored proc which converts SQL Query to HTML format

DECLARE @html nvarchar(MAX);
DECLARE @INPUT NVARCHAR(20)
SET @INPUT= ''LATE''
EXEC query @html = @html OUTPUT,  @query = N'select Studentsurname,absencecomment,Absencedate from absence where AbsenceDate=CAST(CAST(GETDATE() AS DATE) AS DATETIME and YearLevel in (11,12) and absencemeaning= @INPUT', @orderBy = N'ORDER BY StudentSurname';

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'PROFILE',
    @recipients = 'email@email',
    @subject = 'HTML email',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;

My issue is when i try execute the above part it gives me an error saying Incorrect syntax near 'Late'

Am i doing something wrong while passing the sql query to a stored procedure. Is there some other way to pass a sql query to a stored procedure?

The issue only happens when i use where clause. If my select statement ends with just "from table_name" then no issue comes up.

Answer 1

Try This

           DECLARE @html nvarchar(MAX);
           DECLARE @INPUT NVARCHAR(20)
           SET @INPUT= '''LATE''' --To add string with Quotes like this..
           Declare @query nvarchar(MAX) = N'select Studentsurname,absencecomment,Absencedate from absence where AbsenceDate=CAST(CAST(GETDATE() AS DATE) AS DATETIME and YearLevel in (11,12) and absencemeaning='+ @INPUT
           , @orderBy varchar(max) = N'ORDER BY StudentSurname';
           print @query --see difference near 'LATE'
           print @orderBy
           EXEC query @html = @html OUTPUT, @query , @orderBy