SQL Server – Cleaner Way to Select from Multiple Tables

dynamic-sqlsql serversql-server-2008

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.

Best Answer

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).

Related Question