header image

Auto-Generate Template or Skeleton of an Insert Procedure of a SQL Server Table

Posted by: | September 19, 2005 | No Comment |

DROP  procedure InsertProc
GO


CREATE procedure InsertProc
( @tablename varchar(100))
as
declare @cname varchar(100),@First int, @strAllColumns varchar(5000),@strAllColumnsVal varchar(5000),
 @strParam varchar(5000), @strAllParams varchar(1000), @length int


declare crsColumn cursor  for
 select  name from syscolumns where id=object_id(@tablename)
declare crsParam cursor  for
select (select max(name) from systypes where xtype=sc.xtype) param,
length, name
from syscolumns sc where id= ( select id from sysobjects where
name=@tablename)
———————————————————————-
begin
———————————————————————-
—————————————- Header Information ———-
print ‘/*
 Procedure Name  : stp’ + replace(@tablename,’tbl’,”) + ‘Ins
 Version   : 1.0
 Dated   : ‘ + convert(varchar(12),getdate(),107) + ‘
 Project   :
 Module   :  
 Client   :
 Developer               :
 Location  :
 Calling Program         :
 Purpose   : For insersetion fo data into ‘ + @tablename +’
    Method                  : Get Input Params via
 Input Parameters        : Parameters
 Output Parameter        : 1 Parameter @NewId
 Tables Accessed  : ‘ + @tablename + ‘*/’
print ‘CREATE PROCEDURE stp’ + replace(@tablename,’tbl’,”) + ‘Ins (‘
————————————————————————–
—————————————- Parameter List Information ——
OPEN crsParam
select @First = 1
FETCH NEXT FROM crsParam INTO @strParam, @length, @cname
WHILE @@FETCH_STATUS = 0
BEGIN
  If @strParam = ‘varchar’
   print
‘@’ + @cname + ‘ ‘ + @strParam + ‘ (‘ + ltrim(rtrim(str(@length))) + ‘),’
  Else
   print 
‘@’ +  @cname + ‘ ‘ +  @strParam  + ‘,’


 FETCH NEXT FROM crsParam INTO @strParam, @length, @cname
END
CLOSE crsParam
DEALLOCATE crsParam
—————————————————————————-
OPEN crsColumn
FETCH NEXT FROM crsColumn INTO @cname
select @strAllColumns = ”
select @strAllColumnsVal = ”
WHILE @@FETCH_STATUS = 0
BEGIN
 select @strAllColumns = @strAllColumns  + @cname + ‘,’
 select @strAllColumnsVal = @strAllColumnsVal +
‘@’ + @cname + ‘,’
 FETCH NEXT FROM crsColumn INTO @cname
END
CLOSE crsColumn
DEALLOCATE crsColumn
—————————————————————————-
print 
‘@NewId int out
)
AS
Begin Tran
Begin
 INSERT INTO ‘ + @tablename + ‘(‘
print left(@strAllColumns ,len(@strAllColumns)-1)
print ‘ VALUES (‘
print left(@strAllColumnsVal,len(@strAllColumnsval)-1)
print ‘If @@Error <> 0
       Rollback Tran
 Else
  Begin
   SET @NewId = @@Identity
   Commit Tran
  End
End’
end
GO


InsertProc Customers


 


Output:


/*
 Procedure Name  : stpCustomersIns
 Version   : 1.0
 Dated   : Sep 19, 2005
 Project   :
 Module   :  
 Client   :
 Developer               :
 Location  :
 Calling Program         :
 Purpose   : For insersetion fo data into Customers
    Method                  : Get Input Params via
 Input Parameters        : Parameters
 Output Parameter        : 1 Parameter @NewId
 Tables Accessed  : Customers*/
CREATE PROCEDURE stpCustomersIns (
@CustomerID nchar,
@CompanyName sysname,
@ContactName sysname,
@ContactTitle sysname,
@Address sysname,
@City sysname,
@Region sysname,
@PostalCode sysname,
@Country sysname,
@Phone sysname,
@Fax sysname,
@NewId int out
)
AS
Begin Tran
Begin
 INSERT INTO Customers(
CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax
 VALUES (
@CustomerID,@CompanyName,@ContactName,@ContactTitle,@Address,@City,@Region,@PostalCode,@Country,@Phone,@Fax
If @@Error <> 0
       Rollback Tran
 Else
  Begin
   SET @NewId = @@Identity
   Commit Tran
  End
End


* Note: Here User Defined Data Type / Identity Columns are not taken care of. After generating this Script Need to check and do some changes. This is speed up the development procedure but generating the structure of of the procedure. And also typo will not be there.



 

under: 9548