TBProc
CREATE Procedure TbProc @ model varchar (2), @ pagename varchar (32), @ object varchar (32), @ autoField varchar (32) = nullasset nocount onselect @ model = upper (@model) select @ pagename = lower (@pagename Select @ Object = Upper (@Object) Declare @Head Varchar (100), @ hadfct varchar (105), @ Para Varchar (1500), @ Content Varchar (5000) Declare @paravar varchar (2000), @ SAVESTR VARCHAR ( 3000), @ deleteStr VARCHAR (500), @selectfieldstr varchar (1000), @ ReturnPk varchar (250), @ pkeystr varchar (500), @ pkeyparastr varchar (500) SELECT @HEAD = ', @HEADFCT =' ', @para = '', @ content = '' exec TbField @ object, @ autofield, @ paravar output, @ saveStr output, @ deleteStr output, @ selectFieldStr output, @ pkeyStr output, @ pkeyParaStr outputselect @ head = 'create procedure p' @ model @ Pagename '_'- headprint' Note: Unit conversion and currency conversion are implemented in the stored procedure! '--SAVSELECT @ headfct = @ had ' sav'select @ para = @ paravarslect @ content = @ SavestRSELECT @HEADFCT CHAR (13) @ Para CHAR (13) AS ' CHAR (13) @ Content Char (10) char (13) GO ' Char (13) - Treeselect @para =' 'SELECT @HEADFCT = @ Head ' Tree'select @ Content = @selectfieldstrslect @HEADFCT CHAR (13) @ Para Char (13) 'As' Char (13) @ Content Char (10) CHAR (13) ' Go ' Char (13) - DELSELECT @HEADFCT = @ Head ' Del'select @ Para = @ PKeyparastr - Test - Select @ Paravar, Charindex ('
@SValid ', @ Paravar) If Charindex (' isvalid ', @selectfieldstr)> 0select @ deleteStr = Replace (@ deleteStr,' delete ',' update '),' where ',' set isvalid = 0 where ' SELECT @ Content = @ deletestRSELECT @ HeadFCT Char (13) @ Para Char (13) 'As' Char (13) @ Content Char (10) Char (13) GO' CHAR ( 13) - backselect @ headfct = @ head 'back'select @ Content = @selectfieldstrsTrSelect @ headfct char (13) @ Para char (13) ' as' char (13) @ Content Char (10 ) char (13) 'Go' char (13) gotbfield
CREATE procedure TbField @ object varchar (32), @ autofield varchar (32), @ string varchar (2000) output, @ saveStr varchar (3000) output, @ deleteStr varchar (500) output, @ selectFieldStr varchar (1000) output, @ pkeyStr varchar (500) output, @ pkeyParaStr varchar (500) outputasset nocount onselect @ object = upper (@object) declare @nameStr varchar (1000), @ varStr varchar (1000), @ updStr varchar (1500), @ pkeyvarStr varchar ( 500) -, @ pkeyparastr varchar (150), @ pkeystr varchar (250) As the output parameter select @String = ', @ namestr =', @ Varstr = ', @ Updstr =', @ pkeyvarstr = '', @ pkeyStr = '', @ pkeyParaStr = '' declare @moneyStr varchar (500) select @moneyStr = '' declare @i smallintselect @ i = 1declare @fieldtb table (pk int identity, field varchar (32)) declare @attrtb table (field varchar (32), typename varchar (32), length smallint) declare @field varchar (32) declare curfield cursor forselect name from syscolumns where id = object_id (@OBJECT) open curfieldfetch next from curfield into @fieldwhile @ @ fetch_status = 0beginInsert @fieldTB (Field) VALUES (@field) Select @namester = @ NameStr '' @ Field ',' SELECT @ Varstr = @ Varstr '@' @ Field ',' SELECT @ Updstr = @ Updstr @ Field '@' @ Field ',' IF LEN (@ Updstr)> @ i * 100beginselect @ Updstr = @ Updstr char (13) char (9) char (9) SELECT @ i = @ i 1ndfetch next from curfield INTO @fieldndclose CurfieldDeallocate CurfieldInsert @
Attbselect Distinct C.Name, Replace (D.Type_name, '),' () ','), C.LENGTHFROM 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 = 1Declare @Typename Varchar (32), @ Length varchar (5) Declare Record Cursor Fortelect a. * from @fieldtb f inner join @attrtb a on f.field = a.fieldorder by f.pkopen recordfetch next from record into @ field, @ typename, @ lengthwhile @@ fetch_status = 0beginif @typename not in ( 'varchar', 'nvarchar ',' char ',' nchar ',' text ',' ntext ') beginselect @ length = case @typename when' smalldatetime 'then 10when' datetime 'then 32when' bit 'then 1else 16endif @typename in (' money ', 'smallmoney') select @ moneyStr = @ moneyStr '@' @ field '__' @ typename ',' select @ typename = 'varchar'endselect @ string = @ string ' @ ' @ field ' ' @ typename ' (' @ Length ') ' ', 'if len (@string)> @ i * 128beginselect @ String = @ String Char (13) SELECT @ i = @ i 1ENDFETCH NEXT from Record Into @ Field, @ Typename @ LengthThendClose R ecorddeallocate record - About pkeysdeclare @pkeytb table (field varchar (32)) insert @pkeytbselect c.namefrom 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) orc.name = index_col (@Object, I.indid, 2) orc.name = index_col (@Object, I.indid, 3) orc.name = index_col (@Object, I.indid) , 4) Orc.name = index_col (@Object, I.indid, 5) orc.name = index_col (@Object, I.indid, 6) orc.name = index_col (@Object, I.indid, 7) Orc. Name = index_col (@Object, I.indid, 8) orc.name = index_col (@Object, I.indid, 9) Orc.name = index_col (@Object, I.indid, 10) Orc.name = index_col (@ Object, I.indid, 11) Orc.name = index_col (@Object, I.indid, 12) Orc.name = index_col (@Object, I.indid, 13) orc.name = index_col (@Object, I.indid) , 14) Orc.name = index_col (@Object, I.indid, 15) orc.name = index_col (@Object, I.indid, 16))) IF (Select Count (*) from @pkeytb> 1begindeclare Curpkeys Cursor Forselect field from @pkeytbopen curpkeysfetch next from curpkeys into @fieldwhile @@ fetch_status = 0beginselect @ 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 @fieldendclose curpkeysdeallocate curpkeysselect @ pkeyvarStr = left (@ pkeyvarStr, len (@ PKeyvarstr) -3) SELECT @ pkeystr = left (@ pkeystr, len (@pKeystr) -1) select @ pkeyparastr = left (@pkeyparastr, len (@pkeyparastr) -1) endelse if (select count (*) from @pkeytb ) = 1beginselect @
field = field from @pkeytbselect @ pkeyvarStr = @ field '= @' @ fieldselect @ pkeyStr = @ fieldselect @ i = charindex ( '@' @ field, @ string) if @i> 0 select @ pkeyParaStr = substring (@ String, @ i, charindex (',', @ String, @ i) - @ i) endif right (@ String, 1) = char (13) SELECT @ String = Left (@ string, len (@string) -2 ) elselect @ string = left (@ String, Len (@string) -1) Select @ namestr = left (@namester, 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) Elseselect @ Updstr = Left (@ Updstr, Len (@UPDSTR) -1) Select @ Updstr = Replace (@updstr , '@ ModDate', 'getdate ()') select @ updStr = replace (@updStr, '@ IsValid', 'IsValid') ---- treated @moneyStrdeclare @covNameStr varchar (1000) select @ covNameStr = @ nameStrif @ moneyStr <> '' BEGINdeclare @itemStr varchar (50), @ itemfield varchar (32), @ itemtype varchar (20), @ covStr varchar (50) select @moneyStr = ',' @ moneyStrselect @moneyStrselect @ i = 1, @ iTemstr = SU BString (@ MoneyStr, @ i 1, charindex (',', @ Moneystr, @ i 1) - @ i-1) while @itemstr <> '' beginselect @ itemfield = left (@ itemstr, charindex ('__ ', @ iTeMSTR) -1) SELECT @ itemtype = right (@ itemstr, len (@
Itemstr) -charindex ('__', @ iTemstr) -1) SELECT @ Covstr = 'Cast (' @ Itemfield 'AS' @ ipemtype ')' 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) -1)) Select @ i = charindex (',', @ MoneyStr, @ i 1) if @ i = len (@moneystr) Breakelse select @ temstr = Substring (@Moneystr, @ i 1, charindex (',', @ Moneystr, @ i 1) - @ i-1) endend -------------------- Declare @insertstr varchar (2000), @ selectstr varchar (500) -, @Deletestr varchar (500) As the output parameter declare @returnpk varchar (250) -, @ selectfieldstr varchar (1000) As an output parameter if @autofield is nullselect @ INSERTSTR = char (9) 'INSERT' @ Object '(' @ CovnameStr ')' char (13) char (9) 'VALUES' SPACE (LEN (@Object)) '(' @ Varstr ')' Elseselect @ INSERTSTR = Space (3) 'Begin' Char (13) CHAR (9) 'Declare @count int' char (13) Char (9) 'SELECT @ count = count (*) from' @ Object 'Where Substring (' @ autof Ield ', 3, 4) = Convert (VARCHAR (4), getdate (), 12) CHAR (13) CHAR (9) ' SELECT @ ' @ AutoField ' = '' XX '' Convert ( VARCHAR (4), Getdate (), 12) CAST (@ count 1 as varchar (16)) ' char (13)
Char (13) CHAR (9) 'INSERT' @ Object '(' @ CovnameStr ')' Char (13) CHAR (9) 'VALUES' SPACE (LEN (@Object)) ' (' @ Varstr ') ' Char (13) Space (3) ' end'select @ updateStr = char (9) 'Update' @ Object char (13) char (9) 'set ' @ updStr char (13) char (9) ' where ' @ pkeyvarStrselect @ deleteStr =' delete ' @ object ' where ' @ pkeyvarStrselect @ selectStr =' select * from ' @ object ' where ' @ pkeyvarStrselect @ returnPk = 'select @' replace (@pkeyStr, ',', ', @') if charindex ( 'IsValid', @ covNameStr)> 0select @ selectFieldStr = 'IsValid = 1 and' elseselect @ selectFieldStr = '' select @ selectFieldStr = 'select' @ nameStr 'from' @ object 'where' @ selectFieldStr @ pkeyvarStr - declare @saveStr varchar (3000) as an output parameter --select @ moneyStr = stuff (@moneyStr, Len (@MoneyStr), 1, Char (10) char (13)) SELECT @ SAVESTR = 'IF not exists (' @ SelectStr ')' char (13) @ INSERTSTR CHAR (13) ELSE ' char (13) @ UpdateStr Char (10) char (13) @ ReturnPkif Charindex (' @ isvalid ', @ String> 0 Select @ String = Replace (@String,', @ISVALID VARCHAR (1 ) ',' ') if charIndex (', ' char (13) ' @ISVAL ID ', @ String> 0select @ String = Replace (@string,', '
