Must Declare a Scalar Variable Error - Solved | How to insert apostrophe in SQL Query String

You have to use double apostrophe to insert a single apostrophe in SQL Query String. Below is the sample query where it tries to send mail, in which we have to write a SQL query to fetch the details.

In the SQL query we are using a scalar variable, If we are not converting that in to string format SQL throws an error saying "Must declare a scalar variable" and you cant escape from that error

Lets say, we are using a date time value in where condition then its need to be under apostrophes, when we get the value from scalar variable then that value should be converted in to char to form complete SQL query string.

Instead of doing all patch-ups, you can easily convert that value to character and insert in to the query as below.. 


DECLARE @scalarVariable DATETIME

SET @scalarVariable  = (SELECT SomeValue FROM SomeTable )


DECLARE @tab CHAR(1)= CHAR(9);
DECLARE @fileName VARCHAR(256);
DECLARE @sql nvarchar(MAX);
DECLARE @subject nvarchar(MAX);

SET @fileName = 'SomeFile.xls';
SET @sql = N'SELECT * FROM SomeTable WHERE SomeColumn  >' + ''''+CONVERT(VARCHAR(24),@scalarVariable ,21)+'''';


SET @subject = 'Some Subject';

EXEC msdb.dbo.sp_send_dbmail
@profile_name='SomeProfile'
,@recipients='abcd@gmail.com'
,@subject= @subject
,@body='Some data have been changed'
,@body_format = 'Text'
,@query = @sql
,@query_result_separator =  @tab
,@attach_query_result_as_file = 1
,@query_attachment_filename= @fileName
,@query_result_width = 32767
,@query_result_no_padding = 1