Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Conditional parameter


Conditional parameter

Author
Message
5280_Lifestyle
5280_Lifestyle
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 325
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"
5280_Lifestyle
5280_Lifestyle
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 325
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"
OldCursor
OldCursor
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 162
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search