SQL Server writes stored procedure gadgets (ii)

server| Stored Procedures

SQL Server writes stored procedure gadgets
The following are the source programs for two stored procedures
/*===========================================================

Syntax: Sp_geninsert <table name>,<stored Procedure name>
Take the Northwind database as an example
Sp_geninsert ' Employees ', ' ins_employees '

Note: If you create this procedure in the Master System database, you can use the procedure in all 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 (), ' @ ' + syscolumns.name)
+ RTrim (systypes.name)
+ Case when RTrim (Systypes.name) in (' Binary ', ' char ', ' nchar ', ' nvarchar ', ' varbinary ', ' varchar ') Then ' (' + RTrim ') (convert (char (4), syscolumns.length)) + ')'
When RTrim (Systypes.name) isn't 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 systypes on syscolumns.xtype = Systypes.xtype
WHERE id = @TableID and systypes.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 systypes on syscolumns.xtype = Systypes.xtype
WHERE id = @TableID and systypes.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 systypes on syscolumns.xtype = Systypes.xtype
WHERE id = @TableID and systypes.name <> ' sysname '
Union
Select ') ', (3 * @maxcol) + 7 as Colorder
ORDER BY Colorder

Select type from #tempproc order by Colorder

drop table #tempproc