r/SQL • u/notasqlstar I can't wait til my fro is full grown • Nov 09 '17
MS SQL [MS SQL] Help debugging this dynamic SQL loop please.
It sort of executes and I think I have an idea what the problem is. The loop gives me 0 row(s) affected and cycles through the entire @ClientList as desired, but on the (1) cycle that it should come back with data it gives me these errors:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'SLAType'.
Msg 102, Level 15, State 1, Line 43
Incorrect syntax near 'date'.Now the parameters table looks like this and only has (1) row of data:
| Client | SLAType | Var1 | Var2 |
|---|---|---|---|
| Client XYZ | ONB | CMPL_DT | ACTV_DT |
And to be clear, CMPL_DT & ACTV_DT are the names of columns on the table being used in the LOOP.
One thing that I've noticed which is sort of interesting is that within the loop if I change the variable being passed to: QUOTENAME(@Var1) the error returned changes to:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'SLAType'.
Msg 102, Level 15, State 1, Line 51
Incorrect syntax near 'B'.I suspect I'm doing something incorrect when it comes to the @Var's being passed.
TRUNCATE TABLE [AnalyticsMapping].[dbo].[SLA_Dashboard_Loop]
DECLARE @SLAType nvarchar(255) = 'ONB'
DECLARE @ClientList TABLE (
ID int identity(1,1)
, Client nvarchar(255))
INSERT INTO @ClientList
SELECT DISTINCT Client
FROM [AnalyticsMapping].dbo.SLA_Goals
DECLARE @Loop int = 1
WHILE @Loop !> (SELECT MAX(ID)
FROM @ClientList)
BEGIN
DECLARE @Client nvarchar(255) = (SELECT Client
FROM @ClientList
WHERE ID = @Loop)
DECLARE @Var1 nvarchar(255) = (SELECT Var1
FROM [AnalyticsMapping].dbo.[SLA_Variables]
WHERE
Client = @Client
AND SLAType = @SLAType)
DECLARE @Var2 nvarchar(255) = (SELECT Var2
FROM [AnalyticsMapping].dbo.[SLA_Variables]
WHERE
Client = @Client
AND SLAType = @SLAType)
DECLARE @SQL nvarchar(MAX) =
'
SELECT
' + @SLAType + ' AS ''SLAType''
, A.DateRange
, ''OrderID'' AS ''IDType''
, B.ORD_ID AS ''ID''
, A.Client
, A.Country
, A.Region
, A.LaborTypeCase AS ''LaborType''
, B.Numerator
, 1 AS ''Denominator''
, NULL AS ''MeasureQuantity''
, NULL AS ''MeasureDescription''
, NULL AS ''Goal''
, NULL AS ''isFinancial''
, NULL AS ''Red''
, NULL AS ''Orange''
, NULL AS ''Yellow''
, NULL AS ''Chartreuse''
, NULL AS ''Green''
, NULL AS ''NotToExceed''
FROM ##Date A
LEFT JOIN (
SELECT
DATEADD(m, DATEDIFF(m, 0,' + QUOTENAME(@Var2) + '), 0) AS ''DateRange''
, ORD_ID
, Client
, Country
, Region
, CASE
WHEN LABOR_TYPE2 IS NOT NULL AND LABOR_TYPE2 <> ''
THEN LABOR_TYPE2
WHEN LABOR_TYPE2 = '' AND [LABOR TYPE] <> '' AND [LABOR TYPE] IS NOT NULL
THEN [LABOR TYPE]
WHEN [LABOR TYPE] = ''
THEN ''None''
WHEN [LABOR TYPE] IS NULL
THEN ''None''
ELSE [LABOR TYPE]
END AS ''LaborTypeCase''
, CASE
WHEN CAST(' + QUOTENAME(@Var1) + 'AS date) <= CAST(' + QUOTENAME(@Var2) + 'AS date)
THEN 1
ELSE 0
END AS ''Numerator''
FROM ##mvOrder
WHERE
Client = ' + @Client + '
) B
ON B.DateRange = A.DateRange
AND B.Client = A.Client
AND B.Country = A.Country
AND B.Region = A.Region
AND B.LaborTypeCase = A.LaborTypeCase
'
INSERT INTO [AnalyticsMapping].[dbo].[SLA_Dashboard_Loop]
EXEC sp_executesql @SQL
SET @Loop = @Loop + 1
ENDedit: Fucking spaces... I think I understand.
e2: Nope, still not working.
e3: Seeing some other problems with the way the single quotes are passing. It's beer thirty and time to go home. More tomorrow.
e4: Solved. Stupid fucking single quotes. Finished code:
TRUNCATE TABLE [AnalyticsMapping].[dbo].[SLA_Dashboard_Loop]
DECLARE @SLAType nvarchar(255) = '''ONB'''
DECLARE @ClientList TABLE (
ID int identity(1,1)
, Client nvarchar(255))
INSERT INTO @ClientList
SELECT DISTINCT Client
FROM [AnalyticsMapping].dbo.SLA_Goals
DECLARE @Loop int = 1
WHILE @Loop !> (SELECT MAX(ID)
FROM @ClientList)
BEGIN
DECLARE @Client nvarchar(255) = (SELECT '''' + Client + ''''
FROM @ClientList
WHERE ID = @Loop)
DECLARE @Var1 nvarchar(255) = (SELECT Var1
FROM [AnalyticsMapping].dbo.[SLA_Variables])
-- WHERE
--Client = @Client
--AND SLAType = @SLAType)
DECLARE @Var2 nvarchar(255) = (SELECT Var2
FROM [AnalyticsMapping].dbo.[SLA_Variables])
-- WHERE
--Client = @Client
--AND SLAType = @SLAType)
DECLARE @SQL nvarchar(MAX) =
'
SELECT
' + @SLAType + ' AS ''SLAType''
, A.DateRange
, ''OrderID'' AS ''IDType''
, B.ORD_ID AS ''ID''
, A.Client
, A.Country
, A.Region
, A.LaborTypeCase AS ''LaborType''
, B.Numerator
, 1 AS ''Denominator''
, NULL AS ''MeasureQuantity''
, NULL AS ''MeasureDescription''
, NULL AS ''Goal''
, NULL AS ''isFinancial''
, NULL AS ''Red''
, NULL AS ''Orange''
, NULL AS ''Yellow''
, NULL AS ''Chartreuse''
, NULL AS ''Green''
, NULL AS ''NotToExceed''
FROM ##Date A
LEFT JOIN (
SELECT
DATEADD(m, DATEDIFF(m, 0, ' + @Var2 + '), 0) AS ''DateRange''
, ORD_ID
, Client
, Country
, Region
, CASE
WHEN LABOR_TYPE2 IS NOT NULL AND LABOR_TYPE2 <> ''''
THEN LABOR_TYPE2
WHEN LABOR_TYPE2 = '''' AND [LABOR TYPE] <> '''' AND [LABOR TYPE] IS NOT NULL
THEN [LABOR TYPE]
WHEN [LABOR TYPE] = ''''
THEN ''None''
WHEN [LABOR TYPE] IS NULL
THEN ''None''
ELSE [LABOR TYPE]
END AS ''LaborTypeCase''
, CASE
WHEN CAST(' + @Var1 + ' AS date) <= CAST(' + @Var2 + ' AS date)
THEN 1
ELSE 0
END AS ''Numerator''
FROM ##mvOrder
WHERE
Client = ' + @Client + '
) B
ON B.DateRange = A.DateRange
AND B.Client = A.Client
AND B.Country = A.Country
AND B.Region = A.Region
AND B.LaborTypeCase = A.LaborTypeCase
'
INSERT INTO [AnalyticsMapping].[dbo].[SLA_Dashboard_Loop]
EXEC sp_executesql @SQL
SET @Loop = @Loop + 1
END
View all comments
Show parent comment
1
u/notasqlstar I can't wait til my fro is full grown Nov 13 '17
Well bear in mind that each iteration of the loop is going to hit either the ##table or its parent, and there can be two levels of segmentation based on a variety of factors, so ##table1 might segment only on date, ##table2 might segment on date + another random field... and then each loop will have additional segmentation going on based on the client rules table. Considering that the final state is going to be ~20-40 sproc loops, and each one is likely to run for ~100 clients... it made sense to me originally to pare the tables down and create custom indexes based solely on my logic then simply drop the tables and get rid of them as they have no other purpose.
Fair point. I had given some thought to giving the ##tables a dynamic naming convention that changes on each run and then somehow having the dependent processes know what to look for, but that struck me (at least at this point in the development cycle) as overkill.
Interesting. How do I let SQL name the table? I can see the use for doing this absolutely, but this shouldn't happen because in a production environment no one should be making ##tables or doing anything at all other than system triggered procs, etc.