Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conditional parameter Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 5:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 12:49 PM
Points: 39, Visits: 278
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"
Post #1427704
Posted Thursday, March 7, 2013 1:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 12:49 PM
Points: 39, Visits: 278
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"
Post #1428228
Posted Saturday, March 9, 2013 8:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 55, Visits: 149
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
Post #1428901
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse