Passing comma separated values for IN list from SQLCMD

Posted on 2012-09-04
Last Modified: 2012-11-15

Following are the contents of script Delete_employees.sql.
USE CompanyDB


DELETE FROM dbo.employee
WHERE empid IN (<Comma separated values passed from SQLCMD>)


Open in new window

Is it possible to pass comma separate values from SQLCMD for the IN Clause in DELETE statement?

I tried running the above but it does seem to work:
sqlcmd -S localhost\testEXPRESS -i Delete_employees.sql -v empid=100

Open in new window

When I change the IN clause to = in delete sql statement within Delete_employees.sql script, the above SQLCMD works.

But I want to be able to pass comma separate employee ids (which is a INT) for the IN clause in DELETE statement.

Can somebody help me figure out a way to do that?

Question by:sath350163
    LVL 18

    Accepted Solution

    you can build a sql string and execute it using EXECUTE

    just make sure the string you build is literally what you want to execute


    the other tougher way is to parser the list of id's using SQL string functions, drop the values in a temp table or table variable, then
    delete from X where ID in (select id from temptable)

    have done that, would need to look out code, the 'build a string' method is fastest to develop
    LVL 19

    Assisted Solution

    Hi sath350163,

    You can use XML to quickly parse delimited string:

    DECLARE @emplist VARCHAR(500)
    SET @emplist = '1,2,3'
    DECLARE @xml XML
    SET @xml = cast(('<X>'+replace(@emplist,',' ,'</X><X>')+'</X>') as xml)
    DELETE dbo.employee WHERE empid In (SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N))

    Open in new window


    Author Comment


    Got the below Table-Valued Function logic from: (Click on Erland's CLR version under CLR) via the link you provided

    Table-Valued Function that unpacks the string into a table:

    CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
    RETURNS @tbl TABLE (number int NOT NULL) AS
    DECLARE @pos int,
    @nextpos int,
    @valuelen int
    SELECT @pos = 0, @nextpos = 1
    WHILE @nextpos > 0
    SELECT @nextpos = charindex(',', @list, @pos + 1)
    SELECT @valuelen = CASE WHEN @nextpos > 0
    THEN @nextpos
    ELSE len(@list) + 1
    END - @pos - 1
    INSERT @tbl (number)
    VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
    SELECT @pos = @nextpos

    Open in new window

    The function iterates over the string looking for commas, and extracts the values one by one. The code is straightforward, and makes use of some of the string functions in <small>T-SQL</small>. The most complex part is the <small>CASE</small> expression which exists to handle the last value in the string correctly. This example shows how you could use this function:

    CREATE PROCEDURE get_product_names_iter @ids varchar(50) AS
    SELECT P.ProductName, P.ProductID
    FROM Northwind..Products P
    JOIN iter$simple_intlist_to_tbl(@ids) i ON P.ProductID = i.number
    EXEC get_product_names_iter '9, 12, 27, 37'

    Open in new window

    The article does mention that the above function above is not extremely speedy.

    Though my database is in SQL SERVER 2008, I used the above Table-Values Function approach over Table-valued Parameter logic mentioned in since in my usecase there wont be more than 30 to 35 items in comma separated list. Moreover, with the above non-TVP approach, I dont have to create a new TYPE object.

    Please let me know if the choice is inefficent for my usecase.


    Author Comment

    I've requested that this question be closed as follows:

    Accepted answer: 0 points for sath350163's comment #a38381556
    Assisted answer: 250 points for deighton's comment #a38366984
    Assisted answer: 250 points for Rimvis's comment #a38367717

    for the following reason:

    Solution worked for my use case

    Author Comment


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now