SQL Server tool for writing stored procedures (2)

SQL Server tool for writing stored procedures
The following are the source code of the two stored procedures:
/* ===================================================== ======================================

Syntax: sp_geninsert < Table Name > , < Stored Procedure name >
Take the northwind database as an Example
Sp_geninsert 'ployees', 'ins _ Employees'

Note: If you create this process in the Master System database, you can use this process in all the databases on your server.

========================================================== ======================================= */

Create procedure sp_geninsert
@ Tablename varchar (130 ),
@ Procedurename varchar (130)
As
Set nocount on

Declare @ maxcol int,
@ Tableid int

Set @ tableid = object_id (@ tablename)

Select @ maxcol = max (colorder)
From syscolumns
Where id = @ tableid

Select 'create procedure '+ rtrim (@ procedurename) as type, 0 as colorder into # tempproc
Union
Select convert (char (35), '@' + syscolumns. Name)
+ Rtrim (policypes. Name)
+ Case when rtrim (policypes. name) in ('binary ', 'Char', 'nchar ', 'nvarchar', 'varbinary ', 'varchar ') then' ('+ rtrim (convert (char (4), syscolumns. length) + ')'
When rtrim (policypes. Name) not in ('binary ', 'Char', 'nchar ', 'nvarchar', 'varbinary ', 'varchar') then''
End
+ Case when colorder < @ Maxcol Then ','
When colorder = @ Maxcol Then''
End
As type,
Colorder
From syscolumns
Join policypes on syscolumns. xtype = Policypes. xtype
Where ID = @ Tableid And policypes. Name < > 'Sysname'
Union
Select 'as', @ maxcol + 1 as colorder
Union
Select 'insert into' + @ tablename, @ maxcol + 2 as colorder
Union
Select '(', @ maxcol + 3 as colorder
Union
Select syscolumns. Name
+ Case when colorder < @ Maxcol Then ','
When colorder = @ Maxcol Then''
End
As type,
Colorder + @ maxcol + 3 as colorder
From syscolumns
Join policypes on syscolumns. xtype = Policypes. xtype
Where ID = @ Tableid And policypes. Name < > 'Sysname'
Union
Select ')', (2 * @ maxcol) + 4 as colorder
Union
Select 'values', (2 * @ maxcol) + 5 as colorder
Union
Select '(', (2 * @ maxcol) + 6 as colorder
Union
Select '@' + syscolumns. Name
+ Case when colorder < @ Maxcol Then ','
When colorder = @ Maxcol Then''
End
As type,
Colorder + (2 * @ maxcol + 6) as colorder
From syscolumns
Join policypes on syscolumns. xtype = Policypes. xtype
Where ID = @ Tableid And policypes. Name < > 'Sysname'
Union
Select ')', (3 * @ maxcol) + 7 as colorder
Order by colorder


Select Type from # tempproc order by colorder

Drop table # tempproc