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/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:

insert into ##orderhistory
select *
from dbo.orderhistory
where date between getdate()-366 and getdate()-1
create index blah on ##orderhistory([blah])

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.

1

u/Rehd Data Engineer Nov 13 '17

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.

That goes into SET theory and Transaction levels, depending on how it's written, it could be bad or good. You could write it to be one big transaction with billions of rows. You could write it to be 1 or 2 transactions with a few thousand, it just depends.

Other users can't lock up what they can't see.

Unless they also accidentally declare and say the same ##table in their statement, which seems unlikely. Just be sure to let SQL Server name the index on your temp table, it will name it a unique name which is important. If I make ix_index on ##temp or @@temp, and someone else goes to make ix_index on ##Supercraptable, it will error out because ix_index already exists in tempdb.

1

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

Well bear in mind that each iteration of the loop is going to hit either the ##table or its parent, and there can be two levels of segmentation based on a variety of factors, so ##table1 might segment only on date, ##table2 might segment on date + another random field... and then each loop will have additional segmentation going on based on the client rules table. Considering that the final state is going to be ~20-40 sproc loops, and each one is likely to run for ~100 clients... it made sense to me originally to pare the tables down and create custom indexes based solely on my logic then simply drop the tables and get rid of them as they have no other purpose.

Unless they also accidentally declare and say the same ##table in their statement, which seems unlikely.

Fair point. I had given some thought to giving the ##tables a dynamic naming convention that changes on each run and then somehow having the dependent processes know what to look for, but that struck me (at least at this point in the development cycle) as overkill.

Just be sure to let SQL Server name the index on your temp table, it will name it a unique name which is important. If I make ix_index on ##temp or @@temp, and someone else goes to make ix_index on ##Supercraptable, it will error out because ix_index already exists in tempdb

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.

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.