IFEXISTS(SELECTname
FROMsysobjects
WHEREname=Ncreate_table
ANDtype=P)
DROPPROCEDUREcreate_table
go
createprocdbo.create_table
@table_namevarchar(60),---Tablename
@datatypevarchar(1000),---separatedbycomma,
@strnvarchar(3000)---inputstringpastedfromwebpage
AS
BEGIN
declare@dttable(idintidentity(1,1),fld_namevarchar(30),fld_typevarchar(20),blankint)
declare@sqlttable(sql_statementvarchar(8000))
declare@tmpvarchar(1000),@num1int,@num2int,@sqlnvarchar(4000)
declare@anvarchar(3000),@iint,@jint,@kint,@mint,@xnvarchar(1000)
SETNOCOUNTON
ifobject_id(@table_name)isnotnull
begin
set@a=TABLE+@table_name+exists,chooseanewone!
RAISERROR(@a,16,1)
return
end
while@i>0
begin
select@i=charindex(,,@datatype)
--checkdatatypelikedecimal(10,4)
if@i>charindex((,@datatype)and@i<charindex(),@datatype)
set@i=charindex(),@datatype)+1
select@j=charindex(k,@datatype)
set@m=0
if(@j>1and@j<@i)or(@i=0and@j=len(@datatype))set@m=-1
if@i>1
begin
insertinto@dt(fld_type,blank)
values(left(@datatype,@i-1+@m),casewhen@m=-1then1else0end)
select@datatype=right(@datatype,len(@datatype)-@i)
end
if@i=0andlen(@datatype)>0
insertinto@dt(fld_type,blank)values(left(@datatype,len(@datatype)+@m),
casewhen@m=-1then1else0end)
if@i=1orlen(@datatype)=0
begin
RAISERROR(errordatatype,commasigncannotbeaprefixorsurfix,16,1)
return
end
set@num1=@num1+1
end
--反省范例
ifexists(selectfld_typefrom@dt
where(casewhencharindex((,fld_type)>0then
left(fld_type,charindex((,fld_type)-1)
elsefld_typeend)notin(selectnamefromsystypes)or
charindex((,fld_type)*charindex(),fld_type)=0and
charindex((,fld_type)+charindex(),fld_type)>0)
begin
RAISERROR(errordatatype.,16,1)
return
end
--提取字段和数据
set@a=replace(@str,char(9),)---TABchar
set@a=rtrim(ltrim(@a))
ifcharindex(char(13)+char(10),right(@a,len(@a)-1))=0orlen(@a)=0
begin
RAISERROR(inputdataerror,checkyourdata.,16,1)
return
end
ifobject_id(tempdb.dbo.#xx)isnotnulldroptable#xx
selectidentity(int,1,1)ID,space(50)valinto#xxwhere1=2
set@k=0
set@num2=0
set@m=0
whilelen(@a)>0
begin
set@i=1
set@x=left(@a,1)
if@x=char(10)begin
if@m>@num2and@num2>0andcharindex(k,@datatype)=0begin
RAISERROR(numberofdataisgreaterthanthecolumns,youshouldaddkindatatypedifinition.,16,1)
return
end
set@m=0
end
if@xnotin(,char(13),char(10))
begin
set@i=charindex(,@a)
set@j=charindex(char(13)+char(10),@a)
set@m=@m+1
if@k-1set@k=@k+1
if@j>0and(@j<@ior@j>@iandsubstring(@a,@i,@j-@i)=space(@j-@i))begin
set@i=@j
if@k>@num2and@k-1set@num2=@k
set@k=-1
end
if@i=0set@i=(casewhen@j>0then@jelselen(@a)+1end)
select@j=max(ID)from#xx
if@m=1or@j<=@num1or(selectblankfrom@dtwhereID=@m-1)1
begin
if@j<@num1set@x=[+replace(rtrim(left(@a,@i-1)),],]])+]
elseset@x=rtrim(left(replace(@a,,),@i-1))
insertinto#xx(val)values(@x)
end
else
begin
update#xxsetval=val++rtrim(left(@a,@i-1))whereID=@j
set@m=@m-1
end
end
if@i<len(@a)set@a=ltrim(right(@a,len(@a)-@i))
elseset@a=
end