Control of Flow


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Control of Flow

  1. #1
    Jeff Guest

    Control of Flow


    I am trying to create a stored procedure
    (using Visual InterDev 6) that "assembles"
    a query based on what parameter's are passed to it.

    Note: Yes I am aware I can use:
    "Like '%Param1%' And Like '%Param2%'"

    I have tried everything I can think of to make this work
    but...?

    Is this kind of thing possible? I get nothing but ADO error's.



    Create Procedure spSearch
    (
    @xNDC VarChar(11),
    @xTrade VarChar(30),
    @xGeneric VarChar(30)
    )
    As
    Begin
    Declare @stringtoexecute VarChar(255)
    Set @stringtoexecute = ""

    If @xNDC Is Not Null
    Begin
    Set @stringtoexecute = "Active.NDC = '" + @xNDC + "'"
    End

    If @xTrade Is Not Null
    Begin
    If @stringtoexecute != ""
    Set @stringtoexecute = @stringtoexecute
    + " And Active.Trade = '" + @xTrade + "'"
    End

    If @xGeneric Is Not Null
    Begin
    If @stringtoexecute != ""
    Set @stringtoexecute = @stringtoexecute + " And
    Active.Generic = '" + @xGeneric + "'"
    End

    Set @stringtoexecute=
    "Select ,Generic,Trade,Strength,PrtPkg,Package,Drug,Dosage,Unit Dose,Obsolete
    From Active Where " + @stringtoexecute

    Execute (@stringtoexecute)

    End



  2. #2
    Michael Levy Guest

    Re: Control of Flow

    Jeff,

    I'm assuming that any of the parameters could be NULL and, if so, should not
    be used within the query. If this is the case, try something like this

    Declare @stringtoexecute VarChar(255)
    Set @stringtoexecute = ""

    If @xNDC Is Not Null
    Set @stringtoexecute = 'Active.NDC = ''' + @xNDC + ''' AND '

    If @xTrade Is Not Null
    Set @stringtoexecute = @stringtoexecute + 'Active.Trade = ''' + @xTrade
    + ''' AND '

    If @xGeneric Is Not Null
    Set @stringtoexecute = @stringtoexecute + 'Active.Generic = ''' +
    @xGeneric + '''

    IF RIGHT(@stringtoexecute, 4) = 'AND '
    SET @stringtoexecute = LEFT(@stringtoexecute, LEN(@stringtoexecute)-4)

    -Mike
    --
    Michael Levy MCSD, MCDBA, MCT
    Consultant
    GA Sullivan
    michaell@gasullivan.com

    "Jeff" <jeffm@earthlink.net> wrote in message
    news:3904a60b$1@news.devx.com...
    >
    > I am trying to create a stored procedure
    > (using Visual InterDev 6) that "assembles"
    > a query based on what parameter's are passed to it.
    >
    > Note: Yes I am aware I can use:
    > "Like '%Param1%' And Like '%Param2%'"
    >
    > I have tried everything I can think of to make this work
    > but...?
    >
    > Is this kind of thing possible? I get nothing but ADO error's.
    >
    >
    >
    > Create Procedure spSearch
    > (
    > @xNDC VarChar(11),
    > @xTrade VarChar(30),
    > @xGeneric VarChar(30)
    > )
    > As
    > Begin
    > Declare @stringtoexecute VarChar(255)
    > Set @stringtoexecute = ""
    >
    > If @xNDC Is Not Null
    > Begin
    > Set @stringtoexecute = "Active.NDC = '" + @xNDC + "'"
    > End
    >
    > If @xTrade Is Not Null
    > Begin
    > If @stringtoexecute != ""
    > Set @stringtoexecute = @stringtoexecute
    > + " And Active.Trade = '" + @xTrade + "'"
    > End
    >
    > If @xGeneric Is Not Null
    > Begin
    > If @stringtoexecute != ""
    > Set @stringtoexecute = @stringtoexecute + " And
    > Active.Generic = '" + @xGeneric + "'"
    > End
    >
    > Set @stringtoexecute=
    > "Select ,Generic,Trade,Strength,PrtPkg,Package,Drug,Dosage,Unit

    Dose,Obsolete
    > From Active Where " + @stringtoexecute
    >
    > Execute (@stringtoexecute)
    >
    > End
    >
    >




  3. #3
    Tom De Lancey Guest

    Re: Control of Flow


    I find I get this when someone wants a screen that can make a zillion choices
    as "filters" for a report or listing. I use the VB techique of building
    a string for the where clause. Here is some sample code:

    I like to set the default values if a null is passed.
    --------------------------------------------------------------
    /*Tom de Lancey 3/1/2000

    Returns a list of fee schedule detail records for a given
    FeeScheduleID or combination of fackey and custkey.

    */

    CREATE Procedure Get_FeeScheduleDetail

    @CustKey int,
    @FeeScheduleID int=-1,
    @Fackey int=-1,
    @FeeScheduleDetailID int=-1,
    @Facname varchar(40)='',
    @CodeID int=-1,
    @CodeNumber varchar(15)='',
    @Codename varchar(25)='',
    @CodeCategory varchar(15)='',
    @Modifier1 varchar(6)='',
    @Modifier2 varchar(6)='',
    @Modifier3 varchar(6)='',
    @Modifier4 varchar(6)=''


    AS
    set quoted_identifier off
    set nocount on

    Declare @Where varchar(500)

    Select @Where=@Where+'Where f.Custkey=Convert(varchar(10),@Custkey)'

    --Now check if the other parameters are supplied

    If @FeeScheduleID<>-1
    Select @Where=@Where+' and sd.FeeScheduleID=Convert (Varchar(10),@FeeScheduleID)'
    If @Fackey<>-1
    Select @Where=@Where+' and f.Fackey=convert(Varchar(10),@Fackey)'
    If @Facname<>''
    Select @Where=@Where+' and fc.FacName=@FacName'
    If @CodeID<>-1
    Select @Where=@Where+' and sd.CodeID=convert(Varchar(10),@CodeID)'
    If @CodeNumber<>''
    Select @Where=@Where+' and sd.CodeNumber=@CodeNumber'
    If @CodeName<>''
    Select @Where=@Where+' and sd.CodeName=@CodeName'
    If @CodeCategory<>''
    Select @Where=@Where+' and sd.CodeCategory=@CodeCategory'
    If @Modifier1<>''
    Select @Where=@Where+' and sd.Modifier1=@Modifier1'
    If @Modifier2<>''
    Select @Where=@Where+' and sd.Modifier2=@Modifier2'
    If @Modifier3<>''
    Select @Where=@Where+' and sd.Modifier3=@Modifier3'
    If @Modifier4<>''
    Select @Where=@Where+' and sd.Modifier4=@Modifier4'

    --Now excute the string
    Exec ('

    Select sd.FeeScheduleDetailID
    ,sd.FeeScheduleID
    ,sd.AutoFeeCharge
    ,sd.CodeID
    ,sd.CodeNumber
    ,sd.CodeName
    ,sd.CodeCategory
    ,sd.Modifier1
    ,sd.Modifier2
    ,sd.Modifier3
    ,sd.Modifier4
    ,sd.BaseRate

    From PMS_FeeScheduleDetail sd
    Join PMS_FeeSchedule f on.FeeScheduleID=sd.FeeScheduleID
    Join lkFacility fc on f.fackey=fc.fackey'+''+
    @where+''+
    ' Order by sd.FeeScheduleID, sd.CodeNumber')




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center