Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrm23
Posting Yak Master
198 Posts |
 Posted - 2010-05-07 : 06:01:18
|
| Hi All,I have a report with 5 parameters:Location,Users,Clients,FromDate and ToDate.They are dependent. now the req is changed and i must make the clients parameter OPTIONAL.The user may or may not choose from Client List.now i have linked all of them in such a way that, first user selects location. then all the users belonging to that location are shown.Then when he choses user, all the clients under that user are shown.now i must display all clients if the does not select from client drop down and if he selects, i must show data for ONLY THAT CLIENT.how can i do? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-07 : 13:31:06
|
| thats easier. add a default option all clients along with client values you currently get based on user (you pass parameter!users.value as a parameter for clients to get associated clients). then in where use likeWHERE (Client=@Client OR @Client='All Clients')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
abhit_kumar
Posting Yak Master
147 Posts |
Posted - 2010-05-11 : 01:08:58
|
| how can we add a default option all clients along with client values?Because client value is coming from specific field, so how can we add a default value in that?is it possible? |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-05-11 : 04:01:28
|
| Hi Abhit Kumar,Yes. you can do that.you can add a default parameter ALL in the dataset of your client dropdown (or anything in your case).now i have written select id, clientname from hc_clients.instead i can write :select -1 as id, 'ALL' as clientnameunionselect id,clientname from hc_clients where.....****************************************************************************************************now that i have done this, i have a prob. there are over 2000 clients. so, when the default is set to ALL, it will naturally pass all the clientids present in the table to the PROC.Will this not impact the efficiency????is there any alternative so that we can AVOID passing those 2000+ values to my proc?right now i have written my SP like this:IF @userid = -1 BEGIN IF @Clientid = -1 BEGIN -------Procedure to fetch all records. (SELECT ALL conditon) END ELSE BEGIN --------Procedure to retrieve records for selected clients (is this ELSE part required? m not sure) ENDENDELSE (this is for first IF ) BEGIN -------Procedure to fetch records which match USER AND CLIENT ID ONLY------------ ENDEND---------------------------------------------------------------------------------------------------will this cause performance issues? m not sure as i have not run this in the LIVE DB....suggest me something... END |
|
|
|
|
|
|
|
|
Can we make parameters optional?