Click here to Skip to main content
15,901,122 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
                           INSERT INTO QAdmin.PartyLicenseInfo (PartyId, [State], LicenseNumber, LicenseExpirationDate, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate) 
                           VALUES (@partyID, 'SC', 'SC1234', getdate()+360, 0, GETDATE(), null, null)
                     END
ELSE
                     BEGIN
                           UPDATE [QAdmin].[PartyLicenseInfo] SET LicenseExpirationDate = getdate()+360, ModifiedBy = 0, ModifiedDate = GETDATE() 
                           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

 
Share this answer
 
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)
--your code
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
INSERT INTO QAdmin.PartyLicenseInfo (PartyId, [State], LicenseNumber, LicenseExpirationDate, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@partyID, 'SC', 'SC1234', getdate()+360, 0, GETDATE(), null, null)
END
ELSE
BEGIN
UPDATE [QAdmin].[PartyLicenseInfo] SET LicenseExpirationDate = getdate()+360, ModifiedBy = 0, ModifiedDate = GETDATE()
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
 
Share this answer
 
v2
Comments
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.
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
        SET     LicenseExpirationDate = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())+360,0),
                ModifiedBy = 0,
                ModifiedDate = GETDATE()
        FROM    QAdmin.PartyLicenseInfo PL
        WHERE   PL.PartyID = @PartyID AND
                PL.State = 'SC'

        -- ============================================================================
        -- Insert Non-Existing for State
        -- ============================================================================
        INSERT  QAdmin.PartyLicenseInfo (PartyId, [State], LicenseNumber, LicenseExpirationDate, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
        SELECT  @PartyId,
                State='SC',
                LicenseNumber = 'SC1234',
                LicenseExpirationDate = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())+360,0),
                CreatedBy = 0,
                CreatedDate = GETDATE(),
                ModifiedBy = NULL,
                ModifiedDate = NULL
        WHERE   NOT EXISTS (SELECT  1
                            FROM    QAdmin.PartyLicenseInfo DST WITH (NOLOCK)
                            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!!
--  ==================================================
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900