Sp_GenInsert
After this process runs, it generates a complete Insert process for the given table. If the original table has an identification column, you must manually delete the SET IDNTITY_INSERT ON statement in the generated process.
The syntax is as follows
sp_GenInsert < Table Name >,< Stored Procedure Name >
Take northwind database as an example
sp_GenInsert 'Employees', 'INS_Employees'
Finally, an Insert stored procedure will be generated. With it, you can do further development.
Sp_GenUpdate
It generates an update stored procedure for a table. The syntax is as follows:
sp_GenUpdate < Table Name >,< Primary Key >,< Stored Procedure Name >
Take northwind database as an example
sp_GenUpdate 'Employees','EmployeeID','UPD_Employees'
After running, generate the stored procedure as shown below:
Create Procedure UPD_Employees
@EmployeeID int
@LastName nvarchar(40) ,
@FirstName nvarchar(20) ,
@Title nvarchar(60) ,
@TitleofCourtesy nvarchar(50) ,
@BirthDate datetime ,
@HireDate datetime ,
@Address nvarchar(120) ,
@City nvarchar(30) ,
@Region nvarchar(30) ,
@PostalCode nvarchar(20) ,
@Country nvarchar(30) ,
@HomePhone nvarchar(48) ,
@Extension nvarchar(8) ,
@Phote image ,
@Notes ntext ,
@ReportsTo int ,
@PhotoPath nvarchar(510)
AS
UPDATE Employees
SET
LastName = @LastName,
FirstName = @FirstName,
Title = @Title,
TitleofCourtesy = @TitleofCourtesy,
BirthDate = @BirthDate,
HireDate = @HireDate,
Address = @Address,
City = @City,
Regin = @Regin,
PostalCode = @PostCode,
Country = @Country,
HomePhone = @HomePhone,
Extension = @Extension,
Photo = @Photo
Notes = @Notes，
ReportsTo = @ReportsTo,
PhotoPath = @PhotoPath
WHERE EmployeeID = @EmployeeID
Using the above two stored procedures saved me a lot of time. Especially after changing the table structure, reconstructing each stored procedure. You can rewrite these two programs to automatically generate other stored procedures.
SQL Server Writes Stored Procedure Widgets
The following are the source programs of 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 it 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(35),'@' + )
+ rtrim()
+ case when rtrim() in ('binary','char','nchar','nvarchar','varbinary','varchar') then '(' + rtrim(convert(char(4),)) + ')'
when rtrim() 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 systypes on =
where id = @TableID and <> '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
+ case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder + @maxcol + 3 as colorder
from syscolumns
join systypes on =
where id = @TableID and <> '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 '@' +
+ case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder + (2 * @maxcol + 6) as colorder
from syscolumns
join systypes on =
where id = @TableID and <> 'sysname'
union
select ')',(3 * @maxcol) + 7 as colorder
order by colorder
select type from #tempproc order by colorder
drop table #tempproc
SQL Server Writes Stored Procedure Widgets
Function: Create Update stored procedure for a given table
Syntax: sp_GenUpdate <Table Name>,<Primary Key>,<Stored Procedure Name>
Take the northwind database as an example
sp_GenUpdate 'Employees','EmployeeID','UPD_Employees'
Note: If you create this procedure in the Master system database, you can use it in all databases on your server.
===========================================================*/
CREATE procedure sp_GenUpdate
@TableName varchar(130),
@PrimaryKey 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),'@' + )
+ rtrim()
+ case when rtrim() in ('binary','char','nchar','nvarchar','varbinary','varchar') then '(' + rtrim(convert(char(4),)) + ')'
when rtrim() 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 systypes on =
where id = @TableID and <> 'sysname'
union
select 'AS',@maxcol + 1 as colorder
union
select 'UPDATE ' + @TableName,@maxcol + 2 as colorder
union
select 'SET',@maxcol + 3 as colorder
union
select + ' = @' +
+ case when colorder < @maxcol then ','
when colorder = @maxcol then ' '
end
as type,
colorder + @maxcol + 3 as colorder
from syscolumns
join systypes on =
where id = @TableID and <> @PrimaryKey and <> 'sysname'
union
select 'WHERE ' + @PrimaryKey + ' = @' + @PrimaryKey,(2 * @maxcol) + 4 as colorder
order by colorder
select type from #tempproc order by colorder
drop table #tempproc
/*===========================*/