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

Rate

5 (2)

Share

Share

Rate

5 (2)