Dynamically creating stored procedure based on table

[daveonion]

Hi,
I have created a stored procedure whose purpose it is to create a stored procedure based upon the table name passed into it. Basically what it should do is create an update delete and insert stored procedure for that table. see the code below. The problem i have is as soon as i put a begin statement in it ceases to recognise the data types part i.e. the variables for the statement, i can't fathom it out please help


ALTER PROCEDURE [dbo].[SP_Create_Update_procedure]
@table varchar(200),
@DeveloperName varchar(200)
--@Createtable varchar(20)
AS
set nocount on
declare @testTable varchar(8000)

declare @testTable2 varchar(8000)
declare @testTable3 varchar(8000)
declare @opration varchar(8000)
declare @final varchar(8000)
declare @final2 varchar(8000)
declare @final3 varchar(8000)
declare @OP varchar(100)

set @testTable=''
set @testTable2=''
set @final=''
set @final2=''
set @final3=''
set @testTable3=''
set @opration=''
set @OP = ''
declare @Datetime varchar(50)
set @Datetime=getdate()


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 + ' '


set @final2 ='IF @CmdType = Insert
Begin

' + @opration + '

End

If @CmdType = ''Update''

Begin

Insert Into ' + @table + '_Audit
(
'+@testtable +',recordstatus
)
select *,''Amend'' from ' + @table + ' where id = @id

Update ' + @table + ' Set ' + @table +' where id = @id
End

If @CmdType = ''Delete'''

set @final3= 'Begin


Insert Into ' + @table + '_Audit
(
'+@testtable +',recordstatus
)
select *, ''Delete'' from ' + @table + ' where id = @id

Delete from ' + @table + ' where id = @id
End'

exec (@final + @final2 + @final3)
return @final + @final2 + @final3 + @testTable2

 

[gmmastros]

You may be re-inventing the wheel. Take a look here:

http://www.sqlbook.com/scripts/AutoGenerateCRUDStoredProcs.txt

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom