July 17, 2012 at 2:03 pm
Hello All,
First off, I am mostly new to SQLCentral, yet I found a lot of useful information in helping my development as a SQL programmer, so big thumbs up to you guys for the great support! I am using iReports (similar to Crystal Reports) to create visually pleasing reports. I have to tweak this report to have a parameter that changes the data that gets pulled in whether this field is there for the data or not.
So it is possible to create a CASE Statement that includes the Joins?
July 17, 2012 at 2:07 pm
You could solve this with Dynamic code or by writing different queries trough a condition (IF...ELSE...).
I'm not sure if that is what you need.
July 18, 2012 at 9:40 am
It helps give me direction on what I would need to do, I appreciate it!
July 19, 2012 at 12:37 pm
The easiest way is:
IF @Condition = 'Something'
SELECT *
FROM MyTable
WHERE myfield = @Variable
ELSE
SELECT *
FROM MyTable
For the dynamic query you could use something like this:
DECLARE @Sql VARCHAR(MAX);
DECLARE @ParmDefinition nvarchar(500);
SELECT @Sql = 'SELECT * FROM MyTable '
+ CASE WHEN @Condition = 'Something'
THEN ' WHERE myfield = @MyVariable' ELSE '' END;
SET @ParmDefinition = N'@MyVariable int';
EXECUTE sp_executesql @SQL, @ParmDefinition, @MyVariable = @Variable;
July 19, 2012 at 1:00 pm
vlad64 (7/17/2012)
Hello All,First off, I am mostly new to SQLCentral, yet I found a lot of useful information in helping my development as a SQL programmer, so big thumbs up to you guys for the great support! I am using iReports (similar to Crystal Reports) to create visually pleasing reports. I have to tweak this report to have a parameter that changes the data that gets pulled in whether this field is there for the data or not.
So it is possible to create a CASE Statement that includes the Joins?
To make sure we understand what you're looking to do... are you looking for optional parameters or changing the query logic based on if a parameter is sent as NULL?
As mentioned above, when you're changing your restrictions (WHERE clause) based on parameters, you don't do this at a Join but you do it in the result set allowed, so via the where. If there's a lot of fields like this, dynamic based 'catch all' queries are the most optimal, however, typically you'll start with a structure that looks like this:
SELECT *
FROM tblA join tblB ON colA = ColB
WHERE
(tblA.CheckMe = @CheckMe OR @CheckMe IS NULL)
AND (tblA.SomethingElse = @SomethingElse OR @SomethingElse IS NULL)
Start there and get it working correctly before you try tackling dynamic SQL builds. They're really easy to do improperly and end up opening yourself up to SQL Injection. Get the query working first, then come back to us with that and we can help you get it optimal.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply