Setting bit on EXISTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Setting bit on EXISTS

Author

Topic

Boersnoes
Starting Member

6 Posts

Posted - 2007-05-27 : 06:58:43

Hi, I'm puzzled over this problem:
I need a SP to return a bit so I can fill a checkbox inside a gridView with it.

There are three tables

Roles (the aspnet one) with a roleId as PK
Files with a fileId as PK
FilesInRole with roleId and fileId as PK

Now I want to make a SP doesRoleHaveFile(roleId, fileId)

I thought I should use EXISTS since it returns a Byte anyway, but I can't get it to work.
Anyone can help me out here?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-27 : 10:14:27

what have you tried so far?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Boersnoes
Starting Member

6 Posts

Posted - 2007-05-29 : 03:13:13

Sorry for the late reply, was out for the weekend.

For now I've written an SQL that returns all the roles for a specific file. This way I can just itterate through all the roles and check the necessary boxes in the gridview. I think this is more performant then querying the DB for each role.

I am still curious though, is it possible to return the result of an EXISTS somehow?

Kristen
Test

22859 Posts

Posted - 2007-05-29 : 04:35:27

SELECT 1
FROM MyTable
WHERE EXISTS (SELECT * FROM OtherTable WHERE OtherTable..SomeColumn = MyTable..OtherColumn)

or

SELECT CASE WHEN EXISTS (SELECT * FROM SomeTable WHERE SomeColumn = @SomeValue)
THEN 1
ELSE 0
END

or

IF EXISTS (SELECT * FROM SomeTable WHERE SomeColumn = @SomeValue)
RETURN 1
ELSE
RETURN 0

but its not a good idea to Return a value from an Sproc like this - Return should be reserved for Errors / RAISERROR etc.

Kristen

Boersnoes
Starting Member

6 Posts

Posted - 2007-05-29 : 08:06:35

Thx Kristen :)
I'm still finding my way in the SQL world.

Is it possible to do this:

SELECT /*permisionResult*/, RoleName, Description
FROM aspnet_Roles

permissionResult should be a bit obtained by the folowing query for every row in the resultset obtained by the previous query.

SELECT 1
FROM Emendo_FilesInRoles
WHERE fileId = '@fileId' AND roleId = roleIdOfThisRow

e.g. GetAllRolesWithPermisionField(11) would return

1 admin administrator of the site
0 usr1 restricted user
0 usr2 also a restricted user
1 usr3 has permission to file 11

thx in advance

Kristen
Test

22859 Posts

Posted - 2007-05-29 : 08:46:24

If the FROM / WHERE is the same in both then "no"

Boersnoes
Starting Member

6 Posts

Posted - 2007-05-29 : 09:05:02

Sorry for screwing up your answer, but I edited the post to something a bit different ;)

Boersnoes
Starting Member

6 Posts

Posted - 2007-05-29 : 09:48:54

I thought

SELECT R.*, CASE
		WHEN EXISTS(
			SELECT FIR.*
			FROM emendo_FilesInRoles AS FIR
			WHERE FIR.fileId = 29)
		THEN 1
		ELSE 0
	END
	AS permission
FROM aspnet_Roles AS R
LEFT OUTER JOIN emendo_FilesInRoles AS FIR
ON (R.roleId = FIR.roleId)

would do the trick, but off course I then just get all permission = 1 since it just checks on FIR.fileId = 29 and not on FIR.roleId = //currentRole//

But it's the //currentRole// I'm stuck at. Is there such a mechanism?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-31 : 04:05:03

Maybe
SELECT ABS(COUNT(*)) FROM SomeTable WHERE SomeColumn = @SomeValue

But I think it will be slower.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-31 : 04:37:06

ABS(COUNT(*)) ??

how can count be negative?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-31 : 04:44:43

Just making sure that only 1 and 0 are returned...
I know VB and VBA treats all values different from 0 as TRUE, and only 0 as FALSE.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-31 : 04:51:54

ok.... but i still don't see a point...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-05-31 : 05:30:33

"But I think it will be slower."

Yup! EXISTS will "stop" when it finds the first matching record.

Kristen

Boersnoes
Starting Member

6 Posts

Posted - 2007-05-31 : 11:10:08

I solved my problem in te program itself (ASP.net). First I thought I could use a CURSOR, but as all articles say they are pure evil and therefor should never be used in applications, I just did it in my C# code.

Thx for the thinkwork :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources