Or clause in a variable in sql

Question

i have a stored procedure which somehow goes like

create SP_justest
(@datefrom as smalldatetime,
@dateto as smalldatetime,
@rank as varchar(1),
@Filter as varchar(8000)
)

select * from finhours
where (div_cde = @filter)
and date_from = @datefrom
and date_to = @dateto
and rank = @rank


GO

so in vb.net, i have a sub which calls the stored procedure, here is the exact code of the sub:

Private Sub InsertPayRollRecords()
    Try
        'SQLConn.Open()

        sqlcomm.CommandText = "dbo.SP_INSERTVALUESINTOPR"

        sqlcomm.CommandType = CommandType.StoredProcedure

        sqlcomm.Parameters.Add("@DATEFROM", DateValue(dtpCFrom.Value))
        sqlcomm.Parameters.Add("@DATETO", DateValue(dtpCTo.Value))
        sqlcomm.Parameters.Add("@RANK", rank)
        sqlcomm.Parameters.Add("@FILTER", ReturnDivision())

        sqlcomm.Connection = SQLConn
        sqlcomm.CommandTimeout = 999999
        sqlcomm.ExecuteNonQuery()

    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

@filter would have a value like:

'AP0' OR div_cde = 'FO0'  OR div_cde = 'ME0'  OR div_cde = 'SO0'  OR div_cde = 'XO0'

problem is, when i fire this from VB.net 2000, i dont encounter any error at all, it just doest

Answer 1

When you find yourself passing a list to a stored procedure, think "table" instead. Your procedure could refer to a table or view:

select * from finhours
where 
and date_from = @datefrom
and date_to = @dateto
and rank = @rank
and div_cde in (select div_cde from interesting_division_codes)

Usually the list isn't arbitrary. Rather, they belong to some group. Then you wind up with

select * from finhours
where 
and date_from = @datefrom
and date_to = @dateto
and rank = @rank
and div_cde in (select div_cde 
                from interesting_division_codes
                where div_category = @div_cat
                )

This is highly preferred. It's more efficient to process and leads to more consistent results. Not to mention that it will lead to some scintillating discussion over the number of groups and their memberships.

If the list of div_cde values cannot be derived within the database and is per-process, use a temporary table. If it's really arbitrary and the filter values are used only once, insert them into a table parameter and pass that instead of a varchar, and join to it in your query.