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
DECLARE @TESTString NVARCHAR(MAX)
SET @TESTString = CONCAT(
@TableName, ' AS r
@RelField, ' >= ', '''', @StartDate,'''', ' AND ',
@RelField, ' <= ', '''', @EndDate,'''',
On r.ReferralID = c. CnTestID')
EXECUTE sp_executesql @TESTString
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
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.