Control of Flow

[Jeff]

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

[Michael Levy]

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
>
>

[Tom De Lancey]

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')