0

I am passing values that are NOT in sequence to a stored procedure in a while loop.
I know how to do it if the number ARE in sequence, but how do I pass values that are in an array, NOT in sequence?

This works fine because my numbers all are in sequences from 4000 to 5000 (4000, 4001, 4002 ....)

DECLARE @FldID INT
SET @FldID = 4000

WHILE @FldID <= 5000
BEGIN
  PRINT @FldID;
  EXEC [dbo].[usp_delFld] @FldID
  SET @FldID = @FldID + 1;
END;

But what if my numbers are not in sequence ie: 4001, 4002, 4010, 4502, 4999, 4030, 4044)?

How do I pass those values to a stored procedure? Can I still use the while loop?

a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
Gregory Gue
  • 1
  • Nothing is being passed into the stored procedure; the constant values 4000 and 5000 are hardcoded in the sp. Therefore you haven't shown how to pass in values to a sp. Please google >>sql pass array into sp<< You might start by looking at https://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure – donPablo Apr 07 '19 at 16:47

2 Answers2

0

if you stored your values in some table you can do that :

DECLARE @FldID INT
SET @FldID = 4000

WHILE @FldID <= 5000
BEGIN
  if exists(select 1 from SomeTable Where FldID=@FldID)
  begin
      PRINT @FldID;
      EXEC [dbo].[usp_delFld] @FldID
      SET @FldID = @FldID + 1
  end
END;

also you can use cursor for do such things

masoud
  • 454
  • 3
  • 11
0
DECLARE @FldID INT DECLARE @COUNT INT,
@SNO INT DECLARE @TBL TABLE (SNO INT IDENTITY(1, 1), FLDID INT)
INSERT INTO
  @TBL (FLDID)
SELECT
  FLDID
FROM
  TABLE
WHERE
  FLDID BETWEEN 4000
  AND 5000
SELECT
  @COUNT = COUNT(*)
FROM
  @TBL 
SET @SNO=1
WHILE (@COUNT > 0) BEGIN
SELECT
  TOP (1) @FldID = FLDID,
  @SNO = SNO
FROM
  @TBL
WHERE
  SNO = @SNO PRINT @FldID;EXEC [dbo].[usp_delFld] @FldID
SET
  @COUNT = @COUNT -1
SET
  @SNO = @SNO + 1 END;
Dr.Stark
  • 51
  • 4