CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005stored-procedures
David Brunelle
David Brunelle

Reputation: 6450

Use of the IN condition

I can easily create a stored procedure in SQL Server with parameters that I use with =, LIKE and most operators. But when it comes to using IN, I don't really understand what to do, and I can't find a good site to teach me.

Example

    CREATE PROCEDURE TEST 
    @Ids --- What type should go here?
    AS BEGIN
        SELECT * FROM TableA WHERE ID IN ( @Ids )
    END

Is this possible and if so how ?

Upvotes: 0

Views: 78

Answers (3)

tlbignerd
tlbignerd

Reputation: 1114

I've done this in the past using a Split function that I add to my schema functions as described here

Then you can do the following:

CREATE PROCEDURE TEST 
@Ids --- What type should go here?
AS BEGIN

    SELECT * FROM TableA WHERE ID IN ( dbo.Split(@Ids, ',') )
END

Just remember that the IN function always expects a table of values as a result. SQL Server is smart enough to convert strings to this table format, so long as they are specifically written in the procedure.

Another option in your specific example though, could be to use a join. This will have a performance improvement, but often does not really meet a real-world example you need. The join version would be:

SELECT * 
FROM TableA AS ta 
    INNER JOIN dbo.Split(@Ids, ',') AS ids 
        ON ta.Id = ids.items

Upvotes: 1

user1853517
user1853517

Reputation:

If your asking what I think your asking, I do this every day..

WITH myData(FileNames)
AS
(
    SELECT '0608751970%'
    UNION ALL SELECT '1000098846%'
    UNION ALL SELECT '1000101277%'
    UNION ALL SELECT '1000108488%'
)
SELECT DISTINCT f.* 
FROM tblFiles (nolock) f
INNER JOIN myData md 
    ON b.FileNames LIKE md.FileNames

Or if your doing this based on another table:

WITH myData(FileNames)
AS
(
    SELECT RTRIM(FileNames) + '%'
    FROM tblOldFiles
    WHERE Active=1
)
SELECT DISTINCT f.* 
FROM tblFiles (nolock) f
INNER JOIN myData md 
    ON b.FileNames LIKE md.FileNames

Upvotes: 0

Oded
Oded

Reputation: 499062

With SQL Server 2008 and above, you can use Table Valued Parameters.

You declare a table type and can use that as a parameter (read only) for stored procedures that can be used in IN clauses.

For the different options, I suggest reading the relevant article for your version of the excellent Arrays and Lists in SQL Server, by Erland Sommarskog.

Upvotes: 3

Related Questions