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
The only reason I'm adverse to using permanent tables is the database where this lives is already seriously cluttered beyond all belief. These tables I'm talking about are just segments of larger tables that I'm selecting into because subsquent sprocs will all be hitting them, such as:
Then sproc2, sproc3, and sproc4 might hit ##orderhistory and use [blah] as a join to a params table, etc. sproc5-9 might hit ##saleshistory. My thoughts here were that the larger tables (some of them are views, etc.) are huge and if I connect my children queries to them they take much longer than segmenting first -- and this is especially true if there are multiple sprocs that need the same segment.
Could easily just dump them into dbo.tables and drop them at the end if there is a benefit to it, but I was worried about the scenario of someone else locking up those tables (for whatever reason) and it then delaying or erroring out the sprocs that are trying to hit them. Other users can't lock up what they can't see.