Need some help spotting Dynamic SQL error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Need some help spotting Dynamic SQL error

Author

Topic

MrSmallTime
Starting Member

32 Posts

Posted - 2014-07-12 : 08:27:14

I'm trying to write dynamic SQL for a SP to extract data from 2 joined tables. However I'm getting a couple of errors that I can't seem to resolve (maybe I'm just going boss-eyed). Or is it the case that joins cant be handle with dynamic SQL!

Here's the SP


ALTER Proc [dbo].[SpTEST]
	(
	@TableName AS VARCHAR(30)
	,@RelField AS VARCHAR(30)
	,@StartDate AS DATE
	,@EndDate AS DATE
	)
AS 
BEGIN
	DECLARE @TESTString NVARCHAR(MAX)

	SET @TESTString = CONCAT(
		'SELECT
			ReferralID
			,DateReceived
			,DateClosed
		FROM ',
			@TableName, ' AS r
		WHERE ',
			@RelField, ' >= ', '''', @StartDate,'''', ' AND ',
			@RelField, ' <= ', '''', @EndDate,'''',
'Left join(
		SELECT
			CnReferralID
			,CnTestID 
		FROM 
			TESTTABLE2
		)c
On r.ReferralID = c. CnTestID')


		EXECUTE sp_executesql @TESTString
	END

Here's The Execute


DECLARE	@return_value int

EXEC	@return_value = [dbo].[SpTEST]
		@TableName = N'TestTable1',
		@RelField = N'DateClosed',
		@StartDate = '2000-01-01',
		@EndDate = '2017-01-01'

SELECT	'Return Value' = @return_value

GO

And these are the error messages


Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'Left'.
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near 'c'.

(1 row(s) affected)

This has been driving me nuts for about 3 hours now.

MrSmallTime
Starting Member

32 Posts

Posted - 2014-07-12 : 09:51:45

Spotted it - Of course the WHERE clause is in the wrong place - Boss-eyed indeed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources