Generating Stored Procedures dynamically

Share |

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)