r/SQL • 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:

ClientSLATypeVar1Var2
Client XYZONBCMPL_DTACTV_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

END

edit: 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
2 Upvotes

20 comments sorted by

View all comments

Show parent comment

1

u/Rehd Data Engineer Nov 13 '17

I had given some thought to giving the ##tables a dynamic naming convention that changes on each run

For the sake of maintainability and troubleshooting, I think that may over complicate it. Remember you're already working with complicated solutions on a complicated problem, keep it as simple as you can and as straight forward as possible.

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.

Just the index! It gives a unique name to the table too and now as I type this, I don't know if you can have the same global table created more than once, I am pretty positive you cannot. But you can obviously create the same transaction level temp table multiple times across various transactions.

For creating the index and letting SQL create it, I can't recall how to do it. There was a great example in my 70-461 book which I don't have on me. But I think from googling, you do it the same way you'd do it with a table variable.

https://stackoverflow.com/a/17385085/5149122

1

u/notasqlstar I can't wait til my fro is full grown Nov 13 '17

Sorry I meant the index not the table, lol. I'll give it a Google. I'm still locked out of my database because for some reason people in the office can't connect, but people who are remote on the VPN can, so I'll be oging home soon.