In SQL, how does one automatically generate an update script for the table?

I wrote this question in csdn last time and no one has helped solve it. Try again.

The original problem is: Each time you create a new data table, you need to create an up_tablename stored procedure to perform the insert update delete operation on the table. For example, if the created table is woolendelivedetail, the stored procedure for updating the table is up_woolendelivedetail. Format: Create procedure up_woolendelivedetail @ Intupdateid int, -- to-1 Delete 0 modify 1 add @ Intdedetailid int, @ Intdeid int, @ Intcolorid int, @ Nvd1 nvarchar (20 ), @ Nvd2 nvarchar (20 ), @ Nvd3 nvarchar (20 ), @ Nvd4 nvarchar (20 ), @ Nvd5 nvarchar (20 ), @ Nvd6 nvarchar (20 ), @ Nvd7 nvarchar (20 ), @ Nvd8 nvarchar (20 ), @ Nvd9 nvarchar (20 ), @ Nvd10 nvarchar (20) As If (@ intupdateid = 0) Begin Update woolendelivedetail set deid = @ intdeid, colorid = @ intcolorid, D1 = @ nvd1, D2 = @ nvd2, D3 = @ nvd3, D4 = @ nvd4, D5 = @ nvd5, D6 = @ nvd6, D7 = @ nvd7, D8 = @ nvd8, d9 = @ nvd9, D10 = @ nvd10 Where dedetailid = @ intdedetailid End Else if (@ intupdateid = 1) Begin Set @ intdedetailid = (select isnull (max (dedetailid), 0) from woolendelivedetail) + 1 -- get the new ID Insert into woolendelivedetail (dedetailid, deid, colorid, D1, D2, D3, D4, D5, D6, D7, D8, D9, D10 ) Values (@ Intdedetailid, @ intdeid, @ intcolorid, @ Nvd1, @ nvd2, @ nvd3, @ nvd4, @ nvd5, @ nvd6, @ nvd7, @ nvd8, @ nvd9, @ nvd10 ) End Else Delete from woolendelivedetail where dedetailid = @ intdedetailid Go The structure of other conventional data table update stored procedures is similar to that of the stored procedure. Is there a way to create a new table and execute a general Stored Procedure for generating and updating data, and then generate an updated storage script for the table ?? Hope you can give me some advice. Thank you.

My answer is:

Stored Procedure for Automatically Generating table update data: Create procedure sp_createprocdure @ Tablename nvarchar (50) As /* Function: automatically generates a stored procedure for updating table data. For example, after creating the mytable table, execute sp_createprocdure to generate the data in the mytable table New Stored Procedure up_mytable DESIGN: OK _008 Time: 2006-05 Note: 1. Run exec sp_createprocdure tablename on the query analyzer. 2. Because the total length of the generated string is usually greater than 4000, only print output is used, Copy the file. 3. This method can generate a storage process for updating data for general tables. The update format can be based on the actual Situation modification. Design method: 1. Extract the field information of the table. 2. ── construct the data update process Stored Procedure Parameters Construct the new data section Construct the updated data section ─-Construct the data deletion part 3. multipart print 4. Copy the output results to the new stored procedure interface. */ Declare @ strparameter nvarchar (3000) Declare @ strinsert nvarchar (3000) Declare @ strupdate nvarchar (3000) Declare @ strdelete nvarchar (500) Declare @ strwhere nvarchar (100) Declare @ strnewid nvarchar (100) Declare @ SQL _createproc nvarchar (4000) Set @ SQL _createproc = 'create procedure up _ '+ @ tablename + char (13) +' @ intupdateid int, '+'/*-1 Delete 0 modify 1 add */' Set @ strparameter ='' Set @ strinsert ='' Set @ strupdate ='' Set @ strwhere ='' Declare @ tname nvarchar (50), @ typename nvarchar (50), @ typelength nvarchar (50), @ colstat bit Declare obj_cursor cursor Select * From fn_getobjcolinfo (@ tablename) Open obj_cursor Fetch next from obj_cursor into @ tname, @ typename, @ typelength, @ colstat While @ fetch_status = 0 Begin -- Construct stored procedure parameters Set @ strparameter = @ strparameter + char (13) + '@' + @ tname + ''+ @ typename + ',' -- Construct the new data section If @ colstat = 0 set @ strinsert = @ strinsert + '@' + @ tname + ',' -- Construct the updated data section If (@ strwhere = '') Begin Set @ strnewid = 'set @ '+ @ tname +' = (select isnull (max ('+ @ tname +'), 0) from '+ @ tablename + ') + 1 -- get the new id' Set @ strwhere = 'where' + @ tname + '=' + '@' + @ tname End Else Set @ strupdate = @ strupdate + @ tname + '=' + '@' + @ tname + ',' -- Construct the data deletion part Fetch next from obj_cursor into @ tname, @ typename, @ typelength, @ colstat End Close obj_cursor Deallocate obj_cursor Set @ strparameter = left (@ strparameter, Len (@ strparameter)-1) -- remove the rightmost comma Set @ strupdate = left (@ strupdate, Len (@ strupdate)-1) Set @ strinsert = left (@ strinsert, Len (@ strinsert)-1) -- Stored procedure name and Parameters Print @ SQL _createproc + @ strparameter + char (13) + 'as' -- Modify Print 'if (@ intupdateid = 0 )' Print 'begin' + char (13) Print char (9) + 'update' + @ tablename + 'set' + @ strupdate + char (13) + char (9) + @ strwhere Print 'end' -- Add Print 'if (@ intupdateid = 1 )' Print 'begin' Print char (9) + @ strnewid Print char (9) + 'insert into' + @ tablename + 'select' + @ strinsert Print 'end' -- Delete Print 'else' Print 'begin' Print char (9) + 'delete from' + @ tablename + @ strwhere Print 'end' Print 'Go' Go

 

Some of the UDF fn_getobjcolinfo has the following code: /* Function: returns all fields, stored procedures, and function parameters of a table. DESIGN: OK _008 Time: 2006-05 */ Create Function fn_getobjcolinfo (@ Objname varchar (50 )) Returns @ return_table table ( Tname nvarchar (50 ), Typename nvarchar (50 ), Typelength nvarchar (50 ), Colstat bit ) As Begin Insert @ return_table Select B. Name as field name, C. Name as field type, B. length as field length, whether B. colstat as automatically increases From sysobjects Inner join syscolumns B on A. ID = B. ID Inner join policypes C on C. xusertype = B. xtype Where a. Name = @ objname Order by B. colid Return End

After testing, I feel okay.
Saves a lot of time for writing and updating data.