Conditional parameter
-
I have a query that uses a date as a parameter. The date parameter uses conditional parameter which the end user would be able to select. The conditional parameters are =, <>, <, <=, >, >=, 'begins with', 'contains', 'ends with', 'like', 'between', 'is null'. There are several reports that use this same type of conditional & date combination parameters.
This query returns values based on a user selected date being greater than a column date.
SELECT AL3.country_name, AL2.company_name, AL1.amount_charge
FROM db1.dbo.history_adjustment AL1, db1.dbo.company AL2, db1.dbo.country AL3
WHERE (AL1.customer_abbr=AL2.company_abbr AND AL2.country_code=AL3.country_code)
AND ((AL1.move_date > cast(@move_date AS DATE) AND AL1.ban NOT IN (0, 12345)
AND AL2.country_code<>'GB' AND AL1.charge_code NOT IN ('HJKL', 'HELO') AND AL1.amount_charge<>0))
ORDER BY AL2.company_name ASC, AL1.move_date DESC
How can this query be altered to replace the 'greater than' conditional operator with a conditional parameter? I need all of those conditions to be available to the end user when they enter a date, or if they choose 'between' they will need to enter two dates.
"Nicholas"
-
I need the sql equivalent of this for my ssrs report, of which the user can select any of those conditional operators listed in the first post to tie in with a date parameter.
"Nicholas"
-
My solution would be to write the sql code in a procedure and generate it dynamically. For example:
create proc MyProc
@Conditional varchar(10) = (For example) '<='
,@DateVal dateTime = (For example) '20130301'
as
BEGIN
declare @sql varchar(max)
set @sql = "Select etc etc where DateField " + @Conditional + " " + @DateVal + "etc etc"
exec @sql
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply