Simple Merge Stored Procedure with Function

Merge and Where in with String Values

Create proc [dbo].[LT_MergeRoleDetails]

(

@ID int,

@ValueId int,

@SelectedPermission varchar(max)

)

as

BEGIN

MERGE RoleDetail AS TARGET Using

(SELECT @ID,@ValueId) as NEW (ID,ValueId) ON

(

TARGET.ID=NEW.ID AND

TARGET.ValueId=NEW.ValueId

)

WHEN MATCHED THEN

UPDATE SET

TARGET.ID=NEW.ID,             

TARGET.ValueId=NEW.ValueId,

TARGET.UpdatedOn=GETDATE(),

TARGET.Obsolete=0

WHEN

NOT MATCHED BY TARGET THEN

INSERT (ID,ValueId,CreatedOn,UpdatedOn,Obsolete)

VALUES (NEW.ID,NEW.ValueId,GETDATE(),GETDATE(),0);

-- Update

Update RoleDetail set Obsolete=1 where cast(RoleDetId as varchar) not in (SELECT * FROM UF_CSVToTable(@SelectedPermission)) and ValueId=@ValueId

END

Function

Create FUNCTION [dbo].[UF_CSVToTable]

(

 @psCSString VARCHAR(8000)

)

RETURNS @otTemp TABLE(sID VARCHAR(20))

AS

BEGIN

 DECLARE @sTemp VARCHAR(10)

 WHILE LEN(@psCSString) > 0

 BEGIN

  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),

                    LEN(@psCSString)))

  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),

                               LEN(@psCSString)) + 1, LEN(@psCSString))

  INSERT INTO @otTemp VALUES (@sTemp)

 END

RETURN

END