SQL Server do I need two queries and a function efficiency question

I want to get a list of people affiliated with a blog. The table [BlogAffiliates] has:

• BlogID
• UserID
• Privelage

and if the persons associated with that blog have a lower or equal privelage they cannot edit [bit field canedit].

Is this query the most efficient way of doing this or are there开发者_运维百科 better ways to derive this information??

I wonder if it can be done in a single query??

Can it be done without that convert in some more clever way?

declare @privelage tinyint
select @privelage = (select Privelage from BlogAffiliates 
            where UserID=@UserID and BlogID = @BlogID)

select aspnet_Users.UserName as username,
   BlogAffiliates.Privelage as privelage,
       Convert(Bit, Case When @privelage> blogaffiliates.privelage 
                    Then 1 Else 0 End) As canedit 
from BlogAffiliates, aspnet_Users 
where BlogAffiliates.BlogID = @BlogID and BlogAffiliates.Privelage >=2 
        and aspnet_Users.UserId = BlogAffiliates.UserID

Some of this would depend on the indexs and the size of the tables involved. If for example your most costly portion of the query when you profiled it was a seek on the "BlogAffiliates.BlogID" column, then you could do one select into a table variable and then do both calculations from there.

However I think most likely the query you have stated is probably going to be close the the most efficient. The only possible work duplication is you are seeking twice on the "BlogAffiliates.BlogID" fields because of the two queries.

You can try below query.

Select aspnet_Users.UserName as username, Blog.Privelage as privelage,
Convert(Bit, Case When @privelage> Blog.privelage 
    Then 1 Else 0 End) As canedit 
From
(
    Select UserID, Privelage
    From BlogAffiliates
    Where BlogID = @BlogID and Privelage >= 2
)Blog
Inner Join aspnet_Users on aspnet_Users.UserId = Blog.UserID

As per my understanding you should not use Table variable, in case you are joining it with other table. This can reduce the performance. But in case the records are less, then you should go for it. You can also use Local temporary tables for this purpose.