Create Temp table, and alter the temp table to add new columns and update values.
Create Table #Temp
(
ItemID INT IDENTITY(1,1),
Name varchar(50),
Value varchar(50)
)
DECLARE @vEntityID int = @pPropertyId
IF EXISTS(SELECT * FROM [dbo].[Property] WHERE PropertyID = @vEntityID)
BEGIN
INSERT INTO #Temp (Name,Value)
Select A.[GlobalCodeName],C.[GlobalCodeValue] from [dbo].[GlobalCodes] A
inner join [dbo].[GlobalCodeCategory] b
on A.[GlobalCodeCategoryID]=b.[GCCategoryID]
Inner join [dbo].[EntityGlobalCodesValues] c
on c.[GlobalCodeID]=A.[GlobalCodesID]
Where C.[EntityName]='PRO' and A.[IsActive]=1
and C.[EntityID]=@vEntityID
order by [SortOrder]
CREATE TABLE #PropTemp(PropertyId int,[PropertyName] varchar(100),[PropertyDescription]
varchar(max),[Address1] varchar(max),[Address2] varchar(max),[MainImageURL] varchar(100), [PublishedOn] varchar(50),[UpdatedOn] varchar(50),[UpdatedTime] varchar(20),[Latitude] varchar(20), [Longitude] varchar(20))
INSERT INTO #PropTemp (PropertyId,[PropertyName],[PropertyDescription],[Address1],[Address2],[MainImageURL],[PublishedOn],[UpdatedOn],[UpdatedTime],[Latitude], [Longitude])
SELECT @vEntityID PropertyId, [PropertyName],[PropertyDescription],[Address1],[Address2],[MainImageURL],CONVERT(VARCHAR, GETDATE(), 107) [PublishedOn],CONVERT(VARCHAR, GETDATE(), 107) [UpdatedOn],convert(varchar, getdate(), 108) [UpdatedTime], [Latitude], [Longitude] from [dbo].[Property] where [PropertyID]
= @vEntityID
DECLARE @vSql varchar(150);
DECLARE @vSqlUpdate varchar(150);
DECLARE @vAttributeLable varchar(150);
DECLARE @vAttributeValue varchar(150);
DECLARE @loopLength int = (select count(*) from #Temp)
DECLARE @index int = 1;
WHILE(@index <= @loopLength)
BEGIN
SET @vSql = '';
SET @vSqlUpdate = '';
SET @vAttributeLable = (SELECT Name FROM #Temp WHERE ItemID = @index)
SET @vAttributeValue = (SELECT Value FROM #Temp WHERE ItemID = @index)
SET @vSql = 'alter table #PropTemp add
'+ @vAttributeLable
+' varchar(150)'
EXECUTE ( @vSql)
SET @vSqlUpdate = 'update #PropTemp set '
+ @vAttributeLable+' = '''+@vAttributeValue+''' where PropertyId = '+Convert(varchar(50),@vEntityID)
EXEC ( @vSqlUpdate)
SET @index = @index + 1
END
SELECT * FROM #PropTemp
DROP TABLE #PropTemp
DROP TABLE #Temp
END
ELSE
BEGIN
SELECT 'No record found'
AS PropertyDescription
END
