Database

CREATE FUNCTION fDropAllSystemUsers()<br />
<br />
returns int<br />
as<br />
begin<br />
declare @result int<br />
declare @name char(20)<br />
<br />
declare UserNavigator cursor for <br />
select [name] from sysusers where [name] <> 'dbo' and islogin = 1<br />
<br />
open UserNavigator <br />
fetch next from UserNavigator into @name <br />
<br />
while(@@FETCH_STATUS = 0) <br />
begin<br />
exec sp_dropuser @name <br />
fetch next from UserNavigator into @name <br />
end<br />
<br />
close UserNavigator<br />
deallocate UserNavigator<br />
<br />
set @result = 0<br />
return @result<br />
end

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