Calling UDF in where clause

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 i
t
is possible or not.David wrote:
> 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...|||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
"Tracy McKibben" wrote:

> David wrote:
> Works perfectly... Post your query and your error message...
>|||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
"David" <dilworth@.newsgroups.nospam> wrote in message
news:CECD5327-DC62-4C47-B998-B9C691CF2A06@.microsoft.com...
> 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
>
> "Tracy McKibben" wrote:
>|||That's not the way UDFs work in SQL. The function returns a value that can b
e
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/|||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)
"ML" wrote:

> 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 o
f
> some dynamic SQL statement:
> CREATE FUNCTION dbo.udfTest()
> RETURNS varchar(255) AS
> BEGIN
> return '2'
> END
>
> ML
> --
> http://milambda.blogspot.com/|||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.
"David" <dilworth@.newsgroups.nospam> wrote in message
news:CECD5327-DC62-4C47-B998-B9C691CF2A06@.microsoft.com...
> 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
>
> "Tracy McKibben" wrote:
>|||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/|||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?
"Mike C#" wrote:

> You're trying to use a VARCHAR as a boolean comparison in your WHERE claus
e.
> That doesn't work unless you're using dynamic SQL, which you're not.
> "David" <dilworth@.newsgroups.nospam> wrote in message
> news:CECD5327-DC62-4C47-B998-B9C691CF2A06@.microsoft.com...
>
>|||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.
"David" <dilworth@.newsgroups.nospam> wrote in message
news:99AACBDE-6EFA-4863-84D6-404759E3C4A3@.microsoft.com...
> 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?
> "Mike C#" wrote:
>