This zip file contain on stored procedure .SQL
Introduction
This article how built stored procedure to create dynamic stored procedure contains insert command for any tables in our database.
Create Stored Procedure
Create stored procedure to auto generate another stored procedure contain insert command for any tables.
CREATE PROCEDURE Create_procedure_To_insert
parameters
You must add two parameters:
1- Table Name: to generate insert command on it.
2- Developer Name: to set developer name in comments.
CREATE PROCEDURE create_procedure
@table varchar(200),
@DeveloperName varchar(200),
@Createtable varchar(20)
Declaration Variables
You need to declaration many variables to use in stored procedure see below code to know it.
declare @testTable varchar(8000)
declare @testTable2 varchar(8000)
declare @testTable3 varchar(8000)
declare @opration varchar(8000)
declare @final varchar(8000)
declare @OP varchar(100)
1- @testTable: used this variable to set all columns from table.
2- @testTable2:used this variable to set all datatype for columns from table.
3- @testTable3:used this variable to set all parameters for columns from table.
4- @opration :used this variable to set insert command.
5-@final :used this variable to set auto generate stored procedure.
6-@OP :used this variable to set name for the new stored procedure.
Initialization Variables
You need to set empty values in the below variables.
set @testTable=''
set @testTable2=''
set @final=''
set @testTable3=''
set @opration=''
declare @Datetime varchar(50)
set @Datetime=getdate()
Importents Code
You need to create three select statment :
1- The First Select statment to get all columns from information_schema.columns when table equal table name in parameters ,you must get 'isidentity'= zero becuase if 'isidentity'=one you can not insert data on identity columns
2-The second Select statment to get all datatype from information_schema.columns when table equal table name in parameters .you must get 'isidentity'= zero becuase if 'isidentity'=one you can not insert data on identity columns.
3-The Third Select statment to get columns name and set @columns name in parameters
after that you must generate the new stored procedure ,so you can create structure for procedure in string datatype and set the string in execute function to execution stored procedure "Exec(@final)"
select @testTable=@testTable+ ',
'+column_name from information_schema.columns where table_name=@table and (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0) AND (column_default IS NULL)
select @testTable2=@testTable2+ ',
@'+column_name+' '+data_type+'(' + cast(character_maximum_length as varchar(10)) +')' + case is_nullable when 'no' then ' ' when 'yes' then '=null' end from information_schema.columns where table_name=@table and (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)and character_maximum_length<>null AND (column_default IS NULL)and data_type<>'text'
select @testTable2=@testTable2+ ',
@'+column_name+' '+data_type from information_schema.columns where table_name=@table and (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)and (character_maximum_length=null or data_type='text' ) AND (column_default IS NULL)
select @testTable3=@testTable3+ ',
@'+column_name from information_schema.columns where table_name=@table and (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0) AND (column_default IS NULL)
set @testTable=SUBSTRING(@testTable,2,len(@testTable))
set @testTable2=SUBSTRING(@testTable2,4,len(@testTable2))
set @testTable3=SUBSTRING(@testTable3,2,len(@testTable3))
set @opration=' insert into [' +@table+']
(
'+@testTable+'
)
values
(
'+ @testTable3 +'
)'
set @OP='InsertNew'+@table
set @final='/*
----------------------------------------------------------------------------------------
Store Procedure Name : SP__'+@OP +'
----------------------------------------------------------------------------------------
1- Creation Date :'+convert (varchar,getdate(),103) +'
2- Last Update :'+convert (varchar,getdate(),103)+'
3- Parametars No:6
4- Creation By :'+@DeveloperName+'
5- Last Update By :'+@DeveloperName+'
6- Return Value : Dataset
---------------------------------------------------------------------------------------
*/
Create PROCEDURE SP__'+@OP+'
(
'+ @testTable2 + '
)
AS
set nocount on
' + @opration + '
Select * from [' +@table +']'
exec (@final)
Summary
After creation this stored procedure you can creating stored procedure for insert command at one second just you have to set table name parameter and your name .
