Exists command - 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)

Exists command

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-17 : 00:32:48

I want to write a query like this:

IF 'admin' EXISTS IN(SELECT Rolename FROM Roles
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid =@Userid )

I mean if one of the result of the select command be 'admin' do something. how should i write it?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-17 : 00:50:41

some thing like this

DECLARE @Role VARCHAR(256)
SELECT @Role = Rolename FROM Roles
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid =@Userid

IF @Role = 'admin'

stmts1

else

stmts2

If u have one role per a given userid else declare a table variable instead of A single variable (@role)

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-17 : 01:08:24

No, it has more than one role as result. how should i declare table?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-17 : 01:12:48

IF EXISTS IN(SELECT 1 FROM Roles where rolename in ('admin','developer',....)
statements
else
statements

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-17 : 01:13:21

TRY THIS

DECLARE @Role table ( RoleName VARCHAR(256))
INSERT INTO @Role
SELECT Rolename FROM Roles
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid = @Userid

IF EXISTS ( SELECT 1 FROM @Role WHERE RoleName = 'ADMIN')

stmts1

else

stmts2

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-17 : 02:00:00

I wrote:

DECLARE @Role table ( RoleName VARCHAR(256))
INSERT INTO @Role
SELECT Rolename FROM Roles
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid = @Userid
IF EXISTS ( SELECT * FROM @Role WHERE RoleName = 'admin')
begin
SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part= 'admin'
end

but the select in the if clause, doesnt show any result when execute, and only write (3 row(s) affected)!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 02:00:56

quote:
Originally posted by bklr

IF EXISTS IN(SELECT 1 FROM Roles where rolename in ('admin','developer',....)
statements
else
statements

no need of IN. just use


IF  EXISTS(SELECT 1 FROM Roles where rolename in ('admin','developer',....)
statements
else
statements

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-17 : 02:05:51

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

IF EXISTS IN(SELECT 1 FROM Roles where rolename in ('admin','developer',....)
statements
else
statements

no need of IN. just use

IF EXISTS(SELECT 1 FROM Roles where rolename in ('admin','developer',....)
statements
else
statements

k visakh
just copy & paste error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 02:08:45

quote:
Originally posted by bklr

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

IF EXISTS IN(SELECT 1 FROM Roles where rolename in ('admin','developer',....)
statements
else
statements

no need of IN. just use

IF EXISTS(SELECT 1 FROM Roles where rolename in ('admin','developer',....)
statements
else
statements

k visakh
just copy & paste error

Ok agreed...

just wanted to tell you to test solutions before posting as i've seen couple of occasions where you've posted solution which was not even syntactically correct

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-17 : 02:11:34

quote:
Originally posted by Exir

I wrote:

DECLARE @Role table ( RoleName VARCHAR(256))
INSERT INTO @Role
SELECT Rolename FROM Roles
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid = @Userid
IF EXISTS ( SELECT * FROM @Role WHERE RoleName = 'admin')
begin
SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part= 'admin'
end

but the select in the if clause, doesnt show any result when execute, and only write (3 row(s) affected)!

did you see my post? why the select in the if clause doesnt show any resault? only write 3 rows affected? i want the resault of the select

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 02:16:16

you mean select inside IF EXISTS(). the EXISTS clause just checks the internal results and returns a boolean value (either there's a valid result or not), the results of inside select wont be echoed back along with output. can i ask what your exact requirement is? also give some sample data if you can

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-17 : 02:19:55

quote:
Originally posted by Exir

quote:
Originally posted by Exir

I wrote:

DECLARE @Role table ( RoleName VARCHAR(256))
INSERT INTO @Role
SELECT Rolename FROM Roles
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid = @Userid
IF EXISTS ( SELECT * FROM @Role WHERE RoleName = 'admin')
begin
SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part= 'admin'
end

but the select in the if clause, doesnt show any result when execute, and only write (3 row(s) affected)!

did you see my post? why the select in the if clause doesnt show any resault? only write 3 rows affected? i want the resault of the select

try this

DECLARE @Role table ( RoleName VARCHAR(256))
INSERT INTO @Role
SELECT Rolename FROM Roles
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid = @Userid
IF EXISTS ( SELECT 1 FROM @Role WHERE RoleName = 'admin')
begin
SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part= 'admin'
end

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-17 : 02:26:46

I have a userid(@Userid) I want the SP check the roles wich the userid of them = (@Userid), then check if the 'admin' role exists among those roles, and if the admin was there, show all rows from other table (Emprequest) which Emprequest.Part= 'admin'

i hope i could define simply and understandable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 02:32:25

do you mean this


SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part= 'admin'
AND EXISTS (SELECT  1 FROM Roles 
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid = @Userid
AND Rolename='Admin')

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-17 : 02:37:57

k thanks for ur suggestion
max i will check the output then i will send

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-18 : 02:26:10

quote:

Originally posted by visakh16

do you mean this


SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part= 'admin'
AND EXISTS (SELECT  1 FROM Roles 
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid = @Userid
AND Rolename='Admin')

visakh or anyone who can help:

how can i use thi s form with if statement or case?
I mean if something like this:

first:
SELECT Rolename FROM Roles
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid =@Userid ----> the result would have more than one row

secondly:
if there is 'admin' among the result of the previous select , then show this: SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part='part1'

else:
if there is 'subscribers' among the result of the previous select , then show this: SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part='part2'

else:
if there is 'administrators' among the result of the previous select , then show this: SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part='part3'

else: ...

and so on

could you please help me with writing this query?

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-18 : 02:50:28

I wrote this:

DECLARE @Role table ( RoleName VARCHAR(256))
INSERT INTO @Role
SELECT Rolename FROM Roles
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid =@userid

IF EXISTS ( SELECT 1 FROM @Role WHERE RoleName = 'admin')
begin
SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part= 'part1'
end

else

IF EXISTS ( SELECT 1 FROM @Role WHERE RoleName = 'subscribers')
begin
SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part= 'part2'
end

and so on
but
as visakh said:
the EXISTS clause just checks the internal results and returns a boolean value (either there's a valid result or not), the results of inside select wont be echoed back along with output.

the exist doesnt show any result. how can i write a code to be able to see the select result?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-18 : 08:59:39

quote:
Originally posted by Exir

quote:
Originally posted by visakh16

do you mean this

SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part= 'admin'
AND EXISTS (SELECT 1 FROM Roles
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid = @Userid
AND Rolename='Admin')

visakh or anyone who can help:

how can i use thi s form with if statement or case?
I mean if something like this:

first:
SELECT Rolename FROM Roles
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid =@Userid ----> the result would have more than one row

secondly:
if there is 'admin' among the result of the previous select , then show this: SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part='part1'

else:
if there is 'subscribers' among the result of the previous select , then show this: SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part='part2'

else:
if there is 'administrators' among the result of the previous select , then show this: SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part='part3'

else: ...

and so on

could you please help me with writing this query?

what you asked can be obtained by


IF EXISTS(SELECT 1 FROM Roles 
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid =@Userid
AND Rolename='admin')
 SELECT * FROM Emprequest
 INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId =     Emprequest.Id
 WHERE Emprequest.Part='part1'
ELSE IF EXISTS(SELECT 1 FROM Roles 
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid =@Userid
AND Rolename='subscribers')
  SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part='part2'
ELSE IF...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-18 : 09:02:03

quote:
Originally posted by Exir

I wrote this:

DECLARE @Role table ( RoleName VARCHAR(256))
INSERT INTO @Role
SELECT Rolename FROM Roles
INNER JOIN UserRoles ON UserRoles.ROleId = Roles.RoleId
WHERE UserRoles.Userid =@userid

IF EXISTS ( SELECT 1 FROM @Role WHERE RoleName = 'admin')
begin
SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part= 'part1'
end

else

IF EXISTS ( SELECT 1 FROM @Role WHERE RoleName = 'subscribers')
begin
SELECT * FROM Emprequest
INNER JOIN EmpRequestCondition ON EmpRequestCondition.PursueId = Emprequest.Id
WHERE Emprequest.Part= 'part2'
end

and so on
but
as visakh said:
the EXISTS clause just checks the internal results and returns a boolean value (either there's a valid result or not), the results of inside select wont be echoed back along with output.

the exist doesnt show any result. how can i write a code to be able to see the select result?

Did understand why you want to see the result of condition check. it will be always boolean (either true (1) or False(0)). Shouldnt you be interested only in other selects?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources