Database

Known Issues
The converter cannot currently determine whether SomeName(SomeParam) is a method call, or a reference to an indexer - this will not be possible without actually compiling the code or performing some much deeper analysis of the code.

SELECT * 
FROM (SELECT * FROM table WHERE keyId=1 AND value=10) AS t1
INNER JOIN (SELECT * FROM table WHERE keyId=2 AND value=11) AS t2 ON t1.title = t2.title

Maysam Mahfouzi wrote:

I just wonder if you think this is a heavy query and can be rephrased to be performed faster?

SELECT
   title
FROM
   table
WHERE 
   (keyId=1 AND value=10) OR (keyId=2 AND value=11)
GROUP BY title
HAVING COUNT(title) = 2

Colin Angus Mackay wrote:

go with the one that is easier to understand unless it is causing a performance problem

Colin Angus Mackay wrote:

I don't know if that was the answer you were looking for

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