1

I am trying to access a table that is recreated in a new database each quarter. The table name stays the same but a new database name is created. What I'm trying to achieve is using a select statement to include a variable that will look at a specific database with a range of Q317 to Q119. At the moment I have the select code set up but I have to reuse the same code but change the quarter for each database. Is there a more efficient way of doing this that I'm currently doing?
To make things trickier the Quarter value has to be a string not an integer.

As you can see I'm no expert and I've tried passing variables to the string which works but I can't successfully manage to have it iterate.

declare @tablename varchar(50)
set @tablename = '2'
EXEC('SELECT DISTINCT [GAELTACHT] FROM [EDB_Q' + @tablename + '18].[dbo].[POSTAL_ADDRESS]')

This is a simplified version of what I'm currently working with but just to demonstrate the databases I'm working with.

SELECT 'Q119' AS Quarter,  Count(BUILDING_ID) FROM Towns.CITY_Q119
UNION ALL
SELECT 'Q418' AS Quarter,  Count(BUILDING_ID) FROM Towns.CITY_Q418
UNION ALL
SELECT 'Q318' AS Quarter,  Count(BUILDING_ID) FROM Towns.CITY_Q318 
UNION ALL
SELECT 'Q218' AS Quarter,  Count(BUILDING_ID) FROM Towns.CITY_Q218
UNION ALL
SELECT 'Q118' AS Quarter,  Count(BUILDING_ID) FROM Towns.CITY_Q118 
UNION ALL
SELECT 'Q417' AS Quarter,  Count(BUILDING_ID) FROM Towns.CITY_Q417

Ideally, I would have 1 select statement that somehow iterates (but using string on integers) from between values 218 to 119. I don't know if what I'm asking for is even possible?

Improve this question
4
  • 1
    Ideally you need to change your design; is that something you can do? On a different note, when using dynamic SQL you should never inject raw strings. Always parametrise or properly quote (which is what you need to do here) your values. Commented Jun 5, 2019 at 10:32
  • You need to properly form your dynamic sql, use SysName instead of VARCHAR(n) and use QUOTENAME() function too to quote the table name, note that you can't pass object names as a parameter. Also I suggest to use sp_execsql instead of EXEC Commented Jun 5, 2019 at 10:39
  • 1
    Also, it seems that your variable which you named @TableName isn't a table name, it's a database name, cause your table name is [POSTAL_ADDRESS]. Commented Jun 5, 2019 at 10:43
  • Thank you for the feedback Sami. I'm totally open to rebuilding from scratch. Commented Jun 5, 2019 at 14:26

3 Answers 3

Reset to default
1

What about this?

DECLARE @quarterStart int = 2
DECLARE @quarterEnd int = 1
DECLARE @yearStart int = 18
DECLARE @yearEnd int = 19

DECLARE @year int
DECLARE @quarter int

DECLARE @quarterForDbName nvarchar(4)
DECLARE @sqlStatement nvarchar(max)

SET @year = @yearStart
SET @quarter = @quarterStart
SET @sqlStatement = ''

WHILE @year <= @yearEnd
BEGIN
   WHILE ((@year < @yearEnd AND @quarter <= 4) OR (@year = @yearEnd AND @quarter <= @quarterEnd))
   BEGIN
      SET @quarterForDbName = 'Q' + CAST(@quarter AS nvarchar(1)) + CAST(@year AS nvarchar(2))
      SET @sqlStatement = @sqlStatement + 'SELECT ''' + @quarterForDbName + ''' AS Quarter, Count(BUILDING_ID) FROM Towns.CITY_' + @quarterForDbName + ' UNION ALL '
      SET @quarter = @quarter + 1
   END
   SET @quarter = 1
   SET @year = @year + 1
END

PRINT SUBSTRING(@sqlStatement, 1, LEN(@sqlStatement) - 10)

Result:

SELECT 'Q218' AS Quarter, Count(BUILDING_ID) FROM Towns.CITY_Q218 UNION ALL SELECT 'Q318' AS Quarter, Count(BUILDING_ID) FROM Towns.CITY_Q318 UNION ALL SELECT 'Q418' AS Quarter, Count(BUILDING_ID) FROM Towns.CITY_Q418 UNION ALL SELECT 'Q119' AS Quarter, Count(BUILDING_ID) FROM Towns.CITY_Q119

Instead of the PRINT statement at the end you can use dynamic SQL:

EXEC SUBSTRING(@sqlStatement, 1, LEN(@sqlStatement) - 10)

Best wishes
Michael

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hi Michael, thank you for this code. I've modified for the real data I'm working with and get the expected result
0

What you can do is create a sequence in your db, please check the below link https://www.techonthenet.com/sql_server/sequences.php

CREATE SEQUENCE quarter_seq
  AS TINYINT
  START WITH 1
  INCREMENT BY -1
  MINVALUE 119
  MAXVALUE 218
  CYCLE
  CACHE 1;

Every time you recreate the database increment that value. I guess you have a script that does that so add a line to increment the sequence.

When you do your select statement use something like this:

SELECT Current_Value 
FROM SYS.Sequences 
WHERE name='quarter_seq'

And cast it as a string.

More info about sequences and how they work can be found here:

https://learn.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers?view=sql-server-2017

Improve this answer

1 Comment

Msg 11708, Level 16, State 1, Line 1 An invalid value was specified for argument 'INCREMENT BY' for the given data type. - Please check your code and test it before posting. There are people here who would downvote your answer...
0

I would suggest you drive your SQL statement creation off of sysdatabases. 

declare @sql varchar(max) = ''
select @sql += case when @sql = '' then '' else 'UNION ALL ' end + char(13) 
            + 'SELECT ''' + right(name, 4) + ''' AS Quarter, Count(Building_id) FROM ' + name + '.Towns.City_' + right(name, 4) + char(13)
  from sys.sysdatabases
 where name like 'EDB_Q%'

print @sql
exec(@sql)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.