Database

ChrisFarrugia wrote:

I have this stored procedure on which I would like to apply if statements at the where clause. But it seems that sql is finding errors associated with the if statements. Can anyone please guide me on what is wrong. Maybe it is not possible to use if statemnts in where clauses?

IF ((@category<>0) AND @brand=0) 
BEGIN
    SELECT equipment_id, equipment_modelNumber, equipment_description, category, brand, 
           link, equipment_small_image, equipment_largr_image 
    FROM Gen_Equipment 
    INNER JOIN Gen_Equipment_Categories
    INNER JOIN Gen_Equipment_Brand
    INNER JOIN Gen_Equipment_Link
    ON (Gen_Equipment.equipment_category=Gen_Equipment_Categories.category_id)
    ON (Gen_Equipment.equipment_brand=Gen_Equipment_Brand.brand) 
    ON (Gen_Equipment.equipment_link=Gen_Equipment_Link.link_id) 
    WHERE Gen_Equipment.equipment_category=@category 
    ORDER BY Gen_Equipment.equipment_category
END
ELSE
BEGIN
    SELECT equipment_id, equipment_modelNumber, equipment_description, category, brand, 
           link, equipment_small_image, equipment_largr_image 
    FROM Gen_Equipment 
    INNER JOIN Gen_Equipment_Categories
    INNER JOIN Gen_Equipment_Brand
    INNER JOIN Gen_Equipment_Link
    ON (Gen_Equipment.equipment_category=Gen_Equipment_Categories.category_id)
    ON (Gen_Equipment.equipment_brand=Gen_Equipment_Brand.brand) 
    ON (Gen_Equipment.equipment_link=Gen_Equipment_Link.link_id) 
    WHERE equipment_brand=@brand
END 

Public Class ListItemData<br />
   <br />
    Dim mstrText As String<br />
    Dim mintIndex As Integer<br />
<br />
    Public Sub New(ByVal Text As String, ByVal Index As Integer)<br />
        mstrText = Text<br />
        mintIndex = Index<br />
    End Sub<br />
<br />
    Public ReadOnly Property Index() As Integer<br />
        Get<br />
            Return mintIndex<br />
        End Get<br />
    End Property<br />
<br />
    Public Overrides Function ToString() As String<br />
        Return mstrText<br />
    End Function<br />
<br />
End Class<br />
<br />

str = "select f_id,f_name from facultylist"<br />
        result = mdGlobalInstance.GetResultFromQuery(str)<br />
'here result is dataset<br />
        For i = 0 To result.Tables(0).Rows.Count - 1<br />
            lstFaculty.Items.Add(New ListItemData(result.Tables(0).Rows(i).Item(1), result.Tables(0).Rows(i).Item(0)))<br />
        Next

dim id as integer<br />
id=CType(LstFaculty.SelectedItem, ListItemData).Index

SELECT Column1,Column2 from Table1 where SearchColumn LIKE '%' + @SearchString + '%'