How to pass more than one id to stored procedure

Question

I have a query in a store procedure like this:

CREATE PROCEDURE DELETEREP (@id INT) 
AS 
    DELETE FROM TABLE 
    WHERE  ID = @id

Now I want to execute this store procedure from another stored procedure like this:

exec(Deleterep) from here i want to pass multiple ids like 

where  id in (selec id from table2)

I want to pass multiple ids to the SP. I don't want to call store procedure multiple time, using cursor or loop, I want to avoid it.

Can someone give me an idea of how to proceed?

What database are you using? SQLServer, MYSQL, Oracle? Also what version? Recent versions of SQLServer can use XML parameters — Jon P, Aug 28 '13 at 05:05
possible duplicate of [How do I execute a stored procedure once for each row returned by query?](http://stackoverflow.com/questions/886293/how-do-i-execute-a-stored-procedure-once-for-each-row-returned-by-query) — rs., Aug 28 '13 at 05:12
@rs i have gone through that link and that is using cursor, which will call the store procedure again and again i want to avoid it, please read the question again — M Akela, Aug 28 '13 at 05:14
You cannot do what you want without a cursor or at least a loop and without modifying the stored procedure. point. — alzaimar, Aug 28 '13 at 05:44
possible duplicate of [SQL Selecting a list of Ids and run them through SP](http://stackoverflow.com/questions/16428243/sql-selecting-a-list-of-ids-and-run-them-through-sp) — Aleksandr Fedorenko, Aug 28 '13 at 07:05
@pavezalam, what you are trying to achieve is not possible through SP, you cannot call stored procedure as part of a sql, you need to convert that to a scalar function. Only way to do this is through cursor or write a new SP that runs actual delete statement using two tables instead of calling another SP. — rs., Aug 28 '13 at 13:28

Roman Pekar · Answer 1 · 2013-08-28T06:59:55.667

2

you can use XML as Devart suggested, but here's two more options.

Comma separated list (you can split it with other methods, I've used convert to xml just here):

create procedure usp_test1(@list nvarchar(max))
as
begin
    declare @data xml

    select @data = cast('<id>' + replace(@list, ', ', '</id><id>') + '</id>' as xml)

    select *
    from test
    where id in
      (
          select t.c.value('.', 'int') as a
          from @data.nodes('id') as t(c)
      )
end

or user-defined table type:

create type list as table (id int primary key)

create procedure usp_test2(@list list readonly)
as
begin
    select *
    from test
    where id in (select id from @list)
end

see sql fiddle demo with 2 examples

edited Aug 28 '13 at 06:59

answered Aug 28 '13 at 05:40

Roman Pekar

107,110
28
195
197

2

[`CREATE PROCEDURE`](http://technet.microsoft.com/en-us/library/ms187926.aspx): "Avoid the use of the **sp_** prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name." – Damien_The_Unbeliever Aug 28 '13 at 06:48
@Damien_The_Unbeliever thanks in our system we're using `usp` for prefix, it was just for example, but you're right, will change the answer – Roman Pekar Aug 28 '13 at 06:59

score 1 · Answer 2 · edited Aug 28 '13 at 05:21

1

you can directly use this in definition of SP

CREATE PROCEDURE DELETEREP (@id INT,@id1 int,@id2 int) 
AS 
    DELETE FROM TABLE 
    WHERE  ID in (@id,@id1,@id2)

If you are taking these id's from table just declare a #temp table or table variable

Like

CREATE PROCEDURE DELETEREP 

AS 

    Declare @TEMP_TABLE TABLE ( ID INT)

    INSERT INTO @TEMP_TABLE select id from table2

        DELETE FROM TABLE 
        WHERE  ID in (SELECT ID FROM @TEMP_TABLE)

OR YOU CAN Directly use your table2 in SP

Please let me know if this is of any help.

Regards, Ashutosh Arya

edited Aug 28 '13 at 05:21

Devart

119,203
23
166
186

answered Aug 28 '13 at 05:20

Ashutosh Arya

1,138
2
8
14

i cannot modify the store procedure, its has been developed by other programer and its being used in multiple file, so i cannot modify sp, i have to just call this sp, thanks – M Akela Aug 28 '13 at 05:34

score 1 · Answer 3 · answered Aug 28 '13 at 05:21

1

Try this one -

CREATE PROCEDURE dbo.usp_DELETEREP 
(
    @XML XML
) 
AS BEGIN

    DELETE FROM dbo.[TABLE] 
    WHERE ID IN (
        SELECT t.c.value('.', 'BIGINT')
        FROM @XML.nodes('/data/id') t(c)
    )

END

GO

DECLARE @XML XML
SELECT @XML = '
<data>
    <id>1</id>
    <id>2</id>
</data>'

EXEC usp_DELETEREP @XML = @XML

answered Aug 28 '13 at 05:21

Devart

119,203
23
166
186

i cannot modify the store procedure, its has been developed by other programer and its being used in multiple file, so i cannot modify sp, i have to just call this sp, thanks – M Akela Aug 28 '13 at 05:33
Can you create a new stored procedure? – Devart Aug 28 '13 at 05:36
I can create, and I am not stuck with this problem in my work, i want to know just for knowledge , is such kind of scenario can be implemented – M Akela Aug 28 '13 at 05:41
Yes, of course. I personally prefer `XML`, but user-defined table type also are good choice for 2008 (and higher) server version. – Devart Aug 28 '13 at 05:44

How to pass more than one id to stored procedure

3 Answers3