CODEWITHSUNDEEP
sql-server
scojomodena
scojomodena

Reputation: 842

How to return boolean from Query

I need to check if the user has access based on their roles. (ASP.NET and SQL Server)

SELECT rf.ReadAccess
FROM aspnet_Users as u INNER JOIN aspnet_Roles as r ON r.UserID=u.UserID
INNER JOIN RolesByForm as rf ON rf.RoleID=r.RoleID
WHERE u.UserID=@UserID

So this gets me close, but this is what I really want. I want forms visible by default, so if there is no record in RolesByForm, it should be "True". But if there is a record it should return based on the ReadAccess field.

How do I get the query to return a Boolean?

Upvotes: 0

Views: 4844

Answers (5)

Nonym
Nonym

Reputation: 6299

You can return 0 (false) and 1 (true). Try this example:

CREATE PROCEDURE CheckReadAccess 
@UserID INT
AS

BEGIN
DECLARE @rdAxs VARCHAR(255)

    SELECT @rdAxs = rf.ReadAccess
    FROM aspnet_Users as u INNER JOIN aspnet_Roles as r ON r.UserID=u.UserID
    INNER JOIN RolesByForm as rf ON rf.RoleID=r.RoleID
    WHERE u.UserID=@UserID

    IF @rdAxs IS NULL
        RETURN 0
    ELSE
        RETURN 1

END

Upvotes: 0

devrooms
devrooms

Reputation: 3149

Assuming rf.ReadAccess is a bit field (1 or 0) then you want an outer join to capture the non existence of a rolesbyform record.

Something like this perhaps?

SELECT distinct isnull(rf.ReadAccess, 1) as ReadAccess
FROM aspnet_Users as u INNER JOIN aspnet_Roles as r ON r.UserID=u.UserID
LEFT OUTER JOIN RolesByForm as rf ON rf.RoleID=r.RoleID
WHERE u.UserID=@UserID

Upvotes: 0

Surjit Samra
Surjit Samra

Reputation: 4662

Try

SELECT case count(rf.ReadAccess) when 0 then 'True' else 'False' end ReadAccess 
FROM aspnet_Users as u INNER JOIN aspnet_Roles as r ON r.UserID=u.UserID
INNER JOIN RolesByForm as rf ON rf.RoleID=r.RoleID
WHERE u.UserID=@UserID

Upvotes: 1

David Aleu
David Aleu

Reputation: 3942

SELECT ReadAccess=CASE rf.ReadAccess WHEN 1 THEN 'True' ELSE 'False' END
FROM aspnet_Users as u INNER JOIN aspnet_Roles as r ON r.UserID=u.UserID
INNER JOIN RolesByForm as rf ON rf.RoleID=r.RoleID
WHERE u.UserID=@UserID

And in your .net code write something like:

bool result;
bool myBoolean=Boolean.TryParse(dataset["ReadAccess"], out result);

Upvotes: 0

Mondo
Mondo

Reputation: 327

In .NET the Sql Server field BIT is true or false, so:

CAST ( rf.ReadAccess as BIT)

Or if ReadAccess is a varchar:

CASE ReadAccess
   WHEN 'Something' THEN CAST(1 as bit)
   ELSE CAST (0 as bit)
END

Upvotes: 0

Related Questions