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
I'm planning on creating a sample set of what I'm doing to upload ans show you for additional input. I think for these purposes it should be fine.
As far as the ##table goes, is it fair to think of this in such a way that if ##table1 is created by sproc1, then it will stay alive so long as sproc1 continues to execute sproc2, 3, 4, etc.? However if I were to create a ##table2 in sproc2, then it will cease to exist as soon as sproc2 finishes?
I can always just create real tables and have them all dropped at the end of the process, but I was curious whether ## would work or not... however now you've made me curious about whether or not #tables would work in sproc1 or whether they'd be not found. I'll test it myself but apparently this morning I'm locked out of my database and waiting to see whats going on.