April 5, 2008 at 11:23 pm
Help! I am a new user.
I have some report parameters: "Year" and "MonthNum"
my dataset query statements will need to change for every month and year. For example, the table names below are for July, August, and September 2007 months:
[dbo].[ProductRank~QD~1~7~2007~0]
[dbo].[ProductRank~QD~1~8~2007~0]
[dbo].[ProductRank~QD~1~9~2007~0]
My SQL statement for July 2007 would be-->
SELECT *
FROM [dbo].[ProductRank~QD~1~7~2007~0]
So I would like to pass my Report parameters- YEAR and MONTHNUM - into the query statement to change my table name for any month and year that I need---
e.g.-->
[dbo].[ProductRank~QD~1~@MonthNum~@Year~0]
Unfortunately, this statement above does not work.
How would I write the sql statement to do that? Please help
April 7, 2008 at 6:45 am
I would have to say that your database design is less-than-perfect when you find yourself making a new table every month with the date tacked onto the end of it.
I would suggest you fix this issue. If you cannot fix the issue, for your reporting problem, I would suggest you create a view that unions these together and query against it:
CREATE VIEW dbo.ProductRank
AS
SELECT 7 AS [Month], 2007 AS [Year], * FROM [dbo].[ProductRank~QD~1~7~2007~0]
UNION ALL
SELECT 8 AS [Month], 2007 AS [Year], * FROM [dbo].[ProductRank~QD~1~8~2007~0]
UNION ALL
SELECT 9 AS [Month], 2007 AS [Year], * FROM [dbo].[ProductRank~QD~1~9~2007~0]
It will not be great performance since you will end up with a query plan that hits all of the tables, but it will work.
You could also create a stored procedure that uses conditional logic to avoid the plan hitting all of the tables (you may want to use RECOMPILE to make sure you do not cache an execution plan).
April 7, 2008 at 7:49 am
assuming you can't unglue your design, I wonder if you could use dynamic sql from within a table based user defined function. attach the user defined function inside your proc like a regular table but let the function handle finding the right table based on your parameters.
April 7, 2008 at 7:59 am
Something like this might work.
CREATE PROC donkey (@MonthNum int, @Year int)
AS
DECLARE @query_head nvarchar(1000), @query_foot nvarchar(100), @sql_string nvarchar(1500)
SET @query_head = 'SELECT * FROM [dbo].[ProductRank~QD~1~'
SET @query_foot = RTRIM(CONVERT(char(2),@MonthNum)) + '~' + RTRIM(CONVERT(char(4),@Year)) + '~0]'
SET @sql_string = @query_head + @query_foot
--PRINT @sql_string
EXEC sp_executesql @sql_string
April 7, 2008 at 10:18 am
In the Data tab, I modified the code as:
CREATE PROC Name (@MonthNum int, @Year int)
AS
DECLARE @query_head nvarchar(1000), @query_foot nvarchar(100), @sql_string nvarchar(1500)
SET @query_head = 'SELECT * FROM [dbo].[E.AN.ONDMD_EC_Leadership~~03~'
SET @query_foot = RTRIM(CONVERT(char(4),@Year)) + '~' + RTRIM(CONVERT(char(2),@MonthNum)) + '~0_NEW]'
SET @sql_string = @query_head + @query_foot
--PRINT @sql_string
EXEC sp_executesql @sql_string
In the command type, I had set to "Text" and I got the following error- "An error occured while excuting the query. Incorrect syntax near the keyword PROC"
April 7, 2008 at 10:31 am
Are you doing this in Reporting Services?
If so, you need to compile the proc in sql, then select that procedure as the source of your dataset, then map your report parameters to the proc parameters.
If you prefer not to make a procedure, then I think you could use a parameterized query . If you remove the create proc stamement, SSRS should allow you to run the query and then you can map the parameters in the same way you would for a procedure.
You may need to manually feed it values for @year and @monthnum when you set up the dataset.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply