1

I have the task of re-factoring a SQL script that performs a lot of unions from different tables. These tables have similar information, so the script queries the same columns as in each table, except that the table names are different. For example:

customerTransaction2011_Tbl

|ID | T.ID | Amount |
|---|------|--------|

CustomerTransaction2012_Tbl

|ID | T.ID | Amount |
|---|------|--------|

CustomerTransaction2013_Tbl

|ID | T.ID | Amount |
|---|------|--------|

The SQL script I have to re-factor is below:

Select ID, Sum(Amount), '2011' as [Year]
into #Tbl_threeYear
From CustomerTransaction2011_Tbl
union all
Select ID, Sum(Amount), '2012' as [Year]
From CustomerTransaction2012_Tbl
union all
Select ID, Sum(Amount), '2013' as [Year]
From CustomerTransaction2013_Tbl;

To re-factor this, I had thought of using Dynamic SQL within a stored procedure, so that I could pass the years (2011, 2012, 2013) as parameters to the stored procedure, which would append these to the Dynamic SQL string.

But turns out I can't be given permission to create a stored procedure.

So 2 questions, please:

If I simply make use of Dynamic SQL like so

declare @sqlmain as varchar(500);
declare @tblPart as varchar(100);
set @tblPart = '2011';
set @sqlmain = 'select ID, sum(Amount) from customerTransaction' + @tblPart + '_Tbl;'

How could I perform a union over the results from exec(sqlmain) while simply changing the @tblPart variable - must be possible somehow?

Also, would you rather another way of re-factoring such a script? Since this is my first time re-factoring, I would more than welcome any suggestions/criticism. Perhaps anybody could suggest how to re-factor in basic SQL?

This is all being done in SQL Server 2005.

Much appreciated.

  • What's the objective of the refactoring? – Pieter B Nov 4 '14 at 8:10
  • @PieterB, for this particular query, it is to generalise the query that could be reused with parameters. So basically shortening it, and also permitting re-use for different variables. But I would be glad even if a re-factored query not aiming at this objective is posted. – info_seekeR Nov 4 '14 at 8:18
  • Do you have permission to edit the existing stored procedure? – James Anderson Nov 4 '14 at 9:09
  • @JamesAnderson, thanks for the edits. No stored procedures are being used at the moment, neither do I have any permission to write a new proc, unfortunately. – info_seekeR Nov 4 '14 at 9:12
  • So are the tables that this query needs to read from changing a lot? How often will you be running this query? How many tables are usually in the query? – James Anderson Nov 4 '14 at 9:16
2

This might help you to create simple dynamic query for partitioned tables using COALESCE function of SQL Server

Step1: Create function to split tokens

CREATE FUNCTION [dbo].[fnSplit]
(
@List VARCHAR(8000),
@Delimiter CHAR(1) = ','
)
RETURNS @Temp1 TABLE
(
ItemId INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY,
Item VARCHAR(8000) NULL
)
AS BEGIN

DECLARE @item VARCHAR(4000),
@iPos INT

SET @Delimiter = ISNULL(@Delimiter, ',')
SET @List = RTRIM(LTRIM(@List))

-- check for final delimiter
IF RIGHT(@List, 1) <> @Delimiter 
-- append final delimiter
SELECT @List = @List + @Delimiter

-- get position of first element
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)

WHILE @iPos > 0
BEGIN
-- get item
SELECT @item = LTRIM(RTRIM(SUBSTRING(@List, 1, @iPos - 1)))
IF @@ERROR <> 0 
BREAK
-- remove item form list
SELECT @List = SUBSTRING(@List, @iPos + 1, LEN(@List) - @iPos + 1)
IF @@ERROR <> 0 
BREAK
-- insert item
INSERT @Temp1
VALUES ( @item )
IF @@ERROR <> 0 
BREAK
-- get position pf next item
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)
IF @@ERROR <> 0 
BREAK
END
RETURN
END


GO

Step 2: Set parameter value @varyears and execute query

DECLARE @varyears VARCHAR(200)
SET @varyears = '2012, 2011, 2013 ,2014'


DECLARE @combinedString VARCHAR(MAX)

SELECT  @combinedString = COALESCE(@combinedString + ' UNION ALL ', '')
        + CHAR(13) + ' Select ID, sum(amount) TotalAmount, ''' + item
        + ''' as [Year] FROM CustomerTransaction' + item
        + '_tbl  group by ID ' + CHAR(13) --group by clause added to your query for using aggregate function 
FROM    dbo.fnSplit(@varyears, ',')


EXEC ( @combinedString )

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.