Create Update Procedure Automatically in MS SQL
Here I am going to explain you how to create Update Procedure Automatically without spending much time. You just need to pass the required parameter to this procedure, rest of the things will be taken care by this simple procedure.
Below is a simple MS SQL stored procedure which helps you to create Update stored procedure automatically. This will save your time and effort of stored procedure creation.
CREATE Proc sp_UpdateProc
(
@TableName varchar(50),
@ColName1 varchar(100)
)
as
begin
declare @Count int,@i int
declare @Str varchar(max),@sql varchar(max),@sql1 varchar(max)
declare @ColName varchar(max),@ConCat varchar(max)
declare @ColNameTab varchar(max),@ConCatTab varchar(max),@chTableName varchar(max)
set @Count=(select count(ordinal_position) from information_schema.columns where table_name=''+@TableName+'')
set @i=1
set @sql=''
set @ConCat=''
set @ConCatTab=''
set @chTableName='sp_'+@TableName+'_Insert'
begin
while (@i<=@Count)
begin
set @Str=(select '@'+convert(varchar(max),Column_Name) +' '+
case when data_type='nvarchar' then 'nvarchar('+convert(varchar(10),character_maximum_length)+') ,'
when data_type='varchar' then 'varchar('+convert(varchar(10),character_maximum_length)+') ,'
when data_type='char' then 'char('+convert(varchar(10),character_maximum_length)+') ,'
when data_type='varchar2' then 'varchar2('+convert(varchar(10),character_maximum_length)+') ,'
when data_type='numeric' then 'numeric('+convert(varchar(10),numeric_precision)+','+convert(varchar(10),numeric_scale)+') ,'
when data_type='decimal' then 'decimal('+convert(varchar(10),numeric_precision)+','+convert(varchar(10),numeric_scale)+') ,'
when data_type='float' then convert(varchar(10),'float ,')
when data_type='int' then convert(varchar(10),'int ,')
when data_type='bit' then convert(varchar(10),'bit ,')
when data_type='image' then convert(varchar(10),'image ,')
when data_type='datetime' then convert(varchar(10),'datetime ,')
End as ColumnName
from information_schema.columns where table_name=''+@tableName+'' and ordinal_position=@i)
set @sql=@sql+@Str
set @ColName=(select Column_Name +',' from information_schema.columns where table_name=''+@tableName+'' and ordinal_position=@i)
set @ConCat=@ConCat+@ColName
set @ColNameTab=(select Column_Name+'=@'+Column_Name +',' from information_schema.columns where table_name=''+@tableName+'' and ordinal_position=@i)
set @ConCatTab=@ConCatTab+@ColNameTab
set @i=@i+1
end
set @sql1='Create proc sp_'+@tableName+'_Update('+left(@sql,len(@sql)-1)+')
as
begin
Update '+@TableName+' set '+left(@ConCatTab,len(@ConCatTab)-1)+' where '+ @ColName1+'=@'+@ColName1+'
End'
exec(@sql1)
End
End
after that execute like this
exec sp_updateProc 'tableName','ColumnName Towhich you want in where condition'