Calling UDF in where clause

[David]

I am new to user-defined functions in SQL Server. Can someone please shed
some light on my question.

I am trying to call a user-defined function from within a where clause but I
am getting errors. If I move the same UDF call to the 'select' part, it
works. It appears to me that it is not possible to call a UDF from within a
where clause but I am not really sure. Can someone please let me know if it
is possible or not.

[Tracy McKibben]

Post by David
I am new to user-defined functions in SQL Server. Can someone please shed
some light on my question.
I am trying to call a user-defined function from within a where clause but I
am getting errors. If I move the same UDF call to the 'select' part, it
works. It appears to me that it is not possible to call a UDF from within a
where clause but I am not really sure. Can someone please let me know if it
is possible or not.

Works perfectly... Post your query and your error message...

[David]

That's good news.

If I do it this way, it works great:
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (md_group_id = 2)

If I try this, it doesn't work.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE dbo.udfTest()

I get this error and I have already tried everything I could think of.
"Line 1: Incorrect syntax near ')'. "
and
"Error in list of function arguments: 'dbo' not recognized. Unable to
parse query text."

Here is my UDF function:
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '(id = 2)'
END

Post by Tracy McKibben

Post by David
I am new to user-defined functions in SQL Server. Can someone please shed
some light on my question.
I am trying to call a user-defined function from within a where clause but I
am getting errors. If I move the same UDF call to the 'select' part, it
works. It appears to me that it is not possible to call a UDF from within a
where clause but I am not really sure. Can someone please let me know if it
is possible or not.

Works perfectly... Post your query and your error message...

[Arnie Rowland]

Where clause expects some form of evaluation. You are just providing a value
(the results of the udf).

WHERE dbo.udfTest()
is like
WHERE 2

So, add the evaluation criteria, compare the results of the udf to
something. For example,

WHERE dbo.udfTest() <> 0
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Post by David
That's good news.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (md_group_id = 2)
If I try this, it doesn't work.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE dbo.udfTest()
I get this error and I have already tried everything I could think of.
"Line 1: Incorrect syntax near ')'. "
and
"Error in list of function arguments: 'dbo' not recognized. Unable to
parse query text."
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '(id = 2)'
END

Post by Tracy McKibben

Post by David
I am new to user-defined functions in SQL Server. Can someone please shed
some light on my question.
I am trying to call a user-defined function from within a where clause but I
am getting errors. If I move the same UDF call to the 'select' part, it
works. It appears to me that it is not possible to call a UDF from within a
where clause but I am not really sure. Can someone please let me know if it
is possible or not.

Works perfectly... Post your query and your error message...

[ML]

That's not the way UDFs work in SQL. The function returns a value that can be
returned (if called from the SELECT statement) or that can be compared to
another value (if called from within the WHERE/HAVING clauses).

Like this:

SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (dbo.udfTest() = <some value or column name>)

Maybe:

SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (dbo.udfTest() = id)


Change your function to return a value, rather than what looks like part of
some dynamic SQL statement:

CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '2'
END


ML

---
http://milambda.blogspot.com/

[David]

So, I am not able to return a string from my UDF that has the value and the
column name?
something like: (column_name=2)

Post by ML
That's not the way UDFs work in SQL. The function returns a value that can be
returned (if called from the SELECT statement) or that can be compared to
another value (if called from within the WHERE/HAVING clauses).
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (dbo.udfTest() = <some value or column name>)
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (dbo.udfTest() = id)
Change your function to return a value, rather than what looks like part of
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '2'
END
ML
---
http://milambda.blogspot.com/

[ML]

You could, but why? It's best to avoid dynamic SQL. Here's a very nice
article on dynamic SQL by Erland Sommarskog (a must-read):

http://www.sommarskog.se/dynamic_sql.html


ML

---
http://milambda.blogspot.com/

[Mike C#]

You're trying to use a VARCHAR as a boolean comparison in your WHERE clause.
That doesn't work unless you're using dynamic SQL, which you're not.

Post by David
That's good news.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (md_group_id = 2)
If I try this, it doesn't work.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE dbo.udfTest()
I get this error and I have already tried everything I could think of.
"Line 1: Incorrect syntax near ')'. "
and
"Error in list of function arguments: 'dbo' not recognized. Unable to
parse query text."
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '(id = 2)'
END

Post by Tracy McKibben

Post by David
I am new to user-defined functions in SQL Server. Can someone please shed
some light on my question.
I am trying to call a user-defined function from within a where clause but I
am getting errors. If I move the same UDF call to the 'select' part, it
works. It appears to me that it is not possible to call a UDF from within a
where clause but I am not really sure. Can someone please let me know if it
is possible or not.

Works perfectly... Post your query and your error message...

[David]

Thanks for everyone help. I guess I can't use UDF in the way that I thought
I could. Let me explain what I'm trying to do and let me know the best way I
should do it.

I am trying to construct my where clause in a way where the comparison (like
'=', '>=', or '<=') would change depending on a value from another column. I
thought that I could use UDF to return a string but I guess I can't. Does
anyone else have anymore ideas. What about a case statement? Would that
work?

Post by Mike C#
You're trying to use a VARCHAR as a boolean comparison in your WHERE clause.
That doesn't work unless you're using dynamic SQL, which you're not.

Post by David
That's good news.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (md_group_id = 2)
If I try this, it doesn't work.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE dbo.udfTest()
I get this error and I have already tried everything I could think of.
"Line 1: Incorrect syntax near ')'. "
and
"Error in list of function arguments: 'dbo' not recognized. Unable to
parse query text."
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '(id = 2)'
END

Post by Tracy McKibben

Post by David
I am new to user-defined functions in SQL Server. Can someone please shed
some light on my question.
I am trying to call a user-defined function from within a where clause but I
am getting errors. If I move the same UDF call to the 'select' part, it
works. It appears to me that it is not possible to call a UDF from within a
where clause but I am not really sure. Can someone please let me know if it
is possible or not.

Works perfectly... Post your query and your error message...

[Mike C#]

DECLARE @which_operator VARCHAR(2)
SELECT @which_operator = '='

SELECT activity_id, md_group_id
FROM dbo.ImpactedMD
WHERE (@which_operator = '=' AND md_group_id = 2)
OR (@which_operator = '<=' AND md_group_id <= 2)
OR (@which_operator = '>=' AND md_group_id >= 2)

I don't know if the OR's will have a seriously adverse affect on your query
time or not... depends on if SQL Server is smart enough to short-circuit the
WHERE clause.

Post by David
Thanks for everyone help. I guess I can't use UDF in the way that I thought
I could. Let me explain what I'm trying to do and let me know the best way I
should do it.
I am trying to construct my where clause in a way where the comparison (like
'=', '>=', or '<=') would change depending on a value from another column.
I
thought that I could use UDF to return a string but I guess I can't.
Does
anyone else have anymore ideas. What about a case statement? Would that
work?

Post by Mike C#
You're trying to use a VARCHAR as a boolean comparison in your WHERE clause.
That doesn't work unless you're using dynamic SQL, which you're not.

Post by David
That's good news.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (md_group_id = 2)
If I try this, it doesn't work.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE dbo.udfTest()
I get this error and I have already tried everything I could think of.
"Line 1: Incorrect syntax near ')'. "
and
"Error in list of function arguments: 'dbo' not recognized. Unable to
parse query text."
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '(id = 2)'
END

Post by Tracy McKibben

Post by David
I am new to user-defined functions in SQL Server. Can someone
please
shed
some light on my question.
I am trying to call a user-defined function from within a where
clause
but I
am getting errors. If I move the same UDF call to the 'select'
part,
it
works. It appears to me that it is not possible to call a UDF from within a
where clause but I am not really sure. Can someone please let me
know
if it
is possible or not.

Works perfectly... Post your query and your error message...

[David]

Thanks! This helps! I don't know why I didn't think of this before.

Post by David
SELECT activity_id, md_group_id
FROM dbo.ImpactedMD
I don't know if the OR's will have a seriously adverse affect on your query
time or not... depends on if SQL Server is smart enough to short-circuit the
WHERE clause.

Post by David
Thanks for everyone help. I guess I can't use UDF in the way that I thought
I could. Let me explain what I'm trying to do and let me know the best way I
should do it.
I am trying to construct my where clause in a way where the comparison (like
'=', '>=', or '<=') would change depending on a value from another column.
I
thought that I could use UDF to return a string but I guess I can't.
Does
anyone else have anymore ideas. What about a case statement? Would that
work?

Post by Mike C#
You're trying to use a VARCHAR as a boolean comparison in your WHERE clause.
That doesn't work unless you're using dynamic SQL, which you're not.

Post by David
That's good news.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (md_group_id = 2)
If I try this, it doesn't work.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE dbo.udfTest()
I get this error and I have already tried everything I could think of.
"Line 1: Incorrect syntax near ')'. "
and
"Error in list of function arguments: 'dbo' not recognized. Unable to
parse query text."
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '(id = 2)'
END

Post by Tracy McKibben

Post by David
I am new to user-defined functions in SQL Server. Can someone
please
shed
some light on my question.
I am trying to call a user-defined function from within a where
clause
but I
am getting errors. If I move the same UDF call to the 'select'
part,
it
works. It appears to me that it is not possible to call a UDF from
within a
where clause but I am not really sure. Can someone please let me
know
if it
is possible or not.

Works perfectly... Post your query and your error message...

[ML]

A CASE expression might help, but this sounds like a job for dynamic SQL.
Have you read Erland's article? Have you also considered all the options?

Maybe you should give a more thorough example of your requirements.


ML

---
http://milambda.blogspot.com/