EXISTS Clause behavior

I'm not posting the table schemas here because I don't believe they're
relevant to the issue at hand. I've written the following function to
perform a security check. I believe it to be syntactically correct;
however, SQL Server is having trouble resolving a table reference in my
query. Any thoughts?
-Alan
This is the error I'm receiving:
Server: Msg 107, Level 16, State 3, Procedure UserHasReportAccess, Line
8
The column prefix 'GroupUsers' does not match with a table name or
alias name used in the query.
And here is my source (sorry if things wrap):
CREATE FUNCTION dbo.UserHasReportAccess
(
@.LoginName VARCHAR(50)
)
RETURNS CHAR(1)
AS
BEGIN
RETURN CASE
WHEN EXISTS (SELECT * FROM ReportDefinitions WHERE ReportOwner =
@.LoginName)
OR
EXISTS (SELECT * FROM ReportPermissions WHERE MemberType = 'User'
AND MemberName = @.LoginName)
OR
EXISTS
(
SELECT *
FROM GroupUsers
WHERE UserLoginName = @.LoginName
AND EXISTS
(
SELECT *
FROM ReportPermissions
WHERE MemberType = 'Group'
AND MemberName = GroupUsers.GroupName
)
)
THEN 'Y'
ELSE 'N'
END
ENDAs a note, I performed a workaround using a JOIN clause instead of
EXISTS, I'm still curious as to why what I've posted doesn't work.|||Alan Samet wrote:
> I'm not posting the table schemas here because I don't believe they're
> relevant to the issue at hand. I've written the following function to
> perform a security check. I believe it to be syntactically correct;
> however, SQL Server is having trouble resolving a table reference in
> my query. Any thoughts?
> -Alan
> This is the error I'm receiving:
> Server: Msg 107, Level 16, State 3, Procedure UserHasReportAccess,
> Line 8
> The column prefix 'GroupUsers' does not match with a table name or
> alias name used in the query.
>
<snip>
> (
> SELECT *
> FROM ReportPermissions
> WHERE MemberType = 'Group'
> AND MemberName = GroupUsers.GroupName
Try running this query on its own and see what happens:
SELECT *
FROM ReportPermissions
WHERE MemberType = 'Group'
AND MemberName = GroupUsers.GroupName
There is no object named GroupUsers in the query's FROM clause
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||The sub query cannot refer to a value from another query. iI doens't "know"
about the other query. The proper way to handle that secion would be with a
join so you get them to execute within the same query.
SELECT g.*
FROM GroupUser g
INNER JOIN ReportPermissions p ON g.GroupName = p.MemberName
WHERE g.UserLoginName = @.LoginName AND p.MemberType = 'Group'
HTH,
John Scragg
"Alan Samet" wrote:

> I'm not posting the table schemas here because I don't believe they're
> relevant to the issue at hand. I've written the following function to
> perform a security check. I believe it to be syntactically correct;
> however, SQL Server is having trouble resolving a table reference in my
> query. Any thoughts?
> -Alan
> This is the error I'm receiving:
> Server: Msg 107, Level 16, State 3, Procedure UserHasReportAccess, Line
> 8
> The column prefix 'GroupUsers' does not match with a table name or
> alias name used in the query.
> And here is my source (sorry if things wrap):
> CREATE FUNCTION dbo.UserHasReportAccess
> (
> @.LoginName VARCHAR(50)
> )
> RETURNS CHAR(1)
> AS
> BEGIN
> RETURN CASE
> WHEN EXISTS (SELECT * FROM ReportDefinitions WHERE ReportOwner =
> @.LoginName)
> OR
> EXISTS (SELECT * FROM ReportPermissions WHERE MemberType = 'User'
> AND MemberName = @.LoginName)
> OR
> EXISTS
> (
> SELECT *
> FROM GroupUsers
> WHERE UserLoginName = @.LoginName
> AND EXISTS
> (
> SELECT *
> FROM ReportPermissions
> WHERE MemberType = 'Group'
> AND MemberName = GroupUsers.GroupName
> )
> )
> THEN 'Y'
> ELSE 'N'
> END
> END
>|||That doesn't matter -- the GroupUsers reference refers to the table in
the outer query.
The following query should illustrate what I'm observing using the
Northwind database:
USE Northwind
GO
SELECT CASE
WHEN EXISTS
(
SELECT *
FROM Customers
WHERE CustomerID = 'PARIS'
AND EXISTS
(
SELECT *
FROM Orders
WHERE CustomerID =
Customers.CustomerID
)
)
THEN 'Y'
ELSE 'N'
END
GO
CREATE FUNCTION dbo.Test()
RETURNS CHAR(1)
AS
BEGIN
RETURN CASE
WHEN EXISTS
(
SELECT *
FROM Customers
WHERE CustomerID = 'PARIS'
AND EXISTS
(
SELECT *
FROM Orders
WHERE CustomerID =
Customers.CustomerID
)
)
THEN 'Y'
ELSE 'N'
END
END
GO|||In this situation, there is no outer query. A rows-returning query must
start with the word "SELECT", not RETURN. It must also have a FROM clause.
RETURN is a statement, not a sql statement keyword.
Alan Samet wrote:
> That doesn't matter -- the GroupUsers reference refers to the table in
> the outer query.
> The following query should illustrate what I'm observing using the
> Northwind database:
> USE Northwind
> GO
> SELECT CASE
> WHEN EXISTS
> (
> SELECT *
> FROM Customers
> WHERE CustomerID = 'PARIS'
> AND EXISTS
> (
> SELECT *
> FROM Orders
> WHERE CustomerID =
> Customers.CustomerID
> )
> )
> THEN 'Y'
> ELSE 'N'
> END
> GO
> CREATE FUNCTION dbo.Test()
> RETURNS CHAR(1)
> AS
> BEGIN
> RETURN CASE
> WHEN EXISTS
> (
> SELECT *
> FROM Customers
> WHERE CustomerID = 'PARIS'
> AND EXISTS
> (
> SELECT *
> FROM Orders
> WHERE CustomerID =
> Customers.CustomerID
> )
> )
> THEN 'Y'
> ELSE 'N'
> END
> END
> GO
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Bob Barrows [MVP] wrote:
> In this situation, there is no outer query. A rows-returning query
> must start with the word "SELECT", not RETURN. It must also have a
> FROM clause.
slight error here. A FROM clause is needed if table columns are referenced.
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||I think there's a slight misunderstanding here. Try running the script;
you'll see what I'm getting at. The first query in the batch is the
equivalent of calling the UDF as SELECT dbo.Test(). The query executes
and returns the correct result. Since a CASE statement returns a single
scalar value, I frequently write UDFs that are implemented in a single
line: RETURN <expression>. The UDF will compile with no problems when I
use a JOIN clause instead of related EXISTS clauses. Example:
CREATE FUNCTION dbo.Test()
RETURNS CHAR(1)
AS
BEGIN
RETURN CASE
WHEN EXISTS
(
SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerID = 'PARIS'
)
THEN 'Y'
ELSE 'N'
END
END
-Alan|||Maybe I did miss an end parenthesis there ...
You're saying that this query:
SELECT *
FROM GroupUsers
WHERE UserLoginName = @.LoginName
AND EXISTS
(
SELECT *
FROM ReportPermissions
WHERE MemberType = 'Group'
AND MemberName = GroupUsers.GroupName
)
works when run on its own, but fails when run in your function? I need to
try and reproduce this.Later.
Alan Samet wrote:
> I think there's a slight misunderstanding here. Try running the
> script; you'll see what I'm getting at. The first query in the batch
> is the equivalent of calling the UDF as SELECT dbo.Test(). The query
> executes and returns the correct result. Since a CASE statement
> returns a single scalar value, I frequently write UDFs that are
> implemented in a single line: RETURN <expression>. The UDF will
> compile with no problems when I use a JOIN clause instead of related
> EXISTS clauses. Example:
> CREATE FUNCTION dbo.Test()
> RETURNS CHAR(1)
> AS
> BEGIN
> RETURN CASE
> WHEN EXISTS
> (
> SELECT *
> FROM Customers
> INNER JOIN Orders
> ON Customers.CustomerID = Orders.CustomerID
> WHERE Customers.CustomerID = 'PARIS'
> )
> THEN 'Y'
> ELSE 'N'
> END
> END
> -Alan
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||My apologies: you are correct. I cannot provide an answer why the expression
fails in a function creation block. Hopefully someone else will step in.
Bob Barrows
Alan Samet wrote:
> I think there's a slight misunderstanding here. Try running the
> script; you'll see what I'm getting at. The first query in the batch
> is the equivalent of calling the UDF as SELECT dbo.Test(). The query
> executes and returns the correct result. Since a CASE statement
> returns a single scalar value, I frequently write UDFs that are
> implemented in a single line: RETURN <expression>. The UDF will
> compile with no problems when I use a JOIN clause instead of related
> EXISTS clauses. Example:
> CREATE FUNCTION dbo.Test()
> RETURNS CHAR(1)
> AS
> BEGIN
> RETURN CASE
> WHEN EXISTS
> (
> SELECT *
> FROM Customers
> INNER JOIN Orders
> ON Customers.CustomerID = Orders.CustomerID
> WHERE Customers.CustomerID = 'PARIS'
> )
> THEN 'Y'
> ELSE 'N'
> END
> END
> -Alan
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.