use WHERE IN with values passed into a udf

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)

use WHERE IN with values passed into a udf

Author

Topic

jammus
Starting Member

6 Posts

Posted - 2008-01-10 : 10:26:18

Hello everyone,

I wonder if anyone can help me.

This is the functionality I want:
SELECT *
FROM table_1
WHERE id IN (10, 11, 12)

However I want to be able to pass in a list of values for the sub query. Something like this:

SELECT *
FROM table_1
WHERE id IN (@list_of_values)

When @list_of_values = '10', there's no problem. However it complains (and rightly so) when I supply a list such as when @list_of_values = '10, 11, 12'.

Anyone got any tips as to how I might go about resolving this?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 10:36:15

You need to do it as dynamic sql.

DECLARE @Sql varchar(8000)

SET @Sql='SELECT *
FROM table_1
WHERE id IN (' + @list_of_values + ')'

EXEC (@Sql)

If you need to avoid dynamic sql, you need to write a UDF which split the comma seperated values to table of values and take inner join with it.

jammus
Starting Member

6 Posts

Posted - 2008-01-10 : 10:39:26

thanks for your reply.

yeah, i'd like it to remain in a udf so dynamic sql would be out of the question. splitting into tables sounds like the way to go. i'll give it a go.

cheers

jammus
Starting Member

6 Posts

Posted - 2008-01-10 : 10:41:18

a quick google gives me almost exactly what i want:

http://www.4guysfromrolla.com/webtech/031004-1.shtml

sorry, looks like i broke my "don't ask what you can google" rule :)

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-10 : 10:42:42

You could load a variable table with your values and reference them like this:

DECLARE @ValuesTable TABLE ( ColumnValues INT )

INSERT @ValuesTable ( ColumnValues )
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12

SELECT *
FROM table_1
WHERE id IN ( SELECT ColumnValues FROM @ValuesTable )

This would avoid using dynamic sql.

jammus
Starting Member

6 Posts

Posted - 2008-01-10 : 11:08:48

Thanks for the help. I'd like the function to be able to cope with any value without me having to update it to add new ones. My query now looks like this and works fine:

SELECT *
FROM table_1
WHERE id IN
(SELECT CONVERT(int, strValue) FROM udf_general_Split(@list_of_ids, ','))

with udf_general_split being taken from the 4guys link

CREATE FUNCTION dbo.udf_general_Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(

Id int identity(1,1),
strValue nvarchar(100)
)
AS
BEGIN
WHILE (Charindex(@SplitOn,@List)>0)
BEGIN
INSERT INTO @RtnValue (strValue)
SELECT
strValue = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
END
Insert Into @RtnValue (strValue)
Select strValue = ltrim(rtrim(@List))
RETURN
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources