MYSQL网站制作之将表数据天生SQL剧本的存储历程

由于MySQL数据库已经如此普及，对企业来说它无疑是一个更好的选择。存储历程|剧本|数据zlt982001
　　将表数据天生SQL剧本的存储历程:
CREATEPROCEDUREdbo.UspOutputData
@tablenamesysname
AS
declare@columnvarchar(1000)
declare@columndatavarchar(1000)
declare@sqlvarchar(4000)
declare@xtypetinyint
declare@namesysname
declare@objectIdint
declare@objectnamesysname
declare@identint
setnocounton
set@objectId=object_id(@tablename)
if@objectIdisnull--判ο笫欠翊嬖
begin
printTheobjectnotexists
return
end
set@objectname=rtrim(object_name(@objectId))
if@objectnameisnullorcharindex(@objectname,@tablename)=0--此判别不周密
begin
printobjectnotincurrentdatabase
return
end
ifOBJECTPROPERTY(@objectId,IsTable)1--判ο笫欠袷table
begin
printTheobjectisnottable
return
end
select@ident=status&0x80fromsyscolumnswhereid=@objectidandstatus&0x80=0x80
if@identisnotnull
printSETIDENTITY_INSERT+@TableName+ON
declaresyscolumns_cursorcursor
forselectc.name,c.xtypefromsyscolumnscwherec.id=@objectidorderbyc.colid
opensyscolumns_cursor
set@column=
set@columndata=
fetchnextfromsyscolumns_cursorinto@name,@xtype
while@@fetch_status-1
begin
if@@fetch_status-2
begin
if@xtypenotin(189,34,35,99,98)--timestamp不需处置，image,text,ntext,sql_variant临时不处置
begin
set@column=@column+casewhenlen(@column)=0thenelse,end+@name
set@columndata=@columndata+casewhenlen(@columndata)=0thenelse,,,
end
+casewhen@xtypein(167,175)then++@name++--varchar,char
when@xtypein(231,239)thenN++@name++--nvarchar,nchar
when@xtype=61then+convert(char(23),+@name+,121)+--datetime
when@xtype=58then+convert(char(16),+@name+,120)+--smalldatetime
when@xtype=36then+convert(char(36),+@name+)+--uniqueidentifier
else@nameend
end
end
fetchnextfromsyscolumns_cursorinto@name,@xtype
end
closesyscolumns_cursor
deallocatesyscolumns_cursor
set@sql=setnocountonselectinsert+@tablename+(+@column+)values(as--,+@columndata+,)from+@tablename
print--+@sql
exec(@sql)
if@identisnotnull
printSETIDENTITY_INSERT+@TableName+OFF
GO
execUspOutputData你的表名
DBaaS系统其实具有更大的市场机遇：像其他云服务一样，DBaaS意味着更短的销售周期，更少的启动费用，持续不断的收入，也意味着比之前更多的客户。