SQL Server 2008 R2 | Query String Literal passed to parameter having parse issues

I am creating a sql stored procedure to run daily which will send an email alert if an error is identified by the query.  The issue is in the @query parameter. Intellisense is an annoying little tool. It keeps telling me there is a error in where clause for the @query parameter. It appears to be setup correctly to me. Can anyone please point me in the right direction on this.

USE [Enter DB Name Here]
GO
/****** Object:  StoredProcedure [dbo].[LP_Alert_Batch_Status_In_Error]    Script Date: 10/27/2015 09:26:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--  ======================================================================================================================
--	dbo.LP_Alert_Batch_Status_In_Error.sql
--
--
--	Description: This stored procedure checks batch status codes which are in Error(E) or Unknown(U) and
--				sends an email alert to Support Team.

--	Usage: EXEC dbo.LP_Alert_Batch_Status_In_Error @AsOfDate = '2015-10-27 00:00:00.000'
--
--
--	Date__________	Person_____________	Description _____________________________________________________________________
--	27 Oct 2015 AD	Asim Francis		Original module
--
--  ======================================================================================================================
--/*

	CREATE PROCEDURE [dbo].[LP_Alert_Batch_Status_In_Error]
	(
			@AsOfDate	DATETIME
	)

	AS
	SET NOCOUNT ON


/*
	==============================================================================
--	Declare parameters
	==============================================================================
*/
/*
	DECLARE
			@AsOfDate	DATETIME = NULL

*/

    DECLARE
			@StartDate	DATETIME
		,	@StopDate	   DATETIME


/*
	==============================================================================
--	SET the date values ( @AsOfDate, @StartDate, @EndDate )
	==============================================================================
*/

	--SET @AsOfDate = CONVERT( DATETIME, GETDATE(), 103 )

	IF @AsOfDate IS NULL OR @AsOfDate = ''
		BEGIN
			SET @AsOfDate	= CONVERT( DATETIME, GETDATE(), 103 )
			SET @StartDate	= CONVERT( DATETIME, @AsOfDate - 1, 103 )
			SET @StopDate	= CONVERT( DATETIME, @AsOfDate, 103 )
			SET @StartDate	= CONVERT( CHAR( 10 ), @StartDate, 103 ) + ' 00:00:00.000'
			SET @StopDate	= CONVERT( CHAR( 10 ), @StopDate, 103 ) + ' 23:59:59.997'
		END
	ELSE
		BEGIN
			SET @AsOfDate	= CONVERT( DATETIME, @AsOfDate, 103 )
			SET @StartDate	= CONVERT( DATETIME, @AsOfDate - 1, 103 )
			SET @StopDate	= CONVERT( DATETIME, @AsOfDate, 103 )
			SET @StartDate	= CONVERT( CHAR( 10 ), @StartDate, 103 ) + ' 00:00:00.000'
			SET @StopDate	= CONVERT( CHAR( 10 ), @StopDate, 103 ) + ' 23:59:59.997'
		END

        -- Testing Use Only
	-- SET @StartDate = '2015-01-01 00:00:00.000'
        -- SET @EndDate = '2015-12-31 23:59:59.997'


/*
	==============================================================================
	Drop all temp tables
	==============================================================================
*/
	IF OBJECT_ID( N'TempDb..#Results', N'U' ) IS NOT NULL
		DROP TABLE #Results


/*
	==============================================================================
--	Gather batches with an Error(E) or Unknown(U) status
	==============================================================================
*/

	SELECT
			*

	INTO	#Results

	FROM	dbo.T_BATCH_SUMMARY_RECORDS BSR
	WHERE	BSR.BatchStatus IN (			'E'		-- Error
									,	'U'		-- Unknown
									,	''		-- Blank
									,	' '		-- Space Filled
								)
       AND		BSR.BatchMessage NOT LIKE 'Simulated Batch Err%'
       AND		BSR.BatchMessage NOT LIKE '%ACCEPTED'
       AND		BSR.BatchMessage NOT LIKE 'ACCEPTED'
       AND		BSR.BatchMessage NOT LIKE 'Batch Was Re-Submitted%'
       AND		BSR.BatchMessage NOT IN ( 'Simulated Batch Errored')
       AND		BSR.BatchRunTime BETWEEN @BeginDate AND @EndDate

/*
	==============================================================================
--	Send and email alert if any results were found
	==============================================================================
*/

	IF ( SELECT COUNT( * ) FROM #Results ) > 0
	BEGIN
		--format body text for email
		DECLARE
				@body_txt			VARCHAR( 8000 )
			,	@ErrorCode		CHAR( 1 )
			,	@UnknownCode		CHAR( 1 )
			,	@SpaceCode		CHAR( 1 )
			,	@BlankCode		CHAR( 1 )
			,	@BeginDate		DATETIME
			,	@EndDate			DATETIME

		SET		@ErrorCode		= 'E'
		SET		@UnknownCode		= 'U'
		SET		@SpaceCode		= ' '
		SET		@BlankCode		= ''
		SET		@BeginDate		= @StartDate
		SET		@EndDate			= @StopDate
		SET		@body_txt			= 'Enter Body'

		--send email with flagged records as an attachment
		EXEC msdb.dbo.sp_send_dbmail
				@recipients		= 'helpMePlease@helpIsHere.org'
			,	@body			= @body_txt
			,	@subject			= 'Enter Subject'
			,	@query			= 'SELECT
											BSR.BatchID
										,   BSR.MerchantID
										,   MER.MerchantName
										,   BSR.BatchStatus
										,   TRS.VerbalDesc
										,   BSR.BatchRunTime
										,   BSR.BatchResponse
										,   BSR.BatchMessage
										,   BSR.TotalTransCount
										,   BSR.TotalDeposit


									FROM		dbo.T_BATCH_SUMMARY_RECORDS	BSR
									LEFT JOIN	dbo.T_MERCHANT_INFO			MER	ON	BSR.MerchantID		= MER.MerchantID
																							AND	MER.ActiveAccount	= 1
									LEFT JOIN	dbo.T_REF_TRANSACTION_STATUS	TRS ON	BSR.BatchStatus		= TRS.LetterCode

									WHERE	BSR.BatchStatus IN (		''' + @ErrorCode + '''
																	,	''' + @UnknownCode + '''
																	,	''' + @SpaceCode + '''
																	,	''' + @BlankCode + '''
																)
									AND		BSR.BatchMessage NOT LIKE ''' + 'Simulated Batch Err%' + '''
									AND		BSR.BatchMessage NOT LIKE ''' + '%ACCEPTED' + '''
									AND		BSR.BatchMessage NOT LIKE ''' + 'ACCEPTED' + '''
									AND		BSR.BatchMessage NOT LIKE ''' + 'Batch Was Re-Submitted%' + '''
									AND		BSR.BatchMessage NOT IN ( ''' + 'Simulated Batch Errored' + ''' )
									AND		BSR.BatchRunTime BETWEEN @BeginDate AND @EndDate

									ORDER BY
											BSR.BatchID	  DESC
										,	BSR.BatchRunTime'
			,	@attach_query_result_as_file = 1
	END

/*
	==============================================================================
--	Deallocate the #Results table
	==============================================================================
*/
	IF OBJECT_ID( N'TempDb..#Results', N'U' ) IS NOT NULL
		DROP TABLE #Results

Open in new window


Sincerely,

Extremely Frustrated
is_staffAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Maybe it wants N'' for the nvarchar(max) parameter @query?
chaauCommented:
Ignore my previous comment. I think I know what the problem is. As far as I remember from the SQL Server 7 era you can't really use a expression as a parameter, i.e. if your SP is defined as :
CREATE PROCEDURE test(@t VARCHAR(10)) AS SELECT @t END

Open in new window

You can't call it like this:
EXEC test 'A' + 'B'

Open in new window

You need to declare a local variable, use the expression to build this variable and then pass it into the SP:
DECLARE @a VARCHAR(10)
SET @a = 'A' + 'B'
EXEC test @a

Open in new window

In your case you will need to create a local variable for the @query parameter, build it and then pass it to the SP
Found this link that confirms my comment
is_staffAuthor Commented:
The issue is in how the string is setup to be passed into the @query parameter.

EE2015.PNG
@query = 'select columns
                              from tables
                              where columns = ' + @anotherParm + 
                              ' and   columns = ' + @anotherParm +  
                              ' order by   columns'

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

chaauCommented:
It seems you did not understand what I was trying to tell you. I have fixed your code and attached it here
SQLQuery2.sql
is_staffAuthor Commented:
Thanks for your input. I was able to find a working solution to the issue by initializing the parameters again in the string query rather than trying to pass the values from one parameter to another for the dbmail function.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
is_staffAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for is_staff's comment #a41174945

for the following reason:

Solved the issue.
chaauCommented:
This is exactly what I have told the author a few days ago. I have also provided the fully working solution on which I have spent my personal time
chaauCommented:
I am objecting on the terms provided in my previous comment
is_staffAuthor Commented:
While your suggestion of assigning the string literal to another parm (@queryLocal) then reassigning this value to the functions parm (@query) may have been a solution to other issues it was not a solution for this specific problem. The datetime parms were being initialized outside of the string literal being assigned to the msdb.dbo.sp_send_dbmail function parameters (@query) through a concatenation process. This is what was causing the error I reported. Upon further review the most simple solution was to copy the datetime parms declaration statement into the string literal being passed to the functions @query parm which resolved the issue.
chaauCommented:
Not a problem. Good luck in programming
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.