Below is the stored procedure of data maintenance sql server

USE [PCCAS]
GO
/****** Object: StoredProcedure [dbo].[sp_DataMaintenance_Insert_Update] Script Date: 05/06/2014 11:14:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_DataMaintenance_Insert_Update]
@AllIds NVARCHAR(MAX),
@UserId int,
@DataDeatils xml
AS
BEGIN
SET NOCOUNT OFF
SET FMTONLY OFF
DECLARE @i INT;
DECLARE @Id INT;
DECLARE @SQL nvarchar(MAX)
DECLARE @COLUMNNAME NVARCHAR(300)
DECLARE @DmnId INT
DECLARE @TableName NVARCHAR(300)
DECLARE @PhoneId int
DECLARE @EmailId int
DECLARE @IsError bit=0

BEGIN TRANSACTION

BEGIN TRY
BEGIN

–Set the All ids to temp table
SELECT CAST(items AS int)DmgrId INTO #tmpIds
FROM dbo.fn_Split(@AllIds,’,’)
WHERE items<>”;

CREATE TABLE #TempDetails
(RN int,[Column] NVARCHAR(300),DmnId int,TableName NVARCHAR(300))

EXEC sp_xml_preparedocument @i OUTPUT, @DataDeatils

INSERT INTO #TempDetails(RN,[Column],DmnId,TableName)
SELECT ROW_NUMBER() OVER(ORDER BY TableName) RN,[Column],DmnId,TableName
FROM OPENXML(@i, ‘/RequestItemStatus/RequestItem’,2)
WITH ([Column] NVARCHAR(300),DmnId int,TableName NVARCHAR(300))
EXEC sp_xml_removedocument @i

SET @i=1

SELECT * FROM #TempDetails

delete from #TempDetails where [Column]=” AND TableName=”

SELECT * FROM #TempDetails

— Creation of tmpDe,ographocs Done
SELECT
[miscDemographics_FirstName]
,[miscDemographics_MiddleInitial]
,[miscDemographics_LastName]
,[miscDemographics_AKA]
,[miscDemographics_DOB]
,[miscDemographics_AgeString]
,[miscDemographics_AgeRange]
,[miscDemographics_LanguageID]
,[miscDemographics_RaceID]
,[miscDemographics_GenderID]
,[miscDemographics_DisabledID]
,[miscDemographics_IsImmigrant]
,[miscDemographics_IsUVisa]
,[miscDemographics_miscAddressId]
,[miscDemographics_miscTelecomId]
,[miscDemographics_AgeStringIncident]
,[miscDemographics_AgeStringInitialContact]
,[miscDemographics_AgeRangeIncident]
,[miscDemographics_AgeRangeInitialContact]
,[miscDemographics_IsSearchable]
,[miscDemographics_MasterId]
,@UserId [miscDemographics_CreatedBy]
,[miscDemographics_CreatedDate]

INTO #tmpDemographics

FROM [dbo].[miscDemographics] WHERE 1=2

–SELECT * FROM #TempDetails

INSERT INTO #tmpDemographics(miscDemographics_IsUVisa,miscDemographics_IsImmigrant,miscDemographics_CreatedBy) VALUES(0,0,@UserId)

–SET SQL update start
SET @SQL =’UPDATE #tmpDemographics SET [miscDemographics_CreatedDate]=GETDATE()’

WHILE EXISTS(SELECT * FROM #TempDetails WHERE RN=@i)
BEGIN

–select The value from tmp table

SELECT @COLUMNNAME=[Column],@DmnId=DmnId,@TableName=TableName FROM #TempDetails WHERE RN=@i

–UPDATE #tmpDemographics sql dynamic Query
IF(@TableName=”)
BEGIN
SET @SQL +=’ ,’+@COLUMNNAME+’=(SELECT b.’+@COLUMNNAME+’ FROM [dbo].[miscDemographics] b WHERE b.miscDemographics_ID=’+CAST(@DmnId as NVARCHAR(50))+’)’;
END
ELSE
BEGIN
IF(@TableName=’miscEmail’)
BEGIN
SELECT @EmailId=@DmnId FROM #TempDetails where @TableName=’miscEmail’
END
ELSE IF(@TableName=’miscPhoneNo’)
BEGIN
SELECT @PhoneId=@DmnId FROM #TempDetails where @TableName=’miscPhoneNo’
END
END
SET @i +=1
END

–SELECT @SQL

EXECUTE sp_executesql @SQL

–SELECT * FROM #tmpDemographics

–INSERT TO demographics
INSERT INTO [dbo].[miscDemographics]([miscDemographics_FirstName]
,[miscDemographics_MiddleInitial]
,[miscDemographics_LastName]
,[miscDemographics_AKA]
,[miscDemographics_DOB]
,[miscDemographics_AgeString]
,[miscDemographics_AgeRange]
,[miscDemographics_LanguageID]
,[miscDemographics_RaceID]
,[miscDemographics_GenderID]
,[miscDemographics_DisabledID]
,[miscDemographics_IsImmigrant]
,[miscDemographics_IsUVisa]
,[miscDemographics_miscAddressId]
,[miscDemographics_miscTelecomId]
,[miscDemographics_AgeStringIncident]
,[miscDemographics_AgeStringInitialContact]
,[miscDemographics_AgeRangeIncident]
,[miscDemographics_AgeRangeInitialContact]
,[miscDemographics_IsSearchable]
,[miscDemographics_MasterId]
,[miscDemographics_CreatedBy]
,[miscDemographics_CreatedDate])

SELECT [miscDemographics_FirstName]
,[miscDemographics_MiddleInitial]
,[miscDemographics_LastName]
,[miscDemographics_AKA]
,[miscDemographics_DOB]
,[miscDemographics_AgeString]
,[miscDemographics_AgeRange]
,[miscDemographics_LanguageID]
,[miscDemographics_RaceID]
,[miscDemographics_GenderID]
,[miscDemographics_DisabledID]
,[miscDemographics_IsImmigrant]
,[miscDemographics_IsUVisa]
,[miscDemographics_miscAddressId]
,[miscDemographics_miscTelecomId]
,[miscDemographics_AgeStringIncident]
,[miscDemographics_AgeStringInitialContact]
,[miscDemographics_AgeRangeIncident]
,[miscDemographics_AgeRangeInitialContact]
,[miscDemographics_IsSearchable]
,[miscDemographics_MasterId]
,[miscDemographics_CreatedBy]
,[miscDemographics_CreatedDate] FROM #tmpDemographics

SET @Id=@@IDENTITY
— Update the phone Record
UPDATE miscPhoneNo
SET miscPhoneNo.miscPhoneNo_miscDemographics_Id=@Id,miscPhoneNo.miscPhoneNo_ModifiedDate=GETDATE()
where miscPhoneNo.miscPhoneNo_miscDemographics_Id=@PhoneId

–delete the record of phone
DELETE miscPhoneNo FROM miscPhoneNo a
INNER JOIN #tmpIds b ON a.miscPhoneNo_miscDemographics_Id=b.DmgrId
WHERE b.DmgrId<>@PhoneId

 
–select @Id
–select a.* from miscPhoneNo a where a.miscPhoneNo_miscDemographics_Id=@Id

–select a.* from miscPhoneNo a
–INNER JOIN #tmpIds b ON a.miscPhoneNo_miscDemographics_Id=b.DmgrId
— Update the Email
UPDATE miscEmail
SET miscEmail.miscEmail_miscDemographics_Id=@Id,miscEmail.miscEmail_ModifiedDate=GETDATE()
where miscEmail.miscEmail_miscDemographics_Id=@EmailId
–Delete the Email
DELETE miscEmail FROM miscEmail a
INNER JOIN #tmpIds b ON a.miscEmail_miscDemographics_Id=b.DmgrId
WHERE b.DmgrId<>@EmailId
–select a.* from miscEmail a where a.miscEmail_miscDemographics_Id=@Id

–select a.* from miscEmail a
–INNER JOIN #tmpIds b ON a.miscEmail_miscDemographics_Id=b.DmgrId

— SELECT reference table
SELECT ROW_NUMBER() OVER(ORDER BY f.name) RN,
f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
INTO #tmpRefrTbl
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
where COL_NAME(fc.referenced_object_id,
fc.referenced_column_id)=’miscDemographics_ID’
AND OBJECT_NAME(f.parent_object_id)<>OBJECT_NAME (f.referenced_object_id)

–SELECT * FROM #tmpRefrTbl
SET @i =1
WHILE EXISTS(SELECT * FROM #tmpRefrTbl WHERE RN=@i)
BEGIN

–select The value from tmp table

SELECT @COLUMNNAME=ColumnName,@TableName=TableName FROM #tmpRefrTbl WHERE RN=@i

–SELECT @COLUMNNAME,@TableName
–UPDATE #tmpDemographics sql dynamic Query
SET @SQL +=’ UPDATE ‘+@TableName+’ SET ‘+@COLUMNNAME+’=’+CAST(@Id as NVARCHAR(50))+’ WHERE ‘+@COLUMNNAME+’ IN(select DmgrId FROM #tmpIds)’

EXECUTE sp_executesql @SQL

SET @i +=1
END
— Delete the record of Demographics of old ids

DELETE FROM miscDemographics where miscDemographics_ID IN (select DmgrId FROM #tmpIds)

COMMIT TRAN
SELECT @IsError AS IsError
–ROLLBACK TRAN;
END
END TRY

BEGIN CATCH
BEGIN

SET @IsError=1
SELECT @IsError AS IsError
–SELECT
— ERROR_NUMBER() AS ErrorNumber
— ,ERROR_MESSAGE() AS ErrorMessage;

ROLLBACK TRAN;
END
END CATCH

 
END