compare two lists - 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 2008 Forums

Transact-SQL (2008)

compare two lists

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-09-27 : 10:54:46

I am working on a database which has a column owners (it contains a list of owner ids separated by commas) I am not the one who designed it this way.
I need a stored procedure that takes an argument ids which is a list of ids say 1,3,4,6
and it has to pull all records that any of those ids exist in any of the owners column id it will pull the following records
record1 owners 1,7,8
record2 owneres 1,3,9
I have a function split that will take the list which is sent as argument and produces a table value i.e. it will take 1,3,4,6,9
and produces a table with the following rows
1
3
4
6
9
I do not want to use a cursor
i.e I do not want to create a cursor that passes through the rows of my temp table and finds all records that have 1 in its owner column
then takes 3 and do the same.
I hate cursors is there is another better way to do that.
I am not using .net
Thanks

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 11:50:10

once you get comma seperated list into table using function its just a matter of doing like


SELECT t.columns..
FROM dbo.YourFunc(..)f
INNER JOIN YourTable t
ON ',' + t.ownerid + ',' LIKE '%,' + f.arrayvalue + ',%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-09-28 : 09:37:33

Thanks

sarah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources