数据库开发
网络新概念，云计算、大数据、O2O、电商。。。。
Sql Server根据表字段自动生成存储过程
2017-04-06 09:50:15
/*-- =======================================================================================
-- Updated by : ivivian 2016.12
-- Author : Deepthi Viswanathan Nair
-- Description : Generate the Insert / Update/ Delete Stored procedure script of any table
-- Exec [dbo].[pro_zxsys_create_sp_for_table] 't_user'
-- ========================================================================================= */
alter PROCEDURE [dbo].[pro_zxsys_create_sp_for_table]
@tblName Varchar(150)
AS
BEGIN
Declare @dbName Varchar(50)
Declare @SPName Varchar(50)--, @updateSPName Varchar(50), @deleteSPName Varchar(50) ;
Declare @tablColumnParameters Varchar(1000), @tableColumns Varchar(1000),@tableColumnVariables Varchar(1000);
Declare @tableCols Varchar(1000), @tablinsertParameters Varchar(1000);
Declare @space Varchar(50) ;
Declare @colName Varchar(100) ;
Declare @colVariable Varchar(100) ;
Declare @colParameter Varchar(100) ;
Declare @colDataType varchar(100);
Declare @strSpText Varchar(8000);
Declare @updCols Varchar(2000);
Declare @delParamCols Varchar(2000);
Declare @whereCols Varchar(2000);
Declare @whereColsSearch Varchar(2000);
Set @tblName = SubString(@tblName,CharIndex('.',@tblName)+1, Len(@tblName))
Set @SPName = '[dbo].[pro_table_' + lower(@tblName) +']' ;
Set @space = REPLICATE(' ', 4) ;
Set @tablColumnParameters = '' ;
Set @tableColumns = '' ;
Set @tableColumnVariables = '' ;
Set @strSPText = '' ;
Set @tableCols = '' ;
Set @updCols = '' ;
Set @delParamCols = '' ;
Set @whereCols = '' ;
Set @whereColsSearch = '' ;
SET NOCOUNT ON
-- Get all columns & data types for a table
-- SELECT distinct
-- sysobjects.name as 'Table',
-- syscolumns.colid ,
-- '[' + syscolumns.name + ']' as 'ColumnName',
-- '@'+syscolumns.name as 'ColumnVariable',
-- systypes.name +
-- Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'DataType' ,
-- '@'+syscolumns.name + ' ' + systypes.name +
-- Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' 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 syscolumns.name!=(SELECT
-- c.name AS ColumnName
-- FROM sys.columns AS c
-- INNER JOIN
-- sys.tables AS t
-- ON t.[object_id] = c.[object_id]
-- where c.is_identity = 1
-- and t.name=@tblName)
-- Order by syscolumns.colid
-- SELECT distinct
-- sysobjects.name as 'Table',
-- syscolumns.colid ,
-- '[' + syscolumns.name + ']' as 'ColumnName',
-- '@'+syscolumns.name as 'ColumnVariable',
-- systypes.name +
-- Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'DataType' ,
-- '@'+syscolumns.name + ' ' + systypes.name +
-- Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' 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
-- Order by syscolumns.colid
-- 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)
--if exists(select * from #tmp_PK_Structure)
--BEGIN
--print 'y'
SELECT distinct
sysobjects.name as 'Table',
syscolumns.colid ,
'[' + syscolumns.name + ']' as 'ColumnName',
'@'+syscolumns.name as 'ColumnVariable',
systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'DataType' ,
'@'+syscolumns.name + ' ' + systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' 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 syscolumns.name!=(SELECT
c.name AS ColumnName
FROM sys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
where c.is_identity = 1
and t.name=@tblName)
Order by syscolumns.colid
--END
--ELSe
--BEGIN
--print 'n'
SELECT distinct
sysobjects.name as 'Table',
syscolumns.colid ,
'[' + syscolumns.name + ']' as 'ColumnName',
'@'+syscolumns.name as 'ColumnVariable',
systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'DataType' ,
'@'+syscolumns.name + ' ' + systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'ColumnParameter'
Into #tmp_Structure1
From sysobjects , syscolumns , systypes
Where sysobjects.id = syscolumns.id
and syscolumns.xusertype = systypes.xusertype
and sysobjects.xtype = 'u'
and sysobjects.name = @tblName
Order by syscolumns.colid
--END
/* Read the table structure and populate variables*/
Declare SpText_Cursor Cursor For
Select ColumnName, ColumnVariable, ColumnParameter,DataType
From #tmp_Structure
Open SpText_Cursor
Fetch Next From SpText_Cursor Into @colName, @colVariable, @colParameter,@colDataType
While @@FETCH_STATUS = 0
Begin
Set @tableColumns = @tableColumns + @colName + ',' ;
Set @tablColumnParameters = @tablColumnParameters + @colParameter + CHAR(13) + @space + ',' ;
Set @tableColumnVariables = @tableColumnVariables + @colVariable + ',' ;
Set @tableCols = @tableCols + @colName + ',' ;
Set @updCols = @updCols + @colName + ' = ' + @colVariable + ',' ;
--for search
set @whereColsSearch =@whereColsSearch + @space+@space+' if '+@colVariable+'!='''' '+CHAR(13)
if left(@colDataType+'nnnnnn',6)='varchar'
set @whereColsSearch =@whereColsSearch + @space+@space+@space+' set @mysql='' and '+@colName + ' like ''''%''+' + @colVariable + '+''%''''''' + CHAR(13)
else
set @whereColsSearch =@whereColsSearch + @space+@space+@space+' set @mysql='' and '+@colName + ' = ''''''+' + @colVariable + '+''''''''' + CHAR(13)
set @whereColsSearch =@whereColsSearch +@space+@space+' '+CHAR(13)
Fetch Next From SpText_Cursor Into @colName, @colVariable, @colParameter,@colDataType
End
Close SpText_Cursor
Deallocate SpText_Cursor
/* for update parameter*/
if exists(select * from #tmp_PK_Structure)
BEGIN
Set @tablinsertParameters=''
Declare SpText_Cursor1 Cursor For
Select ColumnParameter
From #tmp_Structure1
Open SpText_Cursor1
Fetch Next From SpText_Cursor1 Into @colParameter
While @@FETCH_STATUS = 0
Begin
Set @tablinsertParameters = @tablinsertParameters + @colParameter + CHAR(13) + @space + ',' ;
Fetch Next From SpText_Cursor1 Into @colParameter
End
Close SpText_Cursor1
Deallocate SpText_Cursor1
END
/*end for update parameter*/
/* 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
If (LEN(@tablinsertParameters)>0)
Set @tablinsertParameters = LEFT(@tablinsertParameters,LEN(@tablinsertParameters)-1) ;
-- Stored procedure scripts starts here
If (LEN(@tablColumnParameters)>0)
Begin
Set @tablColumnParameters = LEFT(@tablColumnParameters,LEN(@tablColumnParameters)-1) ;
Set @tableColumnVariables = LEFT(@tableColumnVariables,LEN(@tableColumnVariables)-1) ;
Set @tableColumns = LEFT(@tableColumns,LEN(@tableColumns)-1) ;
Set @tableCols = LEFT(@tableCols,LEN(@tableCols)-1) ;
Set @updCols = LEFT(@updCols,LEN(@updCols)-1) ;
If (LEN(@whereCols)>0)
Begin
Set @whereCols = 'WHERE ' + LEFT(@whereCols,LEN(@whereCols)-4) ;
Set @delParamCols = LEFT(@delParamCols,LEN(@delParamCols)-1) ;
End
/* Create INSERT stored procedure for the table if it does not exist */
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@SPName) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
Set @strSPText = @strSPText + CHAR(13) + '/*-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + '-- Author : ivivian'
Set @strSPText = @strSPText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + '-- Description : Procedure for ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @SPName + ' ' + @tableCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @SPName
Set @strSPText = @strSPText + CHAR(13) + @space +' @ftype varchar(10), --add edit delete search '
--if exists(select * from #tmp_PK_Structure)
--Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParameters
--else
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablinsertParameters
Set @strSPText = @strSPText + CHAR(13) + 'AS'
Set @strSPText = @strSPText + CHAR(13) + 'BEGIN'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + @space + ' if @ftype=''add'' '
Set @strSPText = @strSPText + CHAR(13) + @space + ' begin '
Set @strSPText = @strSPText + CHAR(13) + @space +@space + 'INSERT INTO [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space +@space + '( ' + @tableColumns +' )'
Set @strSPText = @strSPText + CHAR(13) + @space +@space + 'VALUES( ' + @tableColumnVariables+' )'
Set @strSPText = @strSPText + CHAR(13) + @space + ' END'
Set @strSPText = @strSPText + CHAR(13) + @space + ' else if @ftype=''edit'' '
Set @strSPText = @strSPText + CHAR(13) + @space + ' begin '
Set @strSPText = @strSPText + CHAR(13) + @space + +@space +'UPDATE [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + +@space +'SET ' + @updCols
Set @strSPText = @strSPText + CHAR(13) + @space + +@space +@whereCols
Set @strSPText = @strSPText + CHAR(13) + @space + ' END'
Set @strSPText = @strSPText + CHAR(13) + @space + ' else if @ftype=''delete'' '
Set @strSPText = @strSPText + CHAR(13) + @space + ' begin '
Set @strSPText = @strSPText + CHAR(13) + @space + @space + 'DELETE FROM [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + @space + @whereCols
Set @strSPText = @strSPText + CHAR(13) + @space + ' END'
Set @strSPText = @strSPText + CHAR(13) + @space + ' else if @ftype=''search'' '
Set @strSPText = @strSPText + CHAR(13) + @space + ' begin '
Set @strSPText = @strSPText + CHAR(13) + @space + ' declare @mysql varchar(4000) '
Set @strSPText = @strSPText + CHAR(13) + @space +@space + 'set @mysql=''select ' + @tableColumns +''''
Set @strSPText = @strSPText + CHAR(13) + @space +@space + 'set @mysql=''from [dbo].['+@tblName +'] where 1=1'''
Set @strSPText = @strSPText + CHAR(13) + @space +@space + @whereColsSearch
Set @strSPText = @strSPText + CHAR(13) + @space + ' print @mysql '
Set @strSPText = @strSPText + CHAR(13) + @space + ' exec(@mysql) '
Set @strSPText = @strSPText + CHAR(13) + @space + ' END'
Set @strSPText = @strSPText + CHAR(13) + 'END'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
Print @strSPText ;
--print
--Exec(@strSPText);
--if (@@ERROR=0)
-- Print 'Procedure ' + @SPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @SPName + ' Already exists in the database. '
End
End
Drop table #tmp_Structure
Drop table #tmp_Structure1
Drop table #tmp_PK_Structure
END
TAG：
SqlServer 存储过程