Tuesday, April 01, 2008

How to Auto-Create Stored Procedures

Use this script. I make no guarantees on it's solidity, but it has been useful. It will try to execute the CREATE PROCEDURE commands it executes, so comment those out if you want to run them manually.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Author: Matthew Webster
-- Create date: 2007-03-20
-- Description: Automatically creates stored procedures for insert, update and delete functions
ALTER PROCEDURE [dbo].[Cms_CreateAuto_Procs]
@author nvarchar(250),
@tableName varchar(8000)
AS
BEGIN
SET NOCOUNT ON;

--declare @tableName nvarchar(50)
--set @tableName = 'editorialtypegroup'
--declare @author nvarchar(50)
--set @author = 'Matthew Webster'

declare @insert varchar(8000)
declare @update varchar(8000)
declare @delete varchar(8000)

declare @key varchar(8000)
declare @fields varchar(8000)
declare @arguments varchar(8000)
declare @variables varchar(8000)
declare @assignments varchar(8000)

set @key=''
set @fields=''
set @arguments=''
set @variables=''
set @assignments=''

select @key=@key+ ', '+column_name
from information_schema.columns
where table_name=@tableName and (COLUMNPROPERTY(OBJECT_ID(@tableName), column_name, 'isidentity') = 1) AND (column_default IS NULL)

select @fields=@fields+ ', '+column_name
from information_schema.columns
where table_name=@tableName and (COLUMNPROPERTY(OBJECT_ID(@tableName), column_name, 'isidentity') = 0) --AND (column_default IS NULL)

select @arguments=@arguments+ ', @'+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=@tableName and (COLUMNPROPERTY(OBJECT_ID(@tableName), column_name, 'isidentity') = 0) and character_maximum_length<>null AND (column_default IS NULL)and data_type<>'text'

select @arguments=@arguments+ ', @'+column_name+' '+data_type
from information_schema.columns
where table_name=@tableName and (COLUMNPROPERTY(OBJECT_ID(@tableName), column_name, 'isidentity') = 0) -- and (character_maximum_length=null or data_type='text' ) AND (column_default IS NULL)

select @variables=@variables+ ', @'+column_name
from information_schema.columns
where table_name=@tableName and (COLUMNPROPERTY(OBJECT_ID(@tableName), column_name, 'isidentity') = 0) --AND (column_default IS NULL)

select @assignments=@assignments+ ', '+column_name+' = @'+column_name
from information_schema.columns
where table_name=@tableName and (COLUMNPROPERTY(OBJECT_ID(@tableName), column_name, 'isidentity') = 0) --AND (column_default IS NULL)

set @key=SUBSTRING(@key,3,len(@key))
set @fields=SUBSTRING(@fields,3,len(@fields))
set @arguments=SUBSTRING(@arguments,3,len(@arguments))
set @variables=SUBSTRING(@variables,3,len(@variables))
set @assignments=SUBSTRING(@assignments,3,len(@assignments))

/*--
select @tableName
select @key
select @fields
select @arguments
select @variables
select @assignments
*/
declare @insertProc varchar(8000)
set @insertProc = '
CREATE PROCEDURE Cms_Auto_Insert'+@tableName+'
'+@arguments+'
AS
BEGIN
SET NOCOUNT ON;
insert into '+@tableName+' ('+@fields+') values ('+@variables+')
END
;
'

declare @updateProc varchar(8000)
set @updateProc = '
CREATE PROCEDURE Cms_Auto_Update'+@tableName+'
@'+@key+' int, '+@arguments+'
AS
BEGIN
SET NOCOUNT ON;
update '+@tableName+'
set '+@assignments+'
where '+@key+' = @'+@key+'
END
;
'

declare @deleteProc varchar(8000)
set @deleteProc = '
CREATE PROCEDURE Cms_Auto_Delete'+@tableName+'
@'+@key+' int
AS
BEGIN
SET NOCOUNT ON;
delete from '+@tableName+' where '+@key+' = @'+@key+'
END
;
'

exec (@insertProc)
exec (@updateProc)
exec (@deleteProc)

END

How to Stack Trace SQL

Just use SQL Profiler - if it's installed! Open the profiler, connect to a database, File->New. Remember to check the boxes for text query and hit run. Let your code do it's bit, then hit stop. But be quick about it, because there's probably a lot of other stuff going on and (like C# debugging) this could all come jumping out you in a very short space of time.....