自动生成对表进行插入和更新的存储过程的存储过程

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

我找到了两个存储过程,能自动生成对一个数据表的插入和更新存储过程,现在奉献给大家!

插入:

Createproceduresp_GenInsert
@TableNamevarchar(130),
@ProcedureNamevarchar(130)
as
setnocounton
declare@maxcolint,
@TableIDint
set@TableID=object_id(@TableName)
select@MaxCol=max(colorder)
fromsyscolumns
whereid=@TableID
select'CreateProcedure'+rtrim(@ProcedureName)astype,0ascolorderinto#TempProc
union
selectconvert(char(35),'@'+syscolumns.name)
+rtrim(systypes.name)
+casewhenrtrim(systypes.name)in('binary','char','nchar','nvarchar','varbinary','varchar')then'('+rtrim(convert(char(4),syscolumns.length))+')'
whenrtrim(systypes.name)notin('binary','char','nchar','nvarchar','varbinary','varchar')then''
end
+casewhencolorder<@maxcolthen','
whencolorder=@maxcolthen''
end
astype,
colorder
fromsyscolumns
joinsystypesonsyscolumns.xtype=systypes.xtype
whereid=@TableIDandsystypes.name<>'sysname'
union
select'AS',@maxcol+1ascolorder
union
select'INSERTINTO'+@TableName,@maxcol+2ascolorder
union
select'(',@maxcol+3ascolorder
union
selectsyscolumns.name
+casewhencolorder<@maxcolthen','
whencolorder=@maxcolthen''
end
astype,
colorder+@maxcol+3ascolorder
fromsyscolumns
joinsystypesonsyscolumns.xtype=systypes.xtype
whereid=@TableIDandsystypes.name<>'sysname'
union
select')',(2*@maxcol)+4ascolorder
union
select'VALUES',(2*@maxcol)+5ascolorder
union
select'(',(2*@maxcol)+6ascolorder
union
select'@'+syscolumns.name
+casewhencolorder<@maxcolthen','
whencolorder=@maxcolthen''
end
astype,
colorder+(2*@maxcol+6)ascolorder
fromsyscolumns
joinsystypesonsyscolumns.xtype=systypes.xtype
whereid=@TableIDandsystypes.name<>'sysname'
union
select')',(3*@maxcol)+7ascolorder
orderbycolorder
selecttypefrom#tempprocorderbycolorder

更新

Createproceduresp_GenUpdate
@TableNamevarchar(130),
@PrimaryKeyvarchar(130),
@ProcedureNamevarchar(130)
as
setnocounton
declare@maxcolint,
@TableIDint
set@TableID=object_id(@TableName)
select@MaxCol=max(colorder)
fromsyscolumns
whereid=@TableID
select'CreateProcedure'+rtrim(@ProcedureName)astype,0ascolorderinto#TempProc
union
selectconvert(char(35),'@'+syscolumns.name)
+rtrim(systypes.name)
+casewhenrtrim(systypes.name)in('binary','char','nchar','nvarchar','varbinary','varchar')then'('+rtrim(convert(char(4),syscolumns.length))+')'
whenrtrim(systypes.name)notin('binary','char','nchar','nvarchar','varbinary','varchar')then''
end
+casewhencolorder<@maxcolthen','
whencolorder=@maxcolthen''
end
astype,
colorder
fromsyscolumns
joinsystypesonsyscolumns.xtype=systypes.xtype
whereid=@TableIDandsystypes.name<>'sysname'
union
select'AS',@maxcol+1ascolorder
union
select'UPDATE'+@TableName,@maxcol+2ascolorder
union
select'SET',@maxcol+3ascolorder
union
selectsyscolumns.name+'=@'+syscolumns.name
+casewhencolorder<@maxcolthen','1 <script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
阅读更多
文章标签: 存储 object
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭