CODEWITHSUNDEEP
sql-serverstored-procedures
muttley91
muttley91

Reputation: 12674

Run A Loop in SQL Server

I want to run a stored procedure on each ID return by a SELECT query. Is there a simple way to do something like:

FOREACH (SELECT ID FROM myTABLE WHERE myName='bob') AS id
BEGIN
    EXEC @return_value = [dbo].[spMYPROC]
         @PARAM1 = id
         @PARAM2 = 0
END

Upvotes: 0

Views: 8618

Answers (3)

agileMike
agileMike

Reputation: 463

Since I just happened to answer a very similar question yesterday, I have this code handy. As others have stated, it may not be the best approach, but still it's nice to learn how to use a while loop anyway.

Assuming a table named "Customer"

declare @Id int

select @Id = MIN(Id)
from Customer c


while(select COUNT(1)
      from Customer c
      where c.Id >= @Id) > 0
    begin

        --run your sproc right here

        select @Id = MIN(Id)
        from Customer c
        where c.Id > @Id


    end

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

You have two option here

Option 1 Using Split Function

Pass a comma deliminated list of IDs and use a Split function Inside your Procedure to make split these values and do whatever you want to do with it.

To Make it work you will need two thing

1) Create a Function which accepts a Comma Deliminated string and split them.

2) Modify you Store Procedure and add this function in there in a way that passed parameter is passed to the function inside that store procedure and that function split the values before passing it onto your store Procedure .

Create this function 1st

Function Definition

CREATE FUNCTION [dbo].[FnSplit]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table (Id int identity(1,1), Value nvarchar(100))
AS
BEGIN

WHILE(Charindex(@SplitOn,@List)>0)

  BEGIN


        INSERT INTO @RtnValue (value)

        SELECT VALUE = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

        SET @List = SUBSTRING(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

  END


INSERT INTO @RtnValue (Value)

SELECT VALUE = ltrim(rtrim(@List))

RETURN

END

Modify you strored Procedure something like this

Stored Procedure

ALTER Procedure  [dbo].[spMYPROC] (@Param1 VARCHAR(1000)= NULL)
AS
BEGIN

        SELECT * FROM TableName
        where ColumnNAME  IN (SELECT Value FROM dbo.FnSplit(@Param1,','))

END
GO

Option 2 Table Type Parameter

Create a Table Type and alter your proc to accept a Table Type Parameter and do whatever you want to do with them values inside your proc.

TABLE TYPE

CREATE TYPE dbo.TYPENAME AS TABLE 
 (
    Value int 
  )
 GO

Stored Procedure to Accept That Type Param 

 ALTER PROCEDURE [dbo].[spMYPROC]  
 @TableParam TYPENAME READONLY
 AS 
   BEGIN
    SET NOCOUNT ON;
  --Temp table to store passed Id values
  declare @tmp_values table (value INT );   

   --Insert passed values to a table variable inside the proc

   INSERT INTO @tmp_values (value)
   SELECT Value FROM @TableParam

    /* Do your stuff here whatever you want to do with Ids */

END

EXECUTE PROC

Declare a variable of that type and populate it with your values.

 DECLARE @Table TYPENAME     --<-- Variable of this TYPE

 INSERT INTO @Table                --<-- Populating the variable 
 SELECT ID FROM myTABLE WHERE myName='bob'

EXECUTE [dbo].[spMYPROC]  @Table   --<-- Stored Procedure Executed

Upvotes: 0

jdl
jdl

Reputation: 1104

DECLARE @ID INT, @return_value INT

DECLARE c CURSOR FOR
SELECT
    ID
FROM myTABLE
WHERE myName = 'bob'

OPEN c; FETCH NEXT FROM c INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
     EXEC @return_value = [dbo].[spMYPROC]
         @PARAM1 = @ID,
         @PARAM2 = 0

    FETCH NEXT FROM c INTO @ID
END

CLOSE c; DEALLOCATE c;

Upvotes: 0

Related Questions