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

Add dynamic sql to report... Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 10:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 06, 2013 8:10 AM
Points: 64, Visits: 161
Hi,

Need a little advice on a report I'm trying to finish up. I would like to add a parameter where based on the dropdown selection, to add an "AND" operator to the Where clause. I will give an example.

Type
Choice 1 "xid=1"
Choice 2 "xid=2 AND jtype=123"
Choice 3 "xid=4 AND jtype=456"

I tried to add them as available values to a Parameter @Type, but doesn't seem to work, or I don't have the right expression. Or maybe I need a variable?

So, I would put in the main source query Where this=that AND @Type

How is the above possible. Please give steps. THanks in advance for your help!

Post #1436826
Posted Friday, March 29, 2013 6:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 17, 2014 11:46 PM
Points: 17, Visits: 30
Check the attachment

  Post Attachments 
Solutions.JPG (15 views, 26.86 KB)
Post #1436904
Posted Friday, March 29, 2013 7:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 06, 2013 8:10 AM
Points: 64, Visits: 161
Hi, thx for your reply.

What I want was that @Type would be only 1 parameter, and all the selected values/choices would be part of @Type.

@Type=A or B or C

A= AND xid=3
B= AND xid=4
C= AND xid=2 and jid=3

Does this make sense. Thx
Post #1436916
Posted Friday, March 29, 2013 8:05 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 22,504, Visits: 30,209
Nope. Not much here to work with, anything you get will be simple shots in the dark.

Can you post the code you are working with along with the any other pertenant info, like DDL for the tables, sample data, expected results?

You may also want to take a little time and read the first article I reference below in my signature block. It walks you through what you need to post and how to post it to get the best possible answers in return.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1436945
Posted Saturday, March 30, 2013 8:11 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 2:24 PM
Points: 172, Visits: 527
It is a little confusing what you're asking for, but based on your topic title ("adding dynamic sql") and looks like you are trying to build a dynamic where clause. Perhaps try something like this:

AND (@xid='A' AND value= 1)
OR (@xid='B' AND value = 2)
OR (@xid='C' AND value = 3
AND (@jid = 100)))

For each parameter that evaluates as true, it will evaluate the other half of the AND statement. So, if 'A' is chosen the value will be 1, etc.

HTH



Post #1437200
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse