Execute Stored Procedure for List of Parameters in SQL

sql sql-server sql-server-2008 tsql sql-scripts
Saeid picture Saeid · Apr 23, 2012 · Viewed 12.2k times · Source

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?

Answer

Dan picture Dan · Apr 23, 2012

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.

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More
SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine: CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description nvarchar(100), @Type int, @Username nvarchar(100), AS BEGIN DECLARE @TopRelatedItemId uniqueidentifier; SET @TopRelatedItemId = ( SELECT top(1) RelatedItemId FROM …

Read More