Storedprocedure generator

[Piet1976]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[CreateStoredProcedures]
@tablename as varchar(50),
@id as varchar(30)

AS
SET NOCOUNT OFF;


--**************************************************************
--**************************************************************
declare @section1 as varchar(2000)
set @section1=''
declare @section2 as varchar(2000)
set @section2=''
declare @section3 as varchar(2000)
set @section3=''
declare @par as varchar(2000)
set @par=''

SELECT

@section1=@section1+' '+column_name+',' +char(13) --,
,@section2=@section2+' @'+column_name+',' +char(13) --,
,@section3=@section3+' '+column_name+'=@'+column_name+',' +char(13) --,
,@par=@par+'@'+column_name +' as '+
data_type +
case
when isnull(CHARacter_maximum_length,-1)=-1 then ''
when isnull(CHARacter_maximum_length,-1)<>-1 then '('+cast(CHARacter_maximum_length as varchar(6))+')'
end +','+char(13)

FROM information_schema.columns
WHERE table_name = @tablename and @id<>column_name


set @section1=@section1+'!'
set @section2=@section2+'!'
set @section3=@section3+'!'
set @par=@par+'!'

set @par=Replace(@par,','+char(13)+'!','')
set @section1=Replace(@section1,','+char(13)+'!','')
set @section2=Replace(@section2,','+char(13)+'!','')
set @section3=Replace(@section3,','+char(13)+'!','')
--**************************************************************
--**************************************************************




declare @Insertcommand as varchar(8000)
set @Insertcommand='create procedure [dbo].[' + @tablename+'Insertcommand]' +char(13) +char(13)
set @Insertcommand=@Insertcommand+@par+char(13)+char(13) +'as '
set @Insertcommand=@Insertcommand+char(13)+char(13)+'insert into '+@tablename
set @Insertcommand=@Insertcommand+char(13)+'('+@section1+') '+ char(13)+'Values '
set @Insertcommand=@Insertcommand+char(13)+'('+@section2+')'
exec (@Insertcommand)




declare @Updatecommand as varchar(8000)
set @Updatecommand='create procedure [dbo].[' + @tablename+'Updatecommand]' +char(13) +char(13)
set @Updatecommand=@Updatecommand+'@'+@Id+ ' as bigint,'+char(13)
set @Updatecommand=@Updatecommand+@par+char(13)+char(13) +'as '
set @Updatecommand=@Updatecommand+char(13)+char(13)+'update '+@tablename +' set '
set @Updatecommand=@Updatecommand+char(13)+@section3+ char(13)
set @Updatecommand=@Updatecommand+' where '+@id +'=@'+@id
exec (@updatecommand)




declare @SelectCommand as varchar(8000)
set @SelectCommand='create procedure [dbo].[' + @tablename+'SelectCommand]' +char(13)
set @SelectCommand=@SelectCommand+char(13)+char(13) +'as '
set @SelectCommand=@SelectCommand+char(13)+'Select '--+@tablename +' set '
set @SelectCommand=@SelectCommand+char(13)+@section1+ char(13)
set @SelectCommand=@SelectCommand+'from ' +@tablename
exec(@SelectCommand)
--
--print @SelectCommand

declare @DeleteCommand as varchar(8000)
set @DeleteCommand='create procedure [dbo].[' + @tablename+'DeleteCommand]' +char(13)
set @DeleteCommand=@DeleteCommand+'@'+@Id+ ' as bigint'+char(13)
set @DeleteCommand=@DeleteCommand+char(13)+char(13) +'as '
set @DeleteCommand=@DeleteCommand+char(13)+'Delete from '+ @tablename +' where '+@id +'=@'+@id
exec(@DeleteCommand)


GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

 

[RiverGuy]

And your question?

 

[Piet1976]

I just wanted to share this with everybody And hopefully get nice replies.

 

[RiverGuy]

I see. It's always nice to see tips added. Adding a description of what it is and choosing the "helpful tip" icon instead of the question mark when posting might help next time.

By the way, what does it do? Create standard insert, update and delete stored procedures for a table? I think an option to choose whether to execute the script, or output the script text would also be a nice feature, so that a dba has the ability to "see the code before running the code.

 

[Piet1976]

you are right,

It create's insert/update/delete/select queries.

I sugjest that people use it in a development environment so nothing can go wrong.

You can change the 'exec' code to 'Print' to see the storedprocedure being printed to the output screen.

Please post your own version if it has added functionality.