SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Need some help spotting Dynamic SQL error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MrSmallTime
Starting Member

United Kingdom
32 Posts

Posted - 07/12/2014 :  08:27:14  Show Profile  Reply with Quote
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

United Kingdom
32 Posts

Posted - 07/12/2014 :  09:51:45  Show Profile  Reply with Quote
Spotted it - Of course the WHERE clause is in the wrong place - Boss-eyed indeed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000