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.
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.
|||You will need to create a stored proc that will form your desired data and output that to reporting services as a result set.|||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.
|||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.|||

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?