Automatically generate Insert,update,delete,select SQL statements based on table names

select| Statement Tbproc

CREATE Procedure Tbproc
@model varchar (2), @pagename varchar, @object varchar (a), @autoField varchar (a) =null
As
SET NOCOUNT ON
Select @model =upper (@model)
Select @pagename =lower (@pagename)
Select @object =upper (@object)
DECLARE @head varchar (MB), @headfct varchar (@para), varchar (1500), @content varchar (5000)
DECLARE @paravar varchar, @saveStr varchar (3000), @deleteStr varchar, @selectFieldStr varchar (1000), @ RETURNPK varchar (@pkeyStr varchar), @pkeyParaStr varchar (500)
Select @head = ', @headfct = ', @para = ', @content = '
exec Tbfield @object, @autofield, @paravar output, @saveStr output, @deleteStr output, @selectFieldStr output, @pkeyStr Output, @pkeyParaStr output
Select @head = ' CREATE PROCEDURE p ' + @model + @pagename + ' _ '
--head
print ' Note: unit conversions and currency conversions are implemented in the stored procedure! '
--sav
Select @headfct = @head + ' Sav '
Select @para = @paravar
Select @content = @saveStr
Select @headfct +char (+ @para +char) + ' as ' +char + @content +char (+char) + ' Go ' +char (13)
--tree
Select @para = '
Select @headfct = @head + ' tree '
Select @content = @selectFieldStr
Select @headfct +char (+ @para +char) + ' as ' +char + @content +char (+char) + ' Go ' +char (13)
--del
Select @headfct = @head + ' Del '
Select @para = @pkeyParaStr
--test
--select @paravar, charindex (' @IsValid ', @paravar)
If CHARINDEX (' IsValid ', @selectFieldStr) >0
Select @deleteStr =replace (replace (@deleteStr, ' delete ', ' Update '), ' where ', ' Set isvalid=0 where ')
Select @content = @deleteStr
Select @headfct +char (+ @para +char) + ' as ' +char + @content +char (+char) + ' Go ' +char (13)
--back
Select @headfct = @head + ' Back '
Select @content = @selectFieldStr
Select @headfct +char (+ @para +char) + ' as ' +char + @content +char (+char) + ' Go ' +char (13)


Go

Tbfield

CREATE procedure Tbfield
@object varchar, @autofield varchar, @string varchar OUTPUT, @saveStr varchar (3000) output, @deleteStr varchar OUTPUT, @selectFieldStr varchar (1000) output, @pkeyStr varchar OUTPUT, @pkeyParaStr varchar (500) Output
As
SET NOCOUNT ON
Select @object =upper (@object)
DECLARE @nameStr varchar (1000), @varStr varchar (1000), @updStr varchar (1500), @pkeyvarStr varchar (--,) @pkeyParaStr varchar, @pkeyStr varchar (250) as an output parameter
Select @string = ', @nameStr = ', @varStr = ', @updStr = ', @pkeyvarStr = ', @pkeyStr = ', @pkeyParaStr = '
DECLARE @moneyStr varchar (500)
Select @moneyStr = '
DECLARE @i smallint
Select @i=1
Declare @fieldtb table (PK int Identity,field varchar (32))
Declare @attrtb table (field varchar, typename varchar (), length smallint)
DECLARE @field varchar (32)
DECLARE Curfield cursor FOR
Select name from syscolumns where id=object_id (@OBJECT)
Open Curfield
FETCH NEXT from Curfield into @field
While @ @fetch_status =0
Begin
Insert @fieldtb (field) VALUES (@field)
Select @nameStr = @nameStr + ' + @field + ', '
Select @varStr = @varStr + ' @ ' + @field + ', '
Select @updStr = @updStr + @field + ' =@ ' + @field + ', '
If Len (@updStr) >@i*100
Begin
Select @updStr = @updStr +char (+char) (9) +char (9)
Select @i=@i+1
End
FETCH NEXT from Curfield into @field
End
Close Curfield
Deallocate Curfield
Insert @attrtb
SELECT DISTINCT C.name,replace (replace (d.type_name, ' identity ', '), ' () ', '), c.length
From syscolumns C
INNER JOIN Master.dbo.spt_datatype_info D on c.xtype = D.ss_dtype
where c.id=object_id (@OBJECT)
-----SELECT * FROM @attrtb--Test
Select @i=1
DECLARE @typename varchar (@length), varchar (5)
Declare record cursor for
Select A.*
From @fieldtb F INNER join @attrtb A on F.field=a.field
ORDER BY f.pk
Open record
FETCH NEXT from the record into @field, @typename, @length
While @ @fetch_status =0
Begin
If @typename not in (' varchar ', ' nvarchar ', ' char ', ' nchar ', ' text ', ' ntext ')
Begin
Select @length =case @typename when ' smalldatetime ' then 10
When ' datetime ' then 32
When ' bit ' then 1
else 16
End
If @typename in (' Money ', ' smallmoney ')
Select @moneyStr = @moneyStr + ' @ ' + @field + ' __ ' + @typename + ', '
Select @typename = ' varchar '
End
Select @string = @string + ' @ ' + @field + ' + @typename + ' (' + @length + ') ' + ', '
If Len (@string) >@i*128
Begin
Select @string = @string +char (13)
Select @i=@i+1
End
FETCH NEXT from the record into @field, @typename, @length
End
Close record
Deallocate record
--about Pkeys
Declare @pkeytb table (field varchar (32))
Insert @pkeytb
Select C.name
From syscolumns c INNER join sysindexes i on c.id=i.id
where c.id=object_id (@object) and (I.status & 0x800) =0x800
and (C.name = Index_col (@object, I.indid, 1) or
C.name = Index_col (@object, I.indid, 2) or
C.name = Index_col (@object, I.indid, 3) or
C.name = Index_col (@object, I.indid, 4) or
C.name = Index_col (@object, I.indid, 5) or
C.name = Index_col (@object, I.indid, 6) or
C.name = Index_col (@object, I.indid, 7) or
C.name = Index_col (@object, I.indid, 8) or
C.name = Index_col (@object, I.indid, 9) or
C.name = Index_col (@object, I.indid,) or
C.name = Index_col (@object, I.indid, one) or
C.name = Index_col (@object, I.indid,) or
C.name = Index_col (@object, I.indid,) or
C.name = Index_col (@object, I.indid,) or
C.name = Index_col (@object, I.indid,) or
C.name = Index_col (@object, I.indid, 16)
)
if (select COUNT (*) from @pkeytb) >1
Begin
DECLARE Curpkeys cursor FOR
Select field from @pkeytb
Open Curpkeys
FETCH NEXT from Curpkeys into @field
While @ @fetch_status =0
Begin
Select @pkeyvarStr = @pkeyvarStr + @field + ' =@ ' + @field + ' and '
Select @pkeyStr = @pkeyStr + @field + ', '
Select @i=charindex (' @ ' + @field, @string)
If @i>0 Select @pkeyParaStr = @pkeyParaStr +substring (@string, @i,charindex (', ', @string, @i)-@i) + ', '
FETCH NEXT from Curpkeys into @field
End
Close Curpkeys
Deallocate Curpkeys
Select @pkeyvarStr =left (@pkeyvarStr, Len (@pkeyvarStr)-3)
Select @pkeyStr =left (@pkeyStr, Len (@pkeyStr)-1)
Select @pkeyParaStr =left (@pkeyParaStr, Len (@pkeyParaStr)-1)
End
else if (select count (*) from @pkeytb) =1
Begin
Select @field =field from @pkeytb
Select @pkeyvarStr = @field + ' =@ ' + @field
Select @pkeyStr = @field
Select @i=charindex (' @ ' + @field, @string)
If @i>0 select @pkeyParaStr =substring (@string, @i,charindex (', ', @string, @i)-@i)
End
If Right (@string, 1) =char (13)
Select @string =left (@string, Len (@string)-2)
Else
Select @string =left (@string, Len (@string)-1)
Select @nameStr =left (@nameStr, Len (@nameStr)-1)
Select @varStr =left (@varStr, Len (@varStr)-1)
Select @varStr =replace (@varStr, ' @ModDate ', ' getdate () ')
Select @varStr =replace (@varStr, ' @IsValid ', Space (7) + ' 1 ')
If Right (@updStr, 1) =char (9)
Select @updStr =left (@updStr, Len (@updStr)-4)
Else
Select @updStr =left (@updStr, Len (@updStr)-1)
Select @updStr =replace (@updStr, ' @ModDate ', ' getdate () ')
Select @updStr =replace (@updStr, ' @IsValid ', ' IsValid ')
----Processing @moneystr
DECLARE @covNameStr varchar (1000)
Select @covNameStr = @nameStr
If @moneyStr <> '
BEGIN
DECLARE @itemStr varchar, @itemfield varchar (a), @itemtype varchar (50)
Select @moneyStr = ', ' + @moneyStr
Select @moneyStr
Select @i=1, @itemStr =substring (@moneyStr, @i+1,charindex (', ', @moneyStr, @i+1)-@i-1)
While @itemStr <> '
Begin
Select @itemfield =left (@itemStr, CHARINDEX (' __ ', @itemstr)-1)
Select @itemtype =right (@itemStr, Len (@itemStr)-charindex (' __ ', @itemStr)-1)
Select @covStr = ' cast (' + @itemfield + ' as ' + @itemtype + ') '
Select @varStr =replace (@varStr, @itemfield, @covStr)
Select @updStr =replace (@updStr, @itemfield, @covStr)
Select @covNameStr =replace (@covNameStr, right (@itemfield, Len (@itemfield)-1), Space (Len (@covStr)-len (@itemfield)) + Right (@itemfield, Len (@itemfield)-1))
Select @i=charindex (', ', @moneyStr, @i+1)
If @i=len (@moneyStr)
Break
else Select @itemStr =substring (@moneyStr, @i+1,charindex (', ', @moneyStr, @i+1)-@i-1)
End
End
--------------------
DECLARE @insertStr varchar, @updateStr varchar (), @selectStr varchar--, @deleteStr varchar (500) as output parameters
DECLARE @returnPk varchar ()--, @selectFieldStr varchar (1000) as an output parameter
If @autofield is null
Select @insertStr =char (9) + ' Insert ' + @object + ' (' + @covNameStr + ') ' +char +char (9) + ' values ' +space (len (@object)) + ' ( ' + @varStr + ') '
Else
Select @insertStr =space (3) + ' begin ' +char +char (9) + ' declare @count int ' +char (9) + ' Select @count +char (*) From ' + @object + ' where substring (' + @autofield + ', 3,4) =convert (varchar (4), GETDATE (), () ' +char () +char (9) + ' SELECT @ ' + @autofield + ' = ' XX ' +convert (varchar (4), GETDATE (), +cast (@count +1 as varchar ()) ' +char (+char) +char (9) + ' Insert ' + @object + ' (' + @covNameStr + ') ' +char +char (9) + ' values ' +space (len (@object) + ' (' + @varStr + ') ' +char (13) + Spaces (3) + ' end '
Select @updateStr =char (9) + ' update ' + @object +char (9) + ' set ' + @updStr +char +char (9) + ' where ' + @pkeyvarStr
Select @deleteStr = ' Delete ' + @object + ' where ' + @pkeyvarStr
Select @selectStr = ' select * from ' + @object + ' where ' + @pkeyvarStr
Select @returnPk = ' SELECT @ ' +replace (@pkeyStr, ', ', ', @ ')
If CHARINDEX (' IsValid ', @covNameStr) >0
Select @selectFieldStr = ' isvalid=1 and '
Else
Select @selectFieldStr = '
Select @selectFieldStr = ' SELECT ' + @nameStr + ' from ' + @object + ' where ' + @selectFieldStr + @pkeyvarStr
--declare @saveStr varchar (3000) as the output parameter
--select @moneyStr =stuff (@moneyStr, Len (@moneyStr), 1,char (a) +char (13))
Select @saveStr = ' If not EXISTS (' + @selectStr + ') ' +char + @insertStr +char + ' Else ' +char + @updateStr +char (10) + char (+ @returnPk
If CHARINDEX (' @IsValid ', @string) >0
Select @string =replace (@string, ', @IsValid varchar (1) ', ')
If Charindex (', ' +char + ' @IsValid ', @string) >0
Select @string =replace (@string, ', ' +char + ' @IsValid varchar (1) ', ')
If Charindex (', ' +char + ' @ModDate ', @string) >0
Select @string =replace (@string, ', ' +char + ' @ModDate varchar (32) ', ')
If Charindex (', @ModDate ', @string) >0
Select @string =replace (@string, ', @ModDate varchar (32) ', ')
/*
Select @string
Select @pkeyParaStr
Select @saveStr
--select @insertStr
--select @updateStr
Select @deleteStr
--select @selectStr
Select @selectFieldStr
*/