2

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
MaazKhan47
  • 811
  • 1
  • 11
  • 22
  • Instead of a `CURSOR`, try executing the second SP (sub SP) within the main SP using the `EXEC` command. You can refer to this question if you want more details (http://stackoverflow.com/questions/15802511/execute-a-stored-procedure-in-another-stored-procedure-in-sql-server) – N00b Pr0grammer Aug 18 '16 at 06:29
  • Hmm, do you use `NVARCHAR` in result of function and insert this into `BIGINT` field? – anatol Aug 18 '16 at 06:49
  • Why do you let yourself reach this point? SQL Server has types *designed* for holding multiple values, such as table-valued parameters. Stuffing everything into a string to then give yourself the task of unpacking it seems like a mystifying choice. – Damien_The_Unbeliever Aug 18 '16 at 06:51
  • @BPavanKumar Why not CURSOR.. Any reason? – MaazKhan47 Aug 18 '16 at 07:35
  • @MaazKhan47 Cursors are called Memory hogs for a reason, though in your case it doesn't matter much. Take a look at this site for a better read on the problems (https://support.microsoft.com/en-us/kb/837957) – N00b Pr0grammer Aug 18 '16 at 08:03
  • I have traced the problem. Actually dbo.splitstring is not working for parameter @Fund_ID. When I pass hardcoded argument '10,20,30' to dbo.splitstring The SP works just fine. Any help? – MaazKhan47 Aug 18 '16 at 08:14

1 Answers1

1

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.

N00b Pr0grammer
  • 4,503
  • 5
  • 32
  • 46
anatol
  • 1,680
  • 2
  • 24
  • 47