2

I'm currently using this code to select from 2 tables using a stored procedure.

DECLARE @now as varchar(26)
set @now = CONVERT(VARCHAR(26), SYSDATETIME(), 9)

--Select 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -1, GETDATE()),102), '.', '_') 

DECLARE @table1 AS varchar(12)
SET @table1  = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -0, GETDATE()),102), '.', '_')  

DECLARE @table2 AS varchar(12)
SET @table2  = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -1, GETDATE()),102), '.', '_')  

DECLARE @table3 AS varchar(12)
SET @table3  = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -2, GETDATE()),102), '.', '_')  

DECLARE @SQLQuery AS varchar(MAX)
SET @SQLQuery = '

insert into SMDTemp (QDATE, UPC2, AWEEK, WEEKRANGE, MOVEMENT, COST, RTL, LBS, GP) 
SELECT  
''' + @now + ''' as QDATE, UPC2,MAX(RTG_WKD.AWEEK),RTG_WKD.WEEKRANGE, SUM(SMVNUM) AS MOVEMENT, ROUND(AVG(SMVCOST), 2) AS COST, ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS RTL, CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS LBS, (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL ELSE round(AVG(smvsugs / smvsugns), 2) END AS GP 
FROM ' + @table1 +'  INNER JOIN RTG_WKD ON SMVDATE = RTG_WKD.WKDDATE6 
where UPC2 = ''' + @search + '''
and SMVStore = ''' + @store + '''
GROUP BY RTG_WKD.WEEKRANGE, UPC2
union all 

SELECT  
''' + @now + ''' as QDATE, UPC2,MAX(RTG_WKD.AWEEK),RTG_WKD.WEEKRANGE, SUM(SMVNUM) AS MOVEMENT, ROUND(AVG(SMVCOST), 2) AS COST, ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS RTL, CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS LBS, (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL ELSE round(AVG(smvsugs / smvsugns), 2) END AS GP 
FROM ' + @table2 +'  INNER JOIN RTG_WKD ON SMVDATE = RTG_WKD.WKDDATE6 
where UPC2 = ''' + @search + '''
and SMVStore = ''' + @store + '''
GROUP BY RTG_WKD.WEEKRANGE,  UPC2

Order by weekrange desc
'

EXECUTE(@SQLQuery)

SELECT * from SMDTemp
where QDATE = @now
and UPC2 = @search


delete from SMDTemp
where QDATE = @now

I now need to add a 3rd table, and potentially up to 12 tables. The problem is when I add a 3rd table with

UNION ALL 

SELECT  
''' + @now + ''' as QDATE, UPC2,MAX(RTG_WKD.AWEEK),RTG_WKD.WEEKRANGE, SUM(SMVNUM) AS MOVEMENT, ROUND(AVG(SMVCOST), 2) AS COST, ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS RTL, CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS LBS, (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL ELSE round(AVG(smvsugs / smvsugns), 2) END AS GP 
FROM ' + @table3 +'  INNER JOIN RTG_WKD ON SMVDATE = RTG_WKD.WKDDATE6 
where UPC2 = ''' + @search + '''
and SMVStore = ''' + @store + '''
GROUP BY RTG_WKD.WEEKRANGE,  UPC2

I can execute the query in SQL Server Management Studio but when I run it through a stored procedure the request times out.

Is there a cleaner or more efficient way of doing this? Sample code will suffice, I can plug the data in myself.

  • There are some flaws here. First use of non parametric dynamic queryes makes the engine recompiles it at each time (since it's an efective different query every time). Second populate a possible large temp table just to filter it later. You can try just insert already filtered rows – jean Nov 12 '15 at 16:17
  • @jean Even if the two WHERE filters are parameterized, the table names can't be. It does not seem like this type of query would be run often enough to make a difference in that respect. Also, the INSERT...SELECT queries are already inserting just the filtered rows via where UPC2 = ''' + @search + ''' -- I am not sure why that same filter was used in the test query at the end. – Solomon Rutzky Nov 12 '15 at 16:30
  • You got a point. Anyway it's hard to tell why OP did things this way, I myself can try to not use dynamic queries and temp tables at all. But we cannot tell that was the only way due lack of context – jean Nov 12 '15 at 16:35
  • @jean i did it this way because its what i got to work to achieve what i needed after hours of googling. basically i need to select from 3 tables where the table name is SMD_yyyy_mm and query some stuff. i know its a messy bunch of code but im always looking to improve. im open to suggestions – lizzy91 Nov 12 '15 at 17:02
  • 1
    i can. im playing with he code you provided below, that was alot more than i was expecting. really appreciate you taking the time to help me out – lizzy91 Nov 12 '15 at 17:42
1

First, I would remove the Order by weekrange desc as it is unnecessary (since you aren't also using TOP() in any of the SELECT queries.

Also, why use UNION ALL in the first place? Why not just have these be 3 - 12 separate INSERT INTO table SELECT .... queries?

And, given that the only thing changing in each query is the name of the table, and that name follows a pattern, the dynamic query can be templated and then just loop through the number of months you need.

The following should do what you want, with each INSERT...SELECT from each separate SMD_ table being a separate query / transaction:

DECLARE @Search NVARCHAR(10) = N'_search_', -- just to get the test code working
       @Store NVARCHAR(10) = N'_store_'; -- just to get the test code working

DECLARE @Now DATETIME2;
SET @Now = SYSDATETIME();

DECLARE @QueryTemplate AS NVARCHAR(MAX)
SET @QueryTemplate = N'
INSERT INTO SMDTemp (QDATE, UPC2, AWEEK, WEEKRANGE, MOVEMENT, COST, RTL, LBS, GP) 
  SELECT @Now_tmp AS [QDATE], UPC2, MAX(RTG_WKD.AWEEK), RTG_WKD.WEEKRANGE,
         SUM(SMVNUM) AS [MOVEMENT], ROUND(AVG(SMVCOST), 2) AS [COST],
         ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS [RTL],
         CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS [LBS],
         (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE
             WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL
              ELSE round(AVG(smvsugs / smvsugns), 2) END AS [GP]
  FROM SMD_{{TableDate}}
  INNER JOIN RTG_WKD
          ON SMVDATE = RTG_WKD.WKDDATE6
  WHERE UPC2 = @Search_tmp
  AND   SMVStore = @Store_tmp
  GROUP BY RTG_WKD.WEEKRANGE, UPC2;
';

DECLARE @SQLQuery NVARCHAR(MAX);
SET @SQLQuery = N'';

DECLARE @MonthsBack INT;
SET @MonthsBack = 0;

WHILE (@MonthsBack > -12)
BEGIN
  SET @SQLQuery = @SQLQuery + REPLACE(@QueryTemplate,
                                      N'{{TableDate}}',
                                      REPLACE(CONVERT(NVARCHAR(15),
                                                      DATEADD(MONTH,
                                                              @MonthsBack,
                                                              GETDATE()),
                                                      102),
                                              N'.',
                                              N'_')
                                              )
                            + NCHAR(0x0D) + NCHAR(0x0A);

  SET @MonthsBack = @MonthsBack - 1;
END;

PRINT @SQLQuery; -- debug

EXEC sp_executesql
  @SQLQuery, -- define the query
  N'@Now_tmp DATETIME2, @Search_tmp VARCHAR(100), @Store_tmp VARCHAR(100)', --param list
  @Now_tmp = @Now, -- pass in parameter: param_name = local_variable_name
  @Search_tmp = @Search, -- pass in parameter: param_name = local_variable_name
  @Store_tmp = @Store; -- pass in parameter: param_name = local_variable_name

This query was originally using EXEC(@SQLQuery) but has been parameterized to use sp_executesql (as mentioned by @Jean in a comment on the Question).

|improve this answer|||||
  • fixed the formatting. i dont know much about sql. the code im using was fabricated from hours of googling. it was just what worked at the time. which is why im here now looking for a cleaner way. can you offer a snippet of how to use INSERT INTO table SELECT – lizzy91 Nov 12 '15 at 16:18
  • @lizzy91 Your queries are already using the INSERT...SELECT syntax. I was just suggesting that the SELECT part of it didn't need to be the entire query in one shot, combined via UNION ALL. I just posted example code that works in terms of producing the dynamic query, but you should edit (especially for the @search and @store variables) and test. – Solomon Rutzky Nov 12 '15 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.