asked on

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

chaau

Maybe it wants N'' for the nvarchar(max) parameter @query?

chaau

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_staff

ASKER

The issue is in how the string is setup to be passed into the @query parameter.

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

Open in new window

chaau

It seems you did not understand what I was trying to tell you. I have fixed your code and attached it here
SQLQuery2.sql

ASKER CERTIFIED SOLUTION

is_staff

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

is_staff

ASKER

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.

chaau

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

chaau

I am objecting on the terms provided in my previous comment

is_staff

ASKER

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.

chaau

Not a problem. Good luck in programming