none
Query with an optional parameter

    Question

  • I have made a SP with optional paramter, but I don't like this method. Because if column is been changed then this should be changed at two place. Is there another way to make optional paramter is SQl?

     
    ALTER procedure [dbo].[USP_GetPct]    
     @intWebatelierDatabase_id as integer,    
     @intCurtainOrderType_id as integer,    
     @intShopGroup_id as integer,  
     @intPleatType_id as integer = -1  
    as    
     if (@intPleatType_id = -1)  
     begin  
      select top 1 intDefaultPct  
      from WA_DefaultValuesDrapesCurtain  
      where intWebatelierDatabase_id in (@intWebatelierDatabase_id, 0)  
      and intCurtainOrderType_id = @intCurtainOrderType_id  
      and intShopGroup_id in (@intShopGroup_id, 0)  
     end else  
     begin  
      select top 1 intDefaultPct  
      from WA_DefaultValuesDrapesCurtain   
      where intWebatelierDatabase_id in (@intWebatelierDatabase_id, 0)  
      and intCurtainOrderType_id = @intCurtainOrderType_id  
      and intShopGroup_id in (@intShopGroup_id, 0)  
      and intPleatType_id in (@intPleatType_id, 0)  
      order by  
       intWebatelierDatabase_id desc,  
       intShopGroup_id desc,  
       intPleatType_id desc  
     end  

    Friday, February 17, 2012 8:15 AM

Answers

  • You can do this by specifying a default value for the input parameters.. from the given code what I can understand is you always want to pick the records that match the input parameter and 0  as weel.. see the e.g 

    ALTER procedure [dbo].[USP_GetPct]    
     @intWebatelierDatabase_id as integer=0,    
     @intCurtainOrderType_id as integer,    
     @intShopGroup_id as integer=0,  
     @intPleatType_id as integer = -1  
    as    
     if (@intPleatType_id = -1)  
     begin  
      select top 1 intDefaultPct  
      from WA_DefaultValuesDrapesCurtain  
      where (intWebatelierDatabase_id = @intWebatelierDatabase_id or intWebatelierDatabase_id=0 )
      and intCurtainOrderType_id = @intCurtainOrderType_id  
      and ( intShopGroup_id =@intShopGroup_id or intShopGroup_id=0)
     end else  
     begin  
      select top 1 intDefaultPct  
      from WA_DefaultValuesDrapesCurtain   
      where (intWebatelierDatabase_id =@intWebatelierDatabase_id or intWebatelierDatabase_id=0)
      and intCurtainOrderType_id = @intCurtainOrderType_id  
      and (intShopGroup_id =@intShopGroup_id or intShopGroup_id=0)
      and (intPleatType_id =@intPleatType_id or intPleatType_id=0)
      order by  
       intWebatelierDatabase_id desc,  
       intShopGroup_id desc,  
       intPleatType_id desc  
     end  

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by arun.passioniway Wednesday, February 22, 2012 5:02 AM
    • Marked as answer by KJian_ Friday, February 24, 2012 6:25 AM
    Friday, February 17, 2012 8:32 AM
  • Hi Robert3398,

    I think you can also use dynamic SQL to build the query.

    There are several ways to implement a dynamic search, I would suggest you have a look the following link for more information:

    Dynamic Search Conditions in T-SQL
    http://www.sommarskog.se/dyn-search.html


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.

    • Proposed as answer by arun.passioniway Wednesday, February 22, 2012 5:02 AM
    • Marked as answer by KJian_ Friday, February 24, 2012 6:25 AM
    Tuesday, February 21, 2012 6:36 AM

All replies

  • You can do this by specifying a default value for the input parameters.. from the given code what I can understand is you always want to pick the records that match the input parameter and 0  as weel.. see the e.g 

    ALTER procedure [dbo].[USP_GetPct]    
     @intWebatelierDatabase_id as integer=0,    
     @intCurtainOrderType_id as integer,    
     @intShopGroup_id as integer=0,  
     @intPleatType_id as integer = -1  
    as    
     if (@intPleatType_id = -1)  
     begin  
      select top 1 intDefaultPct  
      from WA_DefaultValuesDrapesCurtain  
      where (intWebatelierDatabase_id = @intWebatelierDatabase_id or intWebatelierDatabase_id=0 )
      and intCurtainOrderType_id = @intCurtainOrderType_id  
      and ( intShopGroup_id =@intShopGroup_id or intShopGroup_id=0)
     end else  
     begin  
      select top 1 intDefaultPct  
      from WA_DefaultValuesDrapesCurtain   
      where (intWebatelierDatabase_id =@intWebatelierDatabase_id or intWebatelierDatabase_id=0)
      and intCurtainOrderType_id = @intCurtainOrderType_id  
      and (intShopGroup_id =@intShopGroup_id or intShopGroup_id=0)
      and (intPleatType_id =@intPleatType_id or intPleatType_id=0)
      order by  
       intWebatelierDatabase_id desc,  
       intShopGroup_id desc,  
       intPleatType_id desc  
     end  

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by arun.passioniway Wednesday, February 22, 2012 5:02 AM
    • Marked as answer by KJian_ Friday, February 24, 2012 6:25 AM
    Friday, February 17, 2012 8:32 AM
  • Hi Robert3398,

    I think you can also use dynamic SQL to build the query.

    There are several ways to implement a dynamic search, I would suggest you have a look the following link for more information:

    Dynamic Search Conditions in T-SQL
    http://www.sommarskog.se/dyn-search.html


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.

    • Proposed as answer by arun.passioniway Wednesday, February 22, 2012 5:02 AM
    • Marked as answer by KJian_ Friday, February 24, 2012 6:25 AM
    Tuesday, February 21, 2012 6:36 AM
  • Thx, both answers help me a lot
    • Edited by Robert3398 Tuesday, February 21, 2012 9:26 PM
    Tuesday, February 21, 2012 9:25 PM