1

In a SQL server database I have have this kind of table :

EVENT201201.dbo.EvAcc
EVENT201202.dbo.EvAcc
EVENT201203.dbo.EvAcc
EVENT201204.dbo.EvAcc
EVENT201205.dbo.EvAcc
...
EVENTYYYYMM.dbo.EvAcc

I also have DBACCES1.dbo.*. I created a query like this :

DECLARE @SQL varchar(511)
DECLARE @SQL_ACC varchar(250)
DECLARE @SQL_UClass varchar(250)
DECLARE @SQL_UClassDef varchar(250)
DECLARE @TABLENAME varchar(250)


SELECT @TABLENAME ='EVENT' + CONVERT(char(4), YEAR(GETDATE() - 1)) + CONVERT(char(2), MONTH(GETDATE() - 1))
SELECT @SQL_ACC = '' + QuoteName(@TABLENAME) + '.dbo.EvAcc ON EvAcc.fpointeur = cards.fpointeur'
SELECT @SQL_UClass = 'dbacces1.dbo.UClass ON UClass.fpointeur = cards.fpointeur AND MClass = 1'
SELECT @SQL_UClassDef = 'dbacces1.dbo.UClassDef ON UClassDef.SClass = UClass.SClass'

SELECT @SQL = 'SELECT cards.FPointeur, Ref, Peri, cout, cin, edate FROM dbacces1.dbo.cards INNER JOIN ' + @SQL_ACC + ' INNER JOIN ' + @SQL_UClass

EXEC(@SQL)

It works but I need to put this in a view. It doesn't work. I have this error (sorry for the french) :

La construction ou l'instruction SQL Déclarer un curseur n'est pas prise en charge.

It say than declare is not allowed in a view.

I tried to create a valued table function and it doen't work because I can't use the function exec with a string in a function. I have this error :

Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.

I also tried to create procedure but I can't use it as a table in my query. I tried to create a function which return the database name only but it doen't work again.

Do you have any solution?

EDIT 1 - Solution

I create a script executed each month. This is my script :

DECLARE @start_date DATETIME
DECLARE @end_date DATETIME
DECLARE @sql VARCHAR(MAX)
DECLARE @dbname VARCHAR(15)
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)

--Start the SQL request
SET @sql = 'CREATE VIEW dbo.viewEvAcc AS '

SET @start_date = convert(DATETIME, '01/01/2011', 101)
SET @end_date = GETDATE()

--Loop from the start date to now
WHILE @start_date < @end_date
BEGIN
    --Find new year and month
    SET @year = CONVERT(CHAR(4), YEAR(@start_date))
    SET @month = RIGHT('0' + RTRIM(MONTH(@start_date)), 2);

    --Create the db name with year and month
    SET @dbname = 'EVENT' + @year + @month

    --Concat the SQL Request
    SET @sql = @sql + 'SELECT * FROM ' + @dbname + '.dbo.EvAcc'

    --Update the start date for the month after
    SET @start_date = CONVERT(VARCHAR(8),DATEADD(MONTH,1,CONVERT(VARCHAR(8),@start_date,112)),112)

    --If the date is not the last date, it add a union
    IF @start_date < @end_date
    BEGIN
        SET @sql = @sql + ' UNION ALL '
    END
END

-- drop and create a view with new information
USE dbacces1
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'viewEvAcc')DROP VIEW dbo.viewEvAcc; 
EXEC(@sql)
Dougui
  • 7,142
  • 7
  • 52
  • 87

3 Answers3

0

A view can only contain a static select statement, so as a result if you want to implement dynamic SQL then you will need place this inside of a stored procedure. You could follow these steps:

  1. Create Stored Procedure with your dynamic sql - sp_1
  2. Create stored procedure with your other query - sp_2
  3. Inside this stored procedure, call your sp_1 and insert the results into a temp table
  4. Join this temp table with in your query.
Community
  • 1
  • 1
Taryn
  • 242,637
  • 56
  • 362
  • 405
0

Perhaps you could use synonims : http://msdn.microsoft.com/en-us/library/ms187552(v=sql.100).aspx

You make a static view, which selects data from the synonym object, but before selecting from the view you reassign synonym to the table from the database you need.

Mihail Golubev
  • 126
  • 4
0

If the view changes periodically (i.e. every day), you could script a job to drop and recreate (alter) the view as needed. It could be part of the job that creates and/or populates that new table.

If the empty tables are created ahead of time, look into creating a partitioned view to union them all. You can then put your query with a join on top of that and add the date constraint to that query's where clause.

Partitioned Views

Rawheiser
  • 1,200
  • 8
  • 17