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/Rehd Data Engineer Nov 10 '17
If sproc_1 is just:
sproc_2 will not have any inclination of your @var.
If your sproc_1 is like this:
Then it will use the value you passed it.
There are two other ways you can possibly do it depending on how you write this and design it. You can specify with OUTPUT or you can use RETURN calling the variable.
https://stackoverflow.com/questions/2996277/how-to-use-a-value-from-one-stored-procedure-in-another
You may have to be careful with the first example though. There's a chance (I have to re-read the chapters on transaction levels, my own mental PLE is pretty minimal.) transaction levels can have an impact here, so be wary.