Execute Stored Procedure for List of Parameters in SQL

Question

I have an Stored Procedure that have an argument named Id:

CREATE PROCEDURE [TargetSp](
   @Id  [bigint]
)
AS 
BEGIN
   Update [ATable]
   SET [AColumn] = 
   (
     Select [ACalculatedValue] From [AnotherTable]
   )
   Where [ATable].[Member_Id] = @Id
END

So I need to use it for a list of Id's not for one Id like :

Exec [TargetSp]
  @Id IN (Select [M].[Id] From [Member] AS [M] Where [M].[Title] = 'Example');

First: How can I Execute it for a list?

Second: Is there any Performance difference between I execute the sp many times or rewrite it in target script?

http://stackoverflow.com/a/6384/1278315 check this. You can create a function which returns the IDs and pass it continously to the stored procedure.And do ignore the remark about Sybase but the logic is similar. — Milee, Apr 23 '12 at 12:06
I still think there has to be a very simple solution to your problem. What is the source of the @Id parameter? Is it stored in a table in your database (in that case you should be able to use a join in your stored procedure) or is it generated from your application? — Dan, Apr 24 '12 at 06:40

score 3 · Answer 1 · answered Apr 23 '12 at 12:05

3

You could use a table-valued parameter (see http://msdn.microsoft.com/en-us/library/bb510489.aspx). Generally, if you send only one request to the server instead of a list of requests you will see a shorter execution time.

answered Apr 23 '12 at 12:05

Dan

1,927
2
24
35

Didn't know about this one... unfortunately a good percentage of our clients are still on 2005, so not something we can implement. Worth remembering though – freefaller Apr 23 '12 at 12:29
@Dan I read your mentioned link, and update main question, I think with my situation, I can not Use table-valued. – Saeid Apr 23 '12 at 13:05

score 1 · Answer 2 · answered Apr 23 '12 at 12:12

I normally pass in the information like that as XML, then you can use it just like it's a table... selecting, inserting, updating as necessary

DECLARE @IDS NVARCHAR(MAX), @IDOC INT
SET @IDS = N'<ROOT><ID>1</ID><ID>2<ID></ROOT>'
EXEC sp_xml_preparedocument @IDOC OUTPUT, @IDS
SELECT [ID] FROM OPENXML (@IDOC, '/ROOT/ID', 2) WITH ([ID] INT '.') AS XMLDOC
EXEC sp_xml_removedocument @IDOC

Chris Moutray · Answer 3 · 2012-04-23T13:20:08.173

Similar to freefaller's example, but using xml type instead and inserting into a table variable @ParsedIds

DECLARE @IdXml XML = N'<root><id value="1"/><id value="2"/></root>'

DECLARE @ParsedIds TABLE (parsedId int not null)

INSERT INTO @ParsedIds (parsedId)
SELECT v.parsedId.value('@value', 'int')
FROM @IdXml.nodes('/root/id') as v(parsedId)

SELECT * FROM @ParsedIds

Interestingly I've worked on an large scale system with 1000's of users and we found that using this method out performed the table-valued parameter approach for small lists of id's (no more than say 5 id's). The table-valued parameter approach was faster for larger lists of Id's.

EDIT following edited question:

Looking at your example it looks like you want to update ATable based on the Title parameter. If you can you'd benefit from rewriting your stored procedure to instead except the title parameter.

create procedure [TargetSP](
   @title varchar(50)
)
as 
begin

update [ATable]
set [AColumn] = 
(
    select [ACalculatedValue] from [AnotherTable]
)
where [ATable].[Member_Id] in (select [M].[Id] from [Member] as [M] where [M].[Title] = @title);

end

I update main question, I think with my situation, I can not Use table-valued. — Saeid, Apr 23 '12 at 13:06

score 1 · Answer 4 · answered Apr 23 '12 at 13:39

Since you only care about all the rows with a title of 'Example', you shouldn't need to determine the list first and then tell SQL Server the list you want to update, since you can already identify those with a query. So why not do this instead (I'm guessing at some data types here):

ALTER PROCEDURE dbo.TargetSP
  @title VARCHAR(255)
AS
BEGIN
  SET NOCOUNT ON;

  -- only do this once instead of as a subquery:
  DECLARE @v VARCHAR(255) = (SELECT [ACalculatedValue] From [AnotherTable]);

  UPDATE a
    SET AColumn = @v
    FROM dbo.ATable AS a
    INNER JOIN dbo.Member AS m
    ON a.Member_Id = m.Id
    WHERE m.Title = @title;
END
GO

Now call it as:

EXEC dbo.TargetSP @title = 'Example';

Saeid · Answer 5 · 2012-04-23T13:57:14.703

1

 DECLARE @VId BIGINT;

 DECLARE [My_Cursor] CURSOR FAST_FORWARD READ_ONLY FOR
 Select [M].[Id] From [Member] AS [M] Where [M].[Title] = 'Example'
 OPEN [My_Cursor]
    FETCH NEXT FROM [My_Cursor] INTO @VId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC [TargetSp]
        @Id = @VId
    FETCH NEXT FROM [My_Cursor] INTO @VId
END
CLOSE [My_Cursor]
DEALLOCATE [My_Cursor];
GO

edited Apr 23 '12 at 13:57

answered Apr 23 '12 at 13:48

Saeid

13,224
32
107
173

score 0 · Answer 6 · answered Apr 23 '12 at 12:26

0

if the parameter is integer, you can only pass one value at a time.

Your options are:

call the proc several times, one for each parameter
Change the proc to accept a structure where you can pass more than one id like a varchar where you pass a coma separated list of values (not so good) or a table-value parameter

About the performance question, it would be faster to re-write the proc to iterate through a list of ids than call it several times, once per id, BUT unless you are dealing with a HUGE list of ids, I dont think you will see much of a difference

answered Apr 23 '12 at 12:26

Diego

34,802
21
91
134

I need to Execute it for this Id's `Select [M].[Id] From [Member] AS [M] Where [M].[Title] = 'Example'` What is your suggestion to call several times? – Saeid Apr 23 '12 at 12:28
1

row many records does it return? If it returns, 2 I would call the proc twice, but if it returns more, it may be a good Idea to re-write it, but not because of performance improvements, because it is a "cleaner" solution – Diego Apr 23 '12 at 12:31

Execute Stored Procedure for List of Parameters in SQL

6 Answers6