0

I have a stored procedure that uses a CTE, however the table to be consulted is variable (I have several tables with the same structure), so I try use dynamic SQL.

But it returns an error ("incorrect syntax near 'set'"), because I'm not referencing the CTE in query because dynamic SQL doesn't count (according Incorrect syntax after CTE)

So how do I resolve this? I have this query:

--Dynamic SQL
DECLARE @sql nvarchar(max)
--Extract hour value
DECLARE @hourDate DATETIME = DATEADD(hour,DATEDIFF(hour,0,@beginDate),0);
DECLARE @minutes INT = DATEPART(minute,@beginDate);
DECLARE @outmin INT;
DECLARE @startDate DATETIME;
DECLARE @interval INT=15;

--Verify Next Minute
IF @minutes <= 14 
    SET @outmin = 14;
ELSE IF @minutes > 14 AND @minutes <=29
    SET @outmin = 29;
ELSE IF @minutes > 29 AND @minutes <= 44
    SET @outmin = 44;
ELSE IF @minutes > 44 AND @minutes <=59
    SET @outmin = 59;

--Add Minute
SET @startDate = DATEADD(minute,@outmin,@hourDate);

;WITH Dates(Date) AS
(
    SELECT DATEADD(MINUTE, @interval, @StartDate) AS Date
    UNION ALL
    SELECT DATEADD(MINUTE, @interval, Date) AS Date
    FROM Dates
    WHERE Date < @endDate
)

set @sql='SELECT a.Date
FROM  Dates a
left join '+ @tableName +' b on a.Date=b.TimeStampFrame
where b.TimeStampFrame is null
ORDER BY a.Date ASC
option (maxrecursion 0)'

exec sp_executesql @sql

Thanks a lot!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
FelipeFonsecabh
  • 119
  • 13
  • 6
    With dynamic SQL its all or nothing, not half and half. So you have to put the entire query into dynamic SQL. – Dale K May 02 '23 at 20:23
  • 2
    Just shove that CTE into your @sql string and rerun. Remember that a CTE is mostly just syntactic sugar for a subquery and since you wouldn't leave a subquery out of your dynamic sql string, you can't leave your CTE out of it for the same reason. – JNevill May 02 '23 at 20:31
  • 1
    You can also put the insert the output of CTE into a #temporary table and then use that inside dynamic sql. – siggemannen May 02 '23 at 21:02
  • 2
    Aside... `;` is a statement terminator character, not a statement begininator (as some people are fond of saying). i.e.: don't use `;WITH`. _Although the semicolon isn't required for most statements in this version of SQL Server, it will be required in a future version._ ref: [Transact-SQL syntax conventions](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql) – AlwaysLearning May 02 '23 at 21:35
  • Two more comments in addition to the comments above: 1st: Make sure your `@tableName` variable is white-listed (this is a general advice for using dynamic SQL) and 2nd: Try to avoid using recursive CTEs for things that can be done using a calendar/tally table. For more information, read . Jeff Moden's [Hidden RBAR: Counting with Recursive CTE's](https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes) – Zohar Peled May 03 '23 at 07:49

0 Answers0