Stack Exchange
Or clause in a variable in sql
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

In VB, does If a = """B"" or a = ""C""" Then compare a with two separate strings ("B" and "C"), or to one string containing a complex sequence of characters including quote marks? (Hint, it's option 2). Why, then, do you expect SQL to act any differently?

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.  

you are right sir, the problem is, the division codes there are selected in the vb.net program, the divisions may vary, but you are right, it is the easiest way sir

Well if you have filter like this than you need dynamic query exetion in sql server ..that you can do with sp_executesql procuedure of sql server 
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE  datefrom1 smalldatetime;
DECLARE  @dateto1 smalldatetime;
DECLARE  @rank1 varchar(1);
DECLARE  @Filter1 varchar(8000);

/* Build the SQL string one time.*/
SET @SQLString =
     N'select * from finhours
       where (div_cde = @filter)
      and date_from = @datefrom
      and date_to = @dateto
      and rank = @rank';

SET @ParmDefinition = N'@datefrom1 as smalldatetime,
                         @dateto1 as smalldatetime,
                        @rank1 as varchar(1),
                        @Filter1 as varchar(8000)';


/* Execute the string with the first parameter value. */

EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @datefrom1 =@datefrom,
                      @dateto1 =@dateto,
                      @rank1 = @rank,
                      @Filter1 = @Filter;


i think i couldnt do this because my original query uses strings... well, these strings are hardcoded anyway, meaning i have defined them.. like 'SO'

You would need to include @filter in the @SqlString rather than pass it as a parameter - you've currently just added a layer of obfuscation whilst not eliminating the actual problem.

@Joshuamagsino- this is only way to do if you want to execute query by passing condition as parameter...that you can also google it...google dynamic query in sql server

Damien_The_Unbeliever sorry sir, i'm just pointing what i just know... i have to learn, i need to learn, thats why i ask :D but thanks sir for the info! it really does make sense.

Pranay Rana Sir, does that mean that, i have to "pass" all of the defined parameters on my stored procedure, uhm, they are the ones with the (') sign. like for example my UDF contribution, which is included in the stored procedure, and has a value passed like         round(dbo.Contribution(emp_cde,0,'SSSEmpr',@DATEFROM,)

Your @filter can't work as written.  where (div_cde = @filter) is expecting a simple value  from filter.

Thanks to your ideas guys, i have found the answer to my problem.
I HAVE to use dynamic SQL, but because of the limited size of nvarchar(4000) and varchar(8000) variables in sql server 2000, i think, its not possible to use sp_executesql. (correct me if i'm wrong here please.) because sp_executesql would use a variable which should be declare as nvarchar which is constraint at 4000 size, but the query i have to fire is at 16000+.
(If you have any ideas to fire this at sp_executesql without using nvarchar(max), please do let me know.)
so i used this method
declare @QueryStart as string
declare @QueryBody as string
declare @Queryfrom as string
declare @querywhere as string

set @QueryStart  = <8000 character statement>
set @QueryBody = <8000 character statement>
set @Queryfrom = <8000 character statement>
set @querywhere = <8000 character statement>


exec (@QueryStart + @QueryBody + @Queryfrom + @querywhere)

this procedure is not recommended, due to the posibility of sql injection, but it is the only way that i could think of to do it in sql server 2000.
But if anyone has a more efficient answer, please let me know :D
