How to create a stored procedure to generate update and insert sql statements?
Do you require inserting and updating SQL tables every now and then in your program? This article would help you learn how to create an SQL procedure to generate update and insert sql statements. Get the script for creating insert-update stored procedure.
When you need to insert and update values in SQL tables multiple no. of times, it becomes very time consuming to write the code again and again. I prefer to create a simple SQL stored procedure for such insert and update operations on a table. Stored procedures help to save your precious programming time to a great extent.
Below is the script to create an SQL stored procedure to perform insert and update operations on your SQL tables.
After you create this procedure, all you need to do is just insert a call to this script to execute the Insert-Update Procedure, at the required places in your program.
create Proc sp_InsertUpdateProc
(
@TableName varchar(100),
@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)
declare @ColNameTab1 nvarchar(max),@ColName2 nvarchar(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 @ColName2=''
set @chTableName='sp_'+@TableName+'_Insert'
if exists(select * from sysobjects where xtype='p' and Name=''+@chTableName+'')
begin
print 'Insert procedure already exists'
End
else
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='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 +',' from information_schema.columns where table_name=''+@tableName+'' and ordinal_position=@i)
set @ConCatTab=@ConCatTab+@ColNameTab
set @ColNameTab1=(select Column_Name+'=@'+Column_Name +',' from information_schema.columns where table_name=''+@tableName+'' and ordinal_position=@i)
print @ColNameTab1
set @ColName2=@ColName2+@ColNameTab1
set @i=@i+1
end
print '--------------------'
print @ColName2
set @sql1='Create proc sp_'+@tableName+'_InsertUpdate('+left(@sql,len(@sql)-1)+',@Type bit)
as
Begin
if(@Type=1)
begin
insert into '+@TableName+'('+left(@ConCat,len(@ConCat)-1)+') values('+left(@ConCatTab,len(@ConCatTab)-1)+')
End
else
begin
Update '+@TableName+' set '+left(@ColName2,len(@ColName2)-1)+' where '+ @ColName1+'=@'+@ColName1+'
End
End'
print(@sql1)
PRINT 'Procedure has been created (sp_'+@tableName+'_InsertUpdate)'
End
End
Now that you have created the Automatic InsertUpdate Procedure execute it as:
exec sp_insertproc 'TableName'