15,901,122 members
See more:
in the below code i used single @partyID=750004 but now the scenario needs to use more @partyID... FYI like 750005,750007,750009,7500011,7500021,75000251.

SQL
```BEGIN TRY
BEGIN TRAN
Declare @partyID bigint = 750004
IF NOT EXISTS  (SELECT * FROM [QAdmin].[PartyLicenseInfo] WHERE PartyId = @partyID AND [State]='SC')
BEGIN
VALUES (@partyID, 'SC', 'SC1234', getdate()+360, 0, GETDATE(), null, null)
END
ELSE
BEGIN
WHERE PartyId = @partyID AND [State]='SC'
END

COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH```
Posted
Updated 9-Jun-15 20:03pm
v2

## Solution 2

There is nothing like `foreach` in SQL. If you want to iterate your list of numbers you can use a `WHILE` loop.

First, you need to iterate your csv
SQL
`DECLARE @partyIDs VARCHAR(MAX)= '750005,750007,750009,7500011,7500021,75000251'`

check the link below to see how
http://data.stackexchange.com/stackoverflow/query/66016/walk-the-string[^]

Then, you need to fit your entire code inside the loop (given in above link) like:
SQL
```declare @S varchar(20)
set @S = @partyIDs

while len(@S) > 0
begin
--declare @partyID VARCHAR(20)= left(@S, charindex(',', @S+',')-1)
BEGIN TRY
BEGIN TRAN
--Declare @partyID bigint = 750004
Declare @partyID bigint= CAST(left(@S, charindex(',', @S+',')-1) AS bigint)
IF NOT EXISTS (SELECT * FROM [QAdmin].[PartyLicenseInfo] WHERE PartyId = @partyID AND [State]='SC')
BEGIN
VALUES (@partyID, 'SC', 'SC1234', getdate()+360, 0, GETDATE(), null, null)
END
ELSE
BEGIN
WHERE PartyId = @partyID AND [State]='SC'
END

COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH

set @S = stuff(@S, 1, charindex(',', @S+','), '')
end```

v2
GopalaKrishnan k 10-Jun-15 2:54am
hi, Abhipal .. what about this line in my code "Declare @partyID bigint = 750004" do i need to make any changes here?
Abhipal Singh 10-Jun-15 4:00am
It depends on the column type in your table. If it is bigint then you can type cast your varchar. Check updated solution above to see how.

## Solution 3

You can go the function way as well. Not sure what version you are on or how scalable the task needs to be.... I'd go with the solution entered by Abhipal.

```--  ==================================================
--  CREATE FUNCTION dba_fn_SplitList
--  ==================================================
CREATE FUNCTION [dbo].[dba_fn_SplitList] (@param NVARCHAR(MAX), @delimiter CHAR(1))
RETURNS @t TABLE (val VARCHAR(150), seq INT)
AS
/********************************************************************************\
* Description:      Split a delimited list into a table with each value and the sequence.
*                   Seq is unique so blanks and duplicates can be removed outside the
*                   function.
\********************************************************************************/
BEGIN
SET @param += @delimiter

;WITH A AS
(
SELECT CAST(1 AS BIGINT) F, CHARINDEX(@delimiter, @param) T, 1 Seq
UNION ALL
SELECT T + 1, CHARINDEX(@delimiter, @param, t + 1), Seq + 1
FROM A
WHERE CHARINDEX(@delimiter, @param, t + 1) > 0
)
INSERT @t
SELECT  SUBSTRING(@param, f, t - f), seq
FROM    A
OPTION (MAXRECURSION 0)
RETURN
END
GO

-- ============================================================================
-- Get the PartyIdList
-- ============================================================================
DECLARE @PartyIDList VARCHAR(MAX)
SET     @PartyIDList = '75000A5,750007,1,3434,4546,676,750009,7500011,7500021,75000251,,75000251,750004'

BEGIN TRY
IF OBJECT_ID('tempdb.dbo.#Party') IS NOT NULL DROP TABLE #Party

CREATE TABLE #Party (RID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, PartyId BIGINT NOT NULL)

DECLARE @PartyID BIGINT = 0
DECLARE @min INT, @max INT

-- ============================================================================
-- Get each valid PartyID
-- ============================================================================
INSERT  #Party (PartyId)
SELECT  DISTINCT Val
FROM    dbo.dba_fn_SplitList(@PartyIDList,',')
WHERE   Val <> '' AND
Val NOT LIKE '%[^0-9]%'

SELECT  @min = MIN(RID), @max = MAX(RID) FROM #Party

WHILE (@min <= @max)
BEGIN
SELECT  TOP 1 @partyID = PartyID
FROM    #Party P
WHERE   P.RID = @min

-- ============================================================================
-- Update Existing PartyIds for State
-- ============================================================================
UPDATE  PL
ModifiedBy = 0,
ModifiedDate = GETDATE()
WHERE   PL.PartyID = @PartyID AND
PL.State = 'SC'

-- ============================================================================
-- Insert Non-Existing for State
-- ============================================================================
SELECT  @PartyId,
State='SC',
CreatedBy = 0,
CreatedDate = GETDATE(),
ModifiedBy = NULL,
ModifiedDate = NULL
WHERE   NOT EXISTS (SELECT  1
WHERE   DST.PartyID = @PartyId AND
DST.State = 'SC')
SET @min = @min + 1
END

END TRY
BEGIN CATCH
SELECT  ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage
END CATCH
GO

--  ==================================================
--  END!!
--  ==================================================```