Create all basic stored procedures for any table

Here is the another quick solution to the SQL server database users for creating all basic stored procedures for any given table...

Thanks to Kailash Paliwal, Ashish Chitranshi and Abhishek Mishra(my friends and the webteam) to search and make this functioning.

So read on and enjoy the database using database...


USE [dbName]
GO
/****** Object: StoredProcedure [dbo].[MyTool] Script Date: 02/25/2011 05:53:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MyTool]
@tblName Varchar(50)
AS
BEGIN


--CREATE TABLE #tmp_Structure
--(
--IsIdentity VARCHAR(MAX),
--[Table] VARCHAR(MAX),
--colId VARCHAR(MAX),
--ColumnName VARCHAR(MAX),
--ColumnVariable VARCHAR(MAX),
--DataType VARCHAR(MAX),
--ColumnParameter VARCHAR(MAX)
--)

Declare @dbName Varchar(50)
Declare @insertSPName Varchar(50), @updateSPName Varchar(50), @deleteSPName Varchar(50), @searchSPName Varchar(50), @getSPName Varchar(50);
Declare @tablColumnParameters Varchar(max);
Declare @tableColumns Varchar(max)
Declare @tableColumnVariables Varchar(max);
Declare @tablColumnParametersUpdate Varchar(max);
Declare @tablColumnParametersSearch Varchar(max);
Declare @tableCols Varchar(max);
Declare @tableColsSearch Varchar(max);
Declare @space Varchar(50);
Declare @maxspace Varchar(500);
Declare @spaceHalf Varchar(50) ;
Declare @colName Varchar(100) ;
Declare @colVariable Varchar(100) ;
Declare @colParameter Varchar(100) ;
Declare @colIdentity bit ;
Declare @strSpText Varchar(max);
Declare @updCols Varchar(max);
Declare @delParamCols Varchar(max);
Declare @whereCols Varchar(max);
Declare @whereSearchCols Varchar(max);
DECLARE @lengthOfColumnToCountSpaces Varchar(50)
Set @tblName = SubString(@tblName,CharIndex('.',@tblName)+1, Len(@tblName))


Set @insertSPName = '[dbo].[Insert' + Replace(@tblName,'_','') +']' ;
Set @updateSPName = '[dbo].[Update' + Replace(@tblName,'_','') +']' ;
Set @deleteSPName = '[dbo].[Delete' + Replace(@tblName,'_','') +']' ;
Set @searchSPName = '[dbo].[Search' + Replace(@tblName,'_','') +']' ;
Set @getSPName = '[dbo].[Get' + Replace(@tblName,'_','') +']' ;

SET @lengthOfColumnToCountSpaces = 0
Set @space = REPLICATE(' ', 4) ;
Set @spaceHalf = REPLICATE(' ', 2) ;
Set @tablColumnParameters = '' ;
Set @tableColumns = '' ;
Set @tableColumnVariables = '' ;
Set @strSPText = '' ;
Set @tableCols = '' ;
Set @tableColsSearch = '' ;
Set @updCols = '' ;
Set @delParamCols = '' ;
Set @whereCols = '' ;
set @whereSearchCols = '';
Set @tablColumnParametersUpdate = '' ;
Set @tablColumnParametersSearch = '' ;
SET NOCOUNT ON


/*
To get the length of max cloumn
*/
SELECT @lengthOfColumnToCountSpaces = max(COUNT) FROM
(
SELECT LEN(syscolumns.name) "Count",syscolumns.name FROM sysobjects sysobjects , syscolumns , systypes
WHERE sysobjects.id = syscolumns.id
and syscolumns.xusertype = systypes.xusertype
and sysobjects.xtype = 'u'
and sysobjects.name = @tblName
and systypes.xusertype not in (189)
) "a"
PRINT @lengthOfColumnToCountSpaces
/*
To get the length of max cloumn
*/
SET @lengthOfColumnToCountSpaces = ISNULL(@lengthOfColumnToCountSpaces,0)
set @maxspace = REPLICATE(' ', @lengthOfColumnToCountSpaces) ;
-- Get all columns & data types for a table
--INSERT INTO #tmp_Structure
SELECT distinct
COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity') as 'IsIdentity',
sysobjects.name as 'Table',
syscolumns.colid ,
'[' + syscolumns.name + ']' as 'ColumnName',
'@'+syscolumns.name as 'ColumnVariable',
upper(systypes.name) +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + REPLACE(Convert(varchar(10),syscolumns.prec),'-1','max') +')' Else '' end as 'DataType' ,
--'@'+syscolumns.name + REPLICATE(' ',((convert(INT,@lengthOfColumnToCountSpaces) + convert(INT,'25')))) + UPPER(systypes.name) +
'@'+syscolumns.name + REPLICATE(' ',((convert(INT,@lengthOfColumnToCountSpaces) + convert(INT,'15')) - convert(INT,len(syscolumns.name)))) + UPPER(systypes.name) +
--'@'+syscolumns.name + REPLICATE(' ',(convert(INT,@lengthOfColumnToCountSpaces) + convert(INT,@spaceHalf))) + UPPER(systypes.name) +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + REPLACE(Convert(varchar(10),syscolumns.prec),'-1','max') +')' Else '' end as 'ColumnParameter'
Into #tmp_Structure
From sysobjects , syscolumns , systypes
Where sysobjects.id = syscolumns.id
and syscolumns.xusertype = systypes.xusertype
and sysobjects.xtype = 'u'
and sysobjects.name = @tblName
and systypes.xusertype not in (189)
Order by syscolumns.colid
SELECT * FROM #tmp_Structure

-- Get all Primary KEY columns & data types for a table
SELECT t.name as 'Table',
c.colid ,
'[' + c.name + ']' as 'ColumnName',
'@'+c.name as 'ColumnVariable',
systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),c.length) +')' Else '' end as 'DataType' ,
'@'+c.name + ' ' + systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),c.length) +')' Else '' end as 'ColumnParameter'
Into #tmp_PK_Structure
FROM sysindexes i, sysobjects t, sysindexkeys k, syscolumns c, systypes
WHERE i.id = t.id AND
i.indid = k.indid AND i.id = k.ID And
c.id = t.id AND c.colid = k.colid AND
i.indid BETWEEN 1 And 254 AND
c.xusertype = systypes.xusertype AND
(i.status & 2048) = 2048 AND t.id = OBJECT_ID(@tblName)

/* Read the table structure and populate variables*/
Declare SpText_Cursor Cursor For
Select ColumnName, ColumnVariable, ColumnParameter, IsIdentity
From #tmp_Structure


Open SpText_Cursor

Fetch Next From SpText_Cursor Into @colName, @colVariable, @colParameter, @colIdentity
While @@FETCH_STATUS = 0
Begin
If (@colIdentity=0)
Begin
Set @tablColumnParameters = @tablColumnParameters + @colParameter + CHAR(13) + @space + ',' ;
Set @tableCols = @tableCols + @colName + ',' ;
Set @tableColumns = @tableColumns + @colName + CHAR(13) + @space + @space + ',' ;
Set @tableColumnVariables = @tableColumnVariables + @colVariable + CHAR(13) + @space + @space + ',' ;
Set @updCols = @updCols + @colName + ' = ' + @colVariable + CHAR(13) + @space + @space + ',' ;
End

Set @tableColsSearch = @tableColsSearch + @colName + CHAR(13) + @space + @space + ',' ;

Set @tablColumnParametersUpdate = @tablColumnParametersUpdate + @colParameter + CHAR(13) + @space + ',' ;
Set @tablColumnParametersSearch = @tablColumnParametersSearch + @colParameter + '= NULL' + CHAR(13) + @space +',';
--Set @tablColumnParametersSearch = @tablColumnParametersSearch + @colParameter + '= NULL,' + CHAR(13) + @space;
Fetch Next From SpText_Cursor Into @colName, @colVariable, @colParameter , @colIdentity
End

Close SpText_Cursor
Deallocate SpText_Cursor

/* Read the Primary Keys from the table and populate variables*/
Declare SpPKText_Cursor Cursor For
Select ColumnName, ColumnVariable, ColumnParameter
From #tmp_PK_Structure

Open SpPKText_Cursor
Fetch Next From SpPKText_Cursor Into @colName, @colVariable, @colParameter
While @@FETCH_STATUS = 0
Begin
Set @delParamCols = @delParamCols + @colParameter + CHAR(13) + @space + ',' ;
Set @whereCols = @whereCols + @colName + ' = ' + @colVariable + ' AND ' ;
Fetch Next From SpPKText_Cursor Into @colName, @colVariable, @colParameter
End
Close SpPKText_Cursor
Deallocate SpPKText_Cursor

/* Read the table structure and get all columns to search data*/
Declare SpText_Cursor Cursor For
Select ColumnName, ColumnVariable
From #tmp_Structure
Open SpText_Cursor
Fetch Next From SpText_Cursor Into @colName, @colVariable
While @@FETCH_STATUS = 0
Begin
Set @whereSearchCols = @whereSearchCols + '(' + @colVariable + ' IS NULL OR ' + @colName + ' = ' + @colVariable + ')' + ' AND ' + CHAR(13) + @space + @space + @spaceHalf;
Fetch Next From SpText_Cursor Into @colName, @colVariable
End
Close SpText_Cursor
Deallocate SpText_Cursor
-- Stored procedure scripts starts here
If (LEN(@tablColumnParameters)>0)
Begin
Set @tablColumnParameters = LEFT(@tablColumnParameters,LEN(@tablColumnParameters)-1) ;
Set @tablColumnParametersUpdate = LEFT(@tablColumnParametersUpdate,LEN(@tablColumnParametersUpdate)-1) ;
Set @tablColumnParametersSearch = LEFT(@tablColumnParametersSearch,LEN(@tablColumnParametersSearch)-1) ;
Set @tableColumnVariables = LEFT(@tableColumnVariables,LEN(@tableColumnVariables)-1) ;
Set @tableColumns = LEFT(@tableColumns,LEN(@tableColumns)-1) ;
Set @tableCols = LEFT(@tableCols,LEN(@tableCols)-1) ;
Set @tableColsSearch = LEFT(@tableColsSearch,LEN(@tableColsSearch)-1) ;
Set @updCols = LEFT(@updCols,LEN(@updCols)-1) ;

If (LEN(@whereCols)>0)
Begin
Set @whereSearchCols = 'WHERE ' + LEFT(@whereSearchCols,LEN(@whereSearchCols)-6) ;
Set @whereCols = 'WHERE ' + LEFT(@whereCols,LEN(@whereCols)-4) ;
Set @delParamCols = LEFT(@delParamCols,LEN(@delParamCols)-1) ;
End
ELSE
BEGIN
Set @whereSearchCols = 'WHERE ' + LEFT(@whereSearchCols,LEN(@whereSearchCols)-6) ;
END

/* Create INSERT stored procedure for the table if it does not exist */
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@insertSPName) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + '/*-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + '-- Author : dbo'
Set @strSPText = @strSPText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + '-- Description : Insert Procedure for ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @insertSPName + ' ' + @tableCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @insertSPName
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParameters
Set @strSPText = @strSPText + CHAR(13) + 'AS'
Set @strSPText = @strSPText + CHAR(13) + 'BEGIN'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + @space + 'INSERT INTO [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + '( '
Set @strSPText = @strSPText + CHAR(13) + @space + @space + ' ' + @tableColumns
Set @strSPText = @strSPText + CHAR(13) + @space + ')'
Set @strSPText = @strSPText + CHAR(13) + @space + 'VALUES'
Set @strSPText = @strSPText + CHAR(13) + @space + '('
Set @strSPText = @strSPText + CHAR(13) + @space + @space + ' ' + @tableColumnVariables
Set @strSPText = @strSPText + CHAR(13) + @space + ')'
Set @strSPText = @strSPText + CHAR(13) + 'END'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
-- Print @strSPText ;

Exec(@strSPText);

if (@@ERROR=0)
Print 'Procedure ' + @insertSPName + ' Created Successfully '

End
Else
Begin
Print 'Sorry!! ' + @insertSPName + ' Already exists in the database. '
End

/* Create UPDATE stored procedure for the table if it does not exist */
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@updateSPName) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + '/*-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + '-- Author : dbo'
Set @strSPText = @strSPText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + '-- Description : Update Procedure for ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @updateSPName + ' ' + @tableCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @updateSPName
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParametersUpdate
Set @strSPText = @strSPText + CHAR(13) + 'AS'
Set @strSPText = @strSPText + CHAR(13) + 'BEGIN'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + @space + 'UPDATE [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + 'SET '
Set @strSPText = @strSPText + CHAR(13) + @space + @space + ' ' + @updCols
Set @strSPText = @strSPText + CHAR(13) + @space + @whereCols
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + 'END'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
-- Print @strSPText ;
Exec(@strSPText);

if (@@ERROR=0)
Print 'Procedure ' + @updateSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @updateSPName + ' Already exists in the database. '
End
/* Create DELETE stored procedure for the table if it does not exist */
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@deleteSPName) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + '/*-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + '-- Author : dbo'
Set @strSPText = @strSPText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + '-- Description : Delete Procedure for ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @deleteSPName + ' ' + @delParamCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @deleteSPName
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @delParamCols
Set @strSPText = @strSPText + CHAR(13) + 'AS'
Set @strSPText = @strSPText + CHAR(13) + 'BEGIN'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + @space + 'DELETE FROM [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + @whereCols
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + 'END'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
-- Print @strSPText ;
Exec(@strSPText);

if (@@ERROR=0)
Print 'Procedure ' + @deleteSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @deleteSPName + ' Already exists in the database. '
END
/* Create Search stored procedure for the table if it does not exist */
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@searchSPName) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + '/*-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + '-- Author : dbo'
Set @strSPText = @strSPText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + '-- Description : Search Procedure for ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @searchSPName + ' ' + @tableCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @searchSPName
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParametersSearch
Set @strSPText = @strSPText + CHAR(13) + 'AS'
Set @strSPText = @strSPText + CHAR(13) + 'BEGIN'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + @space + 'SELECT '+@tableColsSearch+' FROM [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + @whereSearchCols
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + 'END'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
--Print @strSPText ;
Exec(@strSPText);

if (@@ERROR=0)
Print 'Procedure ' + @searchSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @searchSPName + ' Already exists in the database. '
End

/* Create Get stored procedure for the table if it does not exist */
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@getSPName) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + '/*-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + '-- Author : dbo'
Set @strSPText = @strSPText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + '-- Description : Search Procedure for ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @getSPName + ' ' + @tableCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @getSPName
--Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParametersSearch
Set @strSPText = @strSPText + CHAR(13) + 'AS'
Set @strSPText = @strSPText + CHAR(13) + 'BEGIN'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + @space + 'SELECT '+@tableColsSearch+' FROM [dbo].['+@tblName +']'
--Set @strSPText = @strSPText + CHAR(13) + @space + @whereSearchCols
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + 'END'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
--Print @strSPText ;
Exec(@strSPText);

if (@@ERROR=0)
Print 'Procedure ' + @getSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @getSPName + ' Already exists in the database. '
End

End
Drop table #tmp_Structure
Drop table #tmp_PK_Structure
END