CODEWITHSUNDEEP
sql.netsql-serverstored-procedurescursor
MaazKhan47
MaazKhan47

Reputation: 849

Run stored procedure for each value in a comma separated string

I want to run my stored procedure for every value in comma separated string. Lets say I have ('10,20,30') so the sp should run for 10 first then for 20 and 30 and return result in a single table. The Sp will return single row for each value. I have tried it using Cursors but not working as it is supposed to be. 

SET ANSI_NULLS OFF
GO   

SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[z_Formulas2]
(
    @Fund_ID nvarchar,      --('32,25,201')
    @XFund_ID bigint, 
    @Start_Dated datetime,
    @End_Dated datetime
)   
AS
    DECLARE @FUNDS TABLE(FundId BIGINT)
    INSERT INTO @FUNDS
    SELECT item FROM dbo.SplitString(@Fund_ID, ',') --Split string parse csv into table
    SELECT * FROM @FUNDS
    DECLARE @MyCursor CURSOR;
    DECLARE @CurFund BIGINT;

BEGIN
SET @MyCursor = CURSOR FOR SELECT FundId FROM @FUNDS   
OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @CurFund

WHILE EXISTS (SELECT FundId FROM @FUNDS)
BEGIN... --Logic part
 FETCH NEXT FROM @MyCursor 
 INTO @CurFund 
END

CLOSE @MyCursor ;
DEALLOCATE @MyCursor;
END

//dbo.SplitString

ALTER FUNCTION [dbo].[SplitString]
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END

Upvotes: 2

Views: 1532

Answers (1)

anatol
anatol

Reputation: 1762

It looks like not correctly working with cursor.

DECLARE @MyCursor CURSOR;
DECLARE @CurFund BIGINT;
BEGIN
    SET @MyCursor = CURSOR FOR SELECT FundId FROM @FUNDS   
    OPEN @MyCursor 
        FETCH NEXT FROM @MyCursor 
        INTO @CurFund

        WHILE EXISTS (SELECT FundId FROM @FUNDS)
        BEGIN
             --Logic part
        END
    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END

In this snippet was skipped another one FETCH NEXT operator in the WHILE block. Also will be correct to use WHILE @@FETCH_STATUS = 0 instead your WHILE EXISTS (SELECT FundId FROM @FUNDS) because it will be always return true.

Upvotes: 1

Related Questions