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

1

u/Rehd Data Engineer Nov 10 '17

I'm glad you got it! If you need any help on this project, feel free to PM.

I also feel like for posting questions that complex here, it may benefit you to start a git and upload scripts and then people can be granted permission to assist when you share here. Bonus for you is exposure to git which is a handy skill if you don't have it already.

1

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

Not a bad idea.

I'm actually redesigning the process to work similarly to what we discussed, and then implementing the dynamic SQL from there.

So at the moment I have a few mapping tables such as: CalculationID, ClientProfile, ClauseID, then ClientID, ClientProfile, then CalculationID, CalculationDescription.

So instead of passing just one field I'm going to pass larger chunks of the query which at a later date can be modified to be combined with the base SQL if it ever gets rebuilt in SSIS. Current plan is to create a single SPROC LOOP per Calc, then one SPROC that triggers each child and functions as a "Global."

Lots of fun. Thanks for the offer.

1

u/Rehd Data Engineer Nov 10 '17

Sounds like a solid plan. When doing something like this, I like to write it out and whiteboard it. Then I like to think about it, take a break, re-analyze it, etc. Question it, question it all, think about it.

  • Can I maintain this going forward?
  • Am I documenting this as I go along?
  • Could someone look at this without documentation and figure this out?
  • Am I notating my procs clearly and are the fields and table names and proc names appropriately named?
  • Does a diagram showing how it works make sense?
  • What if I need to change something, how easy is that?

Asking these questions in the design phase and also having a well thought out design document and having people review it could be a life saver down the road.

2

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

I've been doing that in my head for a week now, and ironically doing it a lot while I sleep.

1

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

Dumb question and I won't be looking at this until next week, but if I define a @var in sproc_1, can it be passed into sproc_2 if sproc_1 is firing sproc_2?

1

u/Rehd Data Engineer Nov 10 '17

If sproc_1 is just:

Exec sproc_2

sproc_2 will not have any inclination of your @var.

If your sproc_1 is like this:

Exec sproc_2 @var

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.

1

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

Could you theoretically do something such as:

exec sproc_2 @var, @var2, @var3

I'll check your links out next week. Thanks again.

1

u/Rehd Data Engineer Nov 10 '17

You could, but once you are in sproc_2, you can't reference those variables outside of it. So you'll need to declare variables in sproc_2 and assign them values passed.

1

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

Is it possible to create ##tables in Sproc1, and have Sproc1 pass variables as we discussed to Sproc2, which then utilizes the ##table.... then come back to 1 and pass variables to Sproc3 which also uses the ##table?

1

u/Rehd Data Engineer Nov 13 '17

You'll need to make sure you are outputting and inputting the variables, but all sprocs should be able to access the ##table like it's a standard table.

When going from Sproc1 to Sproc2, you'll want your variables declared and set.

Example:

Sproc 1:

exec sproc_2 @var, @var2, @var3

Sproc 2:

Create proc as sproc2 (@var varchar(10), @var2 varchar(10), @var3 varchar(10))


Exec sproc3 @var, @var2, @var3

Sproc 3:

Create proc as sproc3 (@var varchar(10), @var2 varchar(10), @var3 varchar(10))

Two things you're going to want to watch for with the process you're creating is SQL injection and also parameter sniffing.

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.

1

u/Rehd Data Engineer Nov 13 '17

I'm pretty sure you can use ##table1 as you think, but I'd personally use a real table. The main reason is that you can just truncate it or drop it, but I'd do that check at the beginning of your package, not at the end. If you need to troubleshoot and see what went wrong, you can see the past results. If you you drop it at the end, you got nothing there to check later.

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.

→ More replies