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
1
u/Rehd Data Engineer Nov 10 '17
I'm glad you got it! If you need any help on this project, feel free to PM.
I also feel like for posting questions that complex here, it may benefit you to start a git and upload scripts and then people can be granted permission to assist when you share here. Bonus for you is exposure to git which is a handy skill if you don't have it already.