SQL Server Performance Forum – Threads Archive
Adding a SQL statement to a parameter ?!
Hi again,I seem to be the most popular member in this forum !!
Anyway, I’m wondering the best way to go about this… I want to have a report in which a user chooses a Month from a dropdown list. The dropdown list will be populated with fields Jan, Feb, March, April, May and so on… When a user selects a month, I then want to create the SQL statement, for example, if a user chooses October I want to run:
SELECT body, eMail, dateEntered
FROM event
WHERE (dateEntered >= ’10/1/2003′) AND (dateEntered < ’11/1/2003′)
I initially created a Dataset which returned all events (SELECT * FROM events), then I created a Report Parameter. In the ‘available values’ section I clicked ‘Non-queried’, and then in the ‘Label’ field I put January, Feb, etc, and the Value field I would put the SQL statement like this:
(for January)
="SELECT body, eMail, dateEntered
FROM event
WHERE (dateEntered >= ‘1/1/2003’) AND (dateEntered < ‘2/1/2003’)" But I just got a lot of errors.
So what’s the best way to go about this ? DO I have to create 12 different datasets for each month.
I’m sort of stumped ?! Thanks again ! D
If this is a SQL datasource I would create a stored procedure that takes a month as the parameter (as an integer) and use that to generate the start and end dates. So for your non-queried parameter list use the Month Names as the labels and the month as an integer as the Value and pass it to a procedure like
create proc GetEvents @month int,@year int = 2003
as
set nocount on declare @start datetime
declare @end datetime
set @start = CONVERT(datetime,(CAST(@year as char(4)) + RIGHT(‘0’+CAST(@month as varchar(2)),2) + ’01’))
set @end = DATEADD(m,1,@start) SELECT body, eMail, dateEntered
FROM event
WHERE dateEntered >= @start AND dateEntered < @end
This would also give the option to add another parameter for year at a later date HTH Jasper Smith
damn, this is great for SQL Server, thanks. Unfortunately, I am using Oracle (no sprocs). I am setting variables in my SQL statements like so:
SELECT * FROM myTable
WHERE myField = ? Any suggestions ? I’ve never worked with Oracle before! thanks!
I’m afraid I know next to nothing about Oracle programming. As a workaround you could use two parameters. Set up the first one (in this example I have called it d1) to have the labels for the months and the values as the first of each month. Set up the second to allow Null values and set its default to Non Queried and its value to
=DateAdd("m",1,Parameters!d1.Value)
Then deploy the report and click on View Details and then Properties for the report. Click on Parameters and uncheck the Prompt User checkbox. This will hide the second parameter from the users but because of the default we have set, it will always be the first of the next month. This assumes you can use a query like
SELECT body, eMail, dateEntered
FROM event
WHERE dateEntered >= ? AND dateEntered < ?
HTH Jasper Smith
]]>