CODEWITHSUNDEEP
sql-server
LCJ
LCJ

Reputation: 22652

Executing SP inside while loop works only first time

In SQL Server 2012, I have a script that call a stored procedure multiple times.. The loop will be executed as many times as the number of records in [ResultHeader] table.

 EXEC [AS400].tp_SelectChildItems @Pgm, @Grp, @Pgmgrpseq, 
                       null, null, null, null, null, @ItemSelection, null,
                       null, null, null, null,

Note: This SP has a table variable inside

I have a comma separated list of SelectedCostPageList. For each value in this list, the loop is executed once.

I am observing a weird behavior. When I invoke the sp from the script, only for the first time the sp is returning proper records.. On subsequent calls the stored procedure is returning no records. Very odd…. When I change the order of comma separated string, only for the first iteration the sp is returing records.. other times no records.

Any guess what will be the reason?

       DECLARE @SelectedCostPageList VARCHAR(MAX)

       SET @SelectedCostPageList = (SELECT (SELECT CONVERT(VARCHAR(32), A.PGM + '-'+A.GRP+'-'+A.PGMGRPSEQ) + ', ' AS [text()]
       FROM dbo.[ResultHeader] A
       WHERE UserId = 'U25703'
       FOR XML PATH('')) AS CostPages) ResultHeader

       PRINT @SelectedCostPageList


       DECLARE @pos INT
       DECLARE @len INT
       set @pos = 0
       set @len = 0

              DELETE FROM ResultItems

       WHILE CHARINDEX(',', @SelectedCostPageList, @pos+1)>0
       BEGIN

              set @len = CHARINDEX(',', @SelectedCostPageList, @pos+1) - @pos
              DECLARE @SelectedCostPage VARCHAR(10)
              set @SelectedCostPage = SUBSTRING(@SelectedCostPageList, @pos, @len)
              set @pos = CHARINDEX(',', @SelectedCostPageList, @pos+@len) +1
              PRINT @SelectedCostPage



              DECLARE @Pgm AS VARCHAR (10);
              DECLARE @Grp AS VARCHAR (10);
              DECLARE @Pgmgrpseq AS VARCHAR (10);



              SELECT @Pgm = (SELECT VALUE
              FROM [AS400].[tf_SelectSplitString] (@SelectedCostPage, '-')
              WHERE POSITION = 1);
              PRINT @Pgm
              SELECT @Grp = (SELECT VALUE
              FROM [AS400].[tf_SelectSplitString] (@SelectedCostPage, '-')
              WHERE POSITION = 2);
              PRINT @Grp
              SELECT @Pgmgrpseq = (SELECT VALUE
              FROM [AS400].[tf_SelectSplitString] (@SelectedCostPage, '-')
              WHERE POSITION = 3);
              PRINT @Pgmgrpseq

              Declare @ItemSelection as varchar(4000)
              set @ItemSelection ='1116102540,1116102541';
              print @ItemSelection

              print 'HAI'
              print @Pgmgrpseq

                           EXEC [AS400].tp_SelectChildItems
                           @Pgm, @Grp, @Pgmgrpseq, 
                           null, null, null, 
                           null, null, @ItemSelection, null,
                           null, null, null, null, 
                           null, null, null, 1, 'U25703'



                           END

                           SELECT * FROM ResultItems
                           WHERE UserId = 'U25703'

Upvotes: 1

Views: 374

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

If I had to guess, it would be because you are using ', ' (comma followed by a space) as a list separator, but the code is just looking for a comma. You would then have a leading space on a string value, which, presumably, would not then match whatever needs to be matched.

The first works, because you initialize the list string with a comma without a space.

Perhaps more importantly, I find the approach of stuffing things into a string to loop over them to be, shall I say, very 1980s. You could use a cursor (not my favorite approach) or a temporary table. When I want to do loops over such data, I sometimes structure them as follows:

declare @looptable table (rownum int identity(1, 1), . . . );
insert into @looptable . . .;
declare @tot int;
select @tot = count(*) from @looptable;
declare @i int = 1;
while (@i <= tot)
begin
    -- something here with "where rownum = @i"
end;

The idea of stuffing values into a string just to write more code to parse them out seems like a waste of programming effort. In addition, this can cause problems because of the conversion of types into character formats and the obscene ways that for path xml handles characters such as '<' and '>'.

Upvotes: 2

Related Questions