script to create default stored procs
The following script will write basic update insert and delete stored procedures for a given table.
It will add comments to the sp's with the autyhors name and also script a single grant statement for a given database user.
I find this script extremely useful for both rad development and keeping consistency in my naming and structure of sp's
have fun
/******************************************************************************
File: createProcs.sql
Desc: This file scripts out the default stored procs for a given table.
it will create a an sp for insert update and delete on a table with a single field key
to use write the tablename databaseuser and author and then simply run in text mode in query analyser
Auth: Peter Livesey
Date: 25/2/2006
*******************************************************************************
Change History
*******************************************************************************
Date: Author: Description:
*******************************************************************************/
DECLARE @TABLENAME varchar(200)
DECLARE @DBUSER varchar(200)
DECLARE @AUTHOR varchar(100)
/*write the table name here*/
SET @TABLENAME= 'customers'
/*write the name of the DBUSER which should have rights to stored procs here*/
SET @DBUSER = 'dbuser'
/*write yourname here*/
SET @AUTHOR = 'Peter Livesey'
/*do not touch below code*/
DECLARE @PARAMETERLIST varchar (8000)
SET @PARAMETERLIST = ''
DECLARE @PARAMETERLISTWITHIDENTITY varchar (8000)
SET @PARAMETERLISTWITHIDENTITY = ''
DECLARE @DELETEPARAMETER varchar (8000)
SET @DELETEPARAMETER = ''
DECLARE @COLUMNLIST varchar (8000)
SET @COLUMNLIST = ''
DECLARE @UPDATELIST varchar (8000)
SET @UPDATELIST = ''
DECLARE @VARLIST varchar (8000)
SET @VARLIST = ''
DECLARE @CRLF char(2)
SET @CRLF = char(13) + char(10)
DECLARE @TAB char(2)
SET @TAB = ' '
DECLARE @IDNAME varchar(50)
DECLARE @IDENTITY bit
SET @IDENTITY = 0
if (@AUTHOR = '')
SET @AUTHOR = 'System'
/*check to see whether the key field is an identity*/
IF (exists (SELECT '*'
FROM
syscolumns sc
JOIN
systypes st
ON(
sc.xtype = st.xusertype
)
WHERE
sc.id = OBJECT_ID(@TABLENAME)
and autoval IS NOT NULL
)
)
BEGIN
SET @IDENTITY= 1
END
/*get the id*/
SELECT @IDNAME = b.name
FROM
syscolumns b
INNER JOIN
sysobjects o
on
o.parent_obj = b.id
and o.uid = b.colid
where
b.id = OBJECT_ID(@TABLENAME)
and o.xtype = 'PK'
/*create the parameter list*/
/******************************************************************************/
SELECT @PARAMETERLIST =@PARAMETERLIST + @CRLF + '@'+ sc.name + @TAB + @TAB + st.name
+
CASE st.name
WHEN 'char' THEN '(' + CONVERT(varchar(10),sc.length) + ') ='''''
WHEN 'varchar' THEN '(' + CONVERT(varchar(10),sc.length) + ') = '''''
WHEN 'binary' THEN '(' + CONVERT(varchar(10),sc.length) + ') = '''''
WHEN 'varbinary' THEN '(' + CONVERT(varchar(10),sc.length) + ') = '''''
ELSE ''
END
+ ','
FROM
syscolumns sc
JOIN
systypes st
ON(
sc.xtype = st.xusertype
)
WHERE
sc.id = OBJECT_ID(@TABLENAME)
and autoval IS NULL
ORDER BY
sc.colorder
/*rip the last comma from the parameterlist*/
SET @PARAMETERLIST = LEFT(@PARAMETERLIST,LEN(@PARAMETERLIST)-1)
/******************************************************************************/
SELECT @PARAMETERLISTWITHIDENTITY =@PARAMETERLISTWITHIDENTITY + @CRLF + '@'+ sc.name + @TAB + @TAB + st.name
+
CASE st.name
WHEN 'char' THEN '(' + CONVERT(varchar(10),sc.length) + ') ='''''
WHEN 'varchar' THEN '(' + CONVERT(varchar(10),sc.length) + ') = '''''
WHEN 'binary' THEN '(' + CONVERT(varchar(10),sc.length) + ') = '''''
WHEN 'varbinary' THEN '(' + CONVERT(varchar(10),sc.length) + ') = '''''
WHEN 'nchar' THEN '(' + CONVERT(varchar(10),sc.length/2) + ') = '''''
WHEN 'nvarchar' THEN '(' + CONVERT(varchar(10),sc.length/2) + ') = '''''
ELSE ''
END
+ ','
FROM
syscolumns sc
JOIN
systypes st
ON(
sc.xtype = st.xusertype
)
WHERE
sc.id = OBJECT_ID(@TABLENAME)
ORDER BY
sc.colorder
/*rip the last comma from the parameterlist*/
SET @PARAMETERLISTWITHIDENTITY = LEFT(@PARAMETERLISTWITHIDENTITY,LEN(@PARAMETERLISTWITHIDENTITY)-1)
/******************************************************************************/
SELECT @DELETEPARAMETER = '@'+ sc.name + @TAB + @TAB + st.name
+
CASE st.name
WHEN 'char' THEN '(' + CONVERT(varchar(10),sc.length) + ') =NULL'
WHEN 'varchar' THEN '(' + CONVERT(varchar(10),sc.length) + ') = NULL'
WHEN 'binary' THEN '(' + CONVERT(varchar(10),sc.length) + ') = NULL'
WHEN 'nchar' THEN '(' + CONVERT(varchar(10),sc.length/2) + ') = NULL'
WHEN 'nvarchar' THEN '(' + CONVERT(varchar(10),sc.length/2) + ') =NULL'
WHEN 'varbinary' THEN '(' + CONVERT(varchar(10),sc.length) + ') = NULL'
ELSE '=NULL'
END
FROM
syscolumns sc
INNER JOIN
systypes st
ON
sc.xtype = st.xusertype
INNER JOIN
sysindexes si
ON
sc.id = si.id
and sc.colid = si.indid
WHERE
sc.id = OBJECT_ID(@TABLENAME)
and indid=1
/******************************************************************************/
/*create the columnlist*/
select @COLUMNLIST=@COLUMNLIST+ @CRLF + @TAB + @TAB + sc.name + ','
FROM
syscolumns sc
JOIN
systypes st
ON(
sc.xtype = st.xusertype
)
WHERE
sc.id = OBJECT_ID(@TABLENAME)
and autoval IS NULL
ORDER BY
sc.colorder
/*rip the last comma from the columnlist*/
SET @COLUMNLIST = LEFT(@COLUMNLIST,LEN(@COLUMNLIST)-1)
/* create the var list*/
/******************************************************************************/
select @VARLIST =@VARLIST + @CRLF + @TAB + @TAB +'@'+ sc.name
+ ','
FROM
syscolumns sc
JOIN
systypes st
ON(
sc.xtype = st.xusertype
)
WHERE
sc.id = OBJECT_ID(@TABLENAME)
and autoval IS NULL
ORDER BY
sc.colorder
/*rip the last comma*/
SET @VARLIST = LEFT(@VARLIST,LEN(@VARLIST)-1)
/******************************************************************************/
SELECT @UPDATELIST = @UPDATELIST + @CRLF + @TAB + @TAB + sc.name + '= @' + sc.name + ','
FROM
syscolumns sc
JOIN
systypes st
ON(
sc.xtype = st.xusertype
)
WHERE
sc.id = OBJECT_ID(@TABLENAME)
and autoval is null
ORDER BY
sc.colorder
/*rip the last comma*/
SET @UPDATELIST = LEFT(@UPDATELIST,LEN(@UPDATELIST)-1)
/******************************************************************************/
/*first of all we create the insert stored procedure*/
/*cretae header just need to print this off*/
PRINT 'SET QUOTED_IDENTIFIER OFF'
PRINT 'GO'
PRINT 'SET ANSI_NULLS ON'
PRINT 'GO'
PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[usp_insert' + @TABLENAME + ']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure [dbo].[usp_insert' + @TABLENAME + ']'
PRINT 'GO'
PRINT @CRLF
PRINT @CRLF
PRINT 'CREATE proc [dbo].[usp_insert' + @TABLENAME + ']'
PRINT @PARAMETERLIST
PRINT @CRLF
PRINT 'AS'
/*PRINT the header*/
PRINT '/********************************************************************'
PRINT 'AUTHOR : ' + @AUTHOR
PRINT 'DATE : ' + CONVERT(varchar(13),getdate(),103)
PRINT 'PURPOSE :Insert Stored Procedure for table ' + @TABLENAME + ' Automatically generated by script'
PRINT '*********************************************************************/'
PRINT @CRLF
PRINT 'BEGIN'
PRINT 'INSERT INTO ' + @TABLENAME
PRINT @TAB + '('
PRINT @COLUMNLIST
PRINT @TAB + ')'
PRINT ' VALUES'
PRINT @TAB + '('
PRINT @VARLIST
PRINT @TAB + ')'
PRINT @CRLF
/*if this is an identity keyed table then we need to return the identity*/
IF (@IDENTITY =1)
PRINT 'RETURN @@IDENTITY'
PRINT @CRLF
PRINT 'END'
PRINT @CRLF
PRINT 'GO'
PRINT 'SET QUOTED_IDENTIFIER OFF '
PRINT 'GO'
PRINT 'SET ANSI_NULLS ON '
PRINT 'GO'
PRINT 'GRANT EXEC ON usp_insert' + @TABLENAME + ' TO ' + @DBUSER
/************************************************************************
now for the update proc
************************************************************************/
PRINT 'SET QUOTED_IDENTIFIER OFF'
PRINT 'GO'
PRINT 'SET ANSI_NULLS ON'
PRINT 'GO'
PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[usp_update' + @TABLENAME + ']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure [dbo].[usp_update' + @TABLENAME + ']'
PRINT 'GO'
PRINT @CRLF
PRINT @CRLF
PRINT 'CREATE proc [dbo].[usp_update' + @TABLENAME + ']'
PRINT @PARAMETERLISTWITHIDENTITY
PRINT @CRLF
PRINT 'AS'
/*PRINT the header*/
PRINT '/********************************************************************'
PRINT 'AUTHOR : ' + @AUTHOR
PRINT 'DATE : ' + CONVERT(varchar(13),getdate(),103)
PRINT 'PURPOSE :update Stored Procedure for table ' + @TABLENAME + ' Automatically generated by script'
PRINT '*********************************************************************/'
PRINT @CRLF
PRINT 'BEGIN'
PRINT 'UPDATE ' + @TABLENAME + ' SET'
PRINT @UPDATELIST
PRINT 'WHERE ' + @IDNAME + '=@'+ @IDNAME
PRINT 'END'
PRINT @CRLF
PRINT 'GO'
PRINT 'SET QUOTED_IDENTIFIER OFF '
PRINT 'GO'
PRINT 'SET ANSI_NULLS ON '
PRINT 'GO'
PRINT 'GRANT EXEC ON usp_update' + @TABLENAME + ' TO ' + @DBUSER
/*************************************************************************
now for the delete
*************************************************************************/
PRINT 'SET QUOTED_IDENTIFIER OFF'
PRINT 'GO'
PRINT 'SET ANSI_NULLS ON'
PRINT 'GO'
PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[usp_delete' + @TABLENAME + ']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure [dbo].[usp_delete' + @TABLENAME + ']'
PRINT 'GO'
PRINT @CRLF
PRINT @CRLF
PRINT 'CREATE proc [dbo].[usp_delete' + @TABLENAME + ']'
PRINT @DELETEPARAMETER
PRINT @CRLF
PRINT 'AS'
/*PRINT the header*/
PRINT '/********************************************************************'
PRINT 'AUTHOR : ' + @AUTHOR
PRINT 'DATE : ' + CONVERT(varchar(13),getdate(),103)
PRINT 'PURPOSE :delete Stored Procedure for table ' + @TABLENAME + ' Automatically generated by script'
PRINT '*********************************************************************/'
PRINT @CRLF
PRINT 'BEGIN'
PRINT 'DELETE FROM ' + @TABLENAME
PRINT 'WHERE ' + @IDNAME + '= @' + @IDNAME
PRINT 'END'
PRINT @CRLF
PRINT 'GO'
PRINT 'SET QUOTED_IDENTIFIER OFF '
PRINT 'GO'
PRINT 'SET ANSI_NULLS ON '
PRINT 'GO'
PRINT 'GRANT EXEC ON usp_update' + @TABLENAME + ' TO ' + @DBUSER
/*now the select procedure*/
PRINT 'SET QUOTED_IDENTIFIER OFF'
PRINT 'GO'
PRINT 'SET ANSI_NULLS ON'
PRINT 'GO'
PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[usp_get' + @TABLENAME + ']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure [dbo].[usp_get' + @TABLENAME + ']'
PRINT 'GO'
PRINT @CRLF
PRINT @CRLF
PRINT 'CREATE proc [dbo].[usp_get' + @TABLENAME + ']'
PRINT @DELETEPARAMETER
PRINT @CRLF
PRINT 'AS'
/*PRINT the header*/
PRINT '/********************************************************************'
PRINT 'AUTHOR : ' + @AUTHOR
PRINT 'DATE : ' + CONVERT(varchar(13),getdate(),103)
PRINT 'PURPOSE :retrieves a row of databy id for table ' + @TABLENAME + ' Automatically generated by script'
PRINT '*********************************************************************/'
PRINT @CRLF
PRINT 'BEGIN'
PRINT 'SELECT '
PRINT @COLUMNLIST
PRINT 'FROM ' + @TABLENAME
PRINT 'WHERE ' + @IDNAME + '= COALESCE(@' + @IDNAME + ',' + @IDNAME +')'
PRINT 'END'
PRINT @CRLF
PRINT 'GO'
PRINT 'SET QUOTED_IDENTIFIER OFF '
PRINT 'GO'
PRINT 'SET ANSI_NULLS ON '
PRINT 'GO'
PRINT 'GRANT EXEC ON usp_get' + @TABLENAME + ' TO ' + @DBUSER
declare @FK Table (id int,
name sysname,
name2 sysname,
type varchar(20),
size int)
insert into @FK
SELECT b.id,b.name,b2.name,st.name,CASE st.name
WHEN 'nchar' THEN b.length/2
WHEN 'nvarchar' THEN b.length/2
ELSE b.length
END
FROM
syscolumns b
INNER JOIN
sysindexes si
ON
b.id = si.id
and b.colid = si.indid
and b.id in (
select o2.id
from
sysobjects o
inner join
sysforeignkeys fk
on
o.id= fk.fkeyid
inner join
sysobjects o2
on fk.rkeyid = o2.id
where
fk.fkeyid = OBJECT_ID(@TABLENAME)
)
INNER JOIN
sysforeignkeys fk
on
fk.rkeyid = b.id
inner join syscolumns b2
on b2.id = fk.rkeyid
and b2.colid=1
JOIN
systypes st
ON
b2.xtype = st.xusertype
where
si.indid = 1
/* now I need to loop through the forigmn keys and create the select procs*/
declare @pointer int
DECLARE @FKNAME sysname
DECLARE @FKSIZE int
DECLARE @FKTYPE varchar(100)
while exists (select '*' from @fk)
BEGIN
select @pointer = max(id) from @fk
select @FKNAME = name, @FKSIZE = size, @FKTYPE = type from @fk where id = @pointer
PRINT 'SET QUOTED_IDENTIFIER OFF'
PRINT 'GO'
PRINT 'SET ANSI_NULLS ON'
PRINT 'GO'
PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[usp_get' + @TABLENAME + 'by' + @FKNAME + ']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure [dbo].[usp_get' + @TABLENAME + 'by' + @FKNAME + ']'
PRINT 'GO'
PRINT @CRLF
PRINT @CRLF
PRINT 'CREATE proc [dbo].[usp_get' + @TABLENAME + 'by' + @FKNAME + ']'
PRINT '@' + @FKNAME + @tab + @FKTYPE + CASE @FKTYPE
WHEN 'char' THEN '(' + CONVERT(varchar(10),@FKSIZE) + ')'
WHEN 'varchar' THEN '(' + CONVERT(varchar(10),@FKSIZE) + ')'
WHEN 'binary' THEN '(' + CONVERT(varchar(10),@FKSIZE) + ')'
WHEN 'nchar' THEN '(' + CONVERT(varchar(10),@FKSIZE/2) + ')'
WHEN 'nvarchar' THEN '(' + CONVERT(varchar(10),@FKSIZE/2) + ')'
WHEN 'varbinary' THEN '(' + CONVERT(varchar(10),@FKSIZE) + ')'
ELSE ''
END
PRINT @CRLF
PRINT 'AS'
/*PRINT the header*/
PRINT '/********************************************************************'
PRINT 'AUTHOR : ' + @AUTHOR
PRINT 'DATE : ' + CONVERT(varchar(13),getdate(),103)
PRINT 'PURPOSE :retrieves data related to ' + @FKname +'in the database'
PRINT '*********************************************************************/'
PRINT @CRLF
PRINT 'BEGIN'
PRINT 'SELECT '
PRINT @COLUMNLIST
PRINT 'FROM ' + @TABLENAME
PRINT 'WHERE ' + @FKNAME + '= @' + @FKNAME
PRINT 'END'
PRINT @CRLF
PRINT 'GO'
PRINT 'SET QUOTED_IDENTIFIER OFF '
PRINT 'GO'
PRINT 'SET ANSI_NULLS ON '
PRINT 'GO'
PRINT 'GRANT EXEC ON usp_get' + @TABLENAME + 'by' + @FKNAME + ' TO ' + @DBUSER
delete from @fk where id = @pointer
END
