Is this method possible?

Hi, I am just wondering is it possible to use report parameter values to create a view for the report
For example I have 5 databases in SQL Server 2005: db1,db2,db3,db4 and db5. Each of the databases has the same table format but storing different datas. I need to create a report that will take in two parameters: parameter START and parameter END, lets say START=db1 and END=db4, then from these values it will create a View that contains all information from db1 up to db 5 and finally the report will be generated base on the values in the View table.
Or if anyone know of a better way to overcome this method, do let me know.
Thanks.

Answer this question

Is this method possible?

Garrett - MSFT
I managed to come out with a stored proc to use the convention <database name>.dbo.<table name>. Below is the sample I used:
databases: db1, db2, db3
all has same table and column information as below:
   table name: tabledb
   column: data
the detail information for each of the databases are as follow:
   db1: data values are 1,2,3
   db2: data values are 4,5,6
   db3: data values are 7,8,9
An example of the values I will have in the result set is 1,2,3,4,5,6,7,8,9 if I have parameters @from = db1 and @to = db3.
Below is the stored proc for it:

CREATE PROCEDURE [dbo].[GrabInfo]
      @from int, --1 as db1, 2 as db2, ...
      @to int
AS
BEGIN
      SET NOCOUNT ON;

      DECLARE @sql nvarchar(MAX)
      DECLARE @temp nvarchar(17)
      SET @temp='db'+CAST(@from AS nvarchar(1))+'.dbo.tabledb'
      SELECT @sql = 'SELECT data FROM '+@temp
      WHILE (@from < @to)
      BEGIN
            SET @from = @from + 1
            SET @temp = 'db' + CAST(@from AS nvarchar(1)) + '.dbo.tabledb'
            SELECT @sql = @sql + ' UNION SELECT data FROM '+@temp
      END
      EXEC(@sql)
      RETURN
END

One more question I have, can it be done with View instead of Stored Proc
coreyw2000
Hi,
I am just wondering if this question is not clear enough, there is just no answer to this particular question, or this question is being posted at the wrong section of the forum
Please do reply something so that I know what to do.
Thanks.
Jonas Nordlund
Hi Joseph, thanks for replying. I know this method of yours will work if I only have to query from one database, but if my result set comes from a combination of a few databases, how can I create one general stored proc to do that Thanks.
Pradnya - MSFT
if the databases is on the same server, you can use the convention <database name>.dbo.<table name> for your query tables. If it's on another server, you might need to employ linked server functionalities of sql server.
xZippy
You will need to create a stored proc that will form your desired data and output that to reporting services as a result set.
Is this method possible?

Answer this question