Function in WHERE clause - 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 2000 Forums

SQL Server Development (2000)

Function in WHERE clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-03-17 : 06:23:03

Brian writes "Hello,

I'd like to create a function that returns an often-used comma-separated list of strings that I can use in the WHERE clause of a SELECT statement.

Basically, I'd like to replace this:
SELECT * FROM myTable WHERE myField IN ('val1','val2','val3')

with this:
SELECT * FROM myTable WHERE myField IN (dbo.myFunction())

That way when the list of values needs to change, I can just change it in the function and not in each query. I've tried a couple of different things and although I'm not receiving any errors, I'm also not getting any data returned. Any thoughts?'

I'm using SQL-Server 2000

Thanks in advance!
Brian"

Bambola
Posting Yak Master

103 Posts

Posted - 2003-03-17 : 06:44:53

Basically what you have to do is create a function that accepts as a parameter a string and a delimiter, and returns a table datatype.
In the function loop through this string and insert all values into the table datatype.
Something like

CREATE FUNCTION myFunction(@sString varchar(8000), @delimiter char(1) )
RETURNS @tElements table (A_Element varchar(500))
AS
BEGIN
-- {parse the string here, and insert values in table}
RETURN
END

You refer to this function as a table, so your select statement will look like this:

SELECT * FROM myTable WHERE myField IN (SELECT A_Element FROM dbo.myFunction(@sString, @delimiter))

OR

SELECT * FROM myTable
INNER JOIN dbo.myFunction(@sString, @delimiter)
ON A_Element = myField

Bambola.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-17 : 08:58:33

search the site for CSV there is a function somewhere that does exactly what you are looking for.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources