Generating Update Queries dynamically in Sql

Introduction

This script is used to generate the update queries dynamically in sql2005/2008.

 Sql Script

CREATE proc usp_UpdateQuery 
( 
 @tablename nvarchar(64) 
) 
as 
begin 
DECLARE @value VARCHAR(64)DECLARE @sql VARCHAR(1024)DECLARE @table VARCHAR(64)DECLARE @column VARCHAR(64) 
 
 
 print 'TableName='+@tablenameSET @value = 'ravi'CREATE TABLE #t ( 
 
        tablename VARCHAR(64), 
 
        columnname VARCHAR(64))create table #tempravi(id int identity(1,1), 
       val nvarchar(max))create table #final([Text] nvarchar(max)) 
     
declare @objid bigint 
 set @objid=(select object_id(@tablename)) 
print ' objectid='+convert(nvarchar,@objid)DECLARE TABLES CURSORFORSELECT o.name, c.nameFROM syscolumns cINNER JOIN sysobjects o ON c.id = o.idWHERE o.type = 'U' --AND c.xtype IN (167, 175, 231, 239,36) and o.name=@tablename and c.colorder > 1ORDER BY o.name, c.nameOPEN TABLESFETCH NEXT FROM TABLESINTO @table, @column 
 
 declare @prmcolname nvarchar(max) 
set @prmcolname=(select [name] from syscolumns where id=@objid and colorder=1) 
print 'primary col name='+@prmcolname  
declare @sqlstrfromtab nvarchar(max) 
set @sqlstrfromtab =' select '+@prmcolname+' from  '+@tablename+' ' 
insert into #tempraviexec(@sqlstrfromtab) 
--print @sqlstrfromtab WHILE @@FETCH_STATUS = 0BEGINdeclare @icount intset @icount=1declare @tablecount intset @tablecount=(SELECT [rows] FROM sysindexes WHERE id = OBJECT_ID(''+ @tablename +'') AND indid < 2)declare @idval nvarchar(max)declare @valueval nvarchar(max)declare @strvalueval nvarchar(max)while(@icount<=(@tablecount))beginset @idval=(select val from #tempravi where id=@icount)set @strvalueval= ('select ['+ @column +'] from  '+ @tablename +' where ['+@prmcolname+'] =  '''+ @idval +''' ')DECLARE @jval nvarchar(max), @sqlval NVARCHAR(max)SET @sqlval = N'select @jval=(select convert(nvarchar(max),['+ @column +']) from  '+ @tablename +' where ['+@prmcolname+'] =  '''+ @idval +''' ) 'EXEC sp_executesql@query = @sqlval,@params = N'@jval nvarchar(max) OUTPUT',@jval = @jval OUTPUTset @sql=' update '+@table + ' set ['+@column +']'+'='+''''+@jval+'''' +' where ['+ @prmcolname+'] = ' + ''''+ @idval +''''insert into #final ([text]) values (@sql)--  EXEC(@sql) set @icount=@icount+1endFETCH NEXT FROM TABLESINTO @table, @columnENDCLOSE TABLESDEALLOCATE TABLES--SELECT * FROM #t --select * from #tempravi select * from #finalDROP TABLE #t 
drop table #tempravi 
 
drop table #final 
 
end

How to use

Example: usp_UpdateQuery 'table_name'

Conclusion

 Any improvements suggested will be considered.and thank you for reading.

Sample Script Source

Download source files -1 kb