In a project if we have 50 tables, and average 10-15 columns then to write stored procedure for insert and update we have to take average 30 minutes each, then more than 25 hours will be required for only data insert/update operaion. This will be very difficult job… typing table name, column name we will do mistake which cost our development time. Always I prefer to generate code by code itself.
Here the InsertUpdateSP will do the job for us. As an example I have created one table called “Member” and follwed by this table uspMemberInsertUpdate
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
— InsertUpdateSP Member
create procedure [dbo].[InsertUpdateSP]
( @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 @sqlUpdate varchar(4000), @sqlWhere varchar(4000)
declare @keyColumn int
declare @PKColumnName varchar(255)
declare @IDentityColumnName varchar(255)
declare @totalcolumn int
declare @ColCount int
declare crsColumn cursor for
select name from syscolumns where id=object_id(@tablename)
and status<>128
declare crsParam cursor for
select (select max(name) from systypes where xtype=sc.xtype and name<>’sysname’) param,
length, name
from syscolumns sc where id= ( select id from sysobjects where name=@tablename)
DECLARE obj_cursor_pk CURSOR FOR
SELECT syscolumns.name + ‘ = @’ + syscolumns.name
FROM sysindexes, syscolumns, sysindexkeys
WHERE syscolumns.colid = sysindexkeys.colid
and syscolumns.id = sysindexes.id
and sysindexkeys.id = sysindexes.id
and syscolumns.id=object_id(@tablename)
and sysindexes.indid = 1 and sysindexkeys.indid=1
SELECT @keyColumn=Count(*)
FROM sysindexes, syscolumns, sysindexkeys
WHERE syscolumns.colid = sysindexkeys.colid
and syscolumns.id = sysindexes.id
and sysindexkeys.id = sysindexes.id
and syscolumns.id=object_id(@tablename)
and sysindexes.indid = 1 and sysindexkeys.indid=1
select @IDentityColumnName=name
from syscolumns
where object_id(@tablename)=id and status=128
select @totalcolumn=count(*)
from syscolumns
where object_id(@tablename)=id
———————————————————————-
begin
———————————————————————-
—————————————- Header Information ———-
select @first=1
set @colCount=0
SELECT @sqlUpdate = ‘ UPDATE [‘ + @tablename + ‘] SET ‘
print ‘/*
Procedure Name : usp’ + replace(@tablename,’tbl’,”) + ‘InsertUpdate
Version : 1.0
Dated : ‘ + convert(varchar(12),getdate(),107) + ‘
Purpose : For insert / update for data into ‘ + @tablename +’
Tables Accessed : ‘ + @tablename + ‘
*/’
print ‘CREATE PROCEDURE usp’ + replace(@tablename,’tbl’,”) + ‘InsertUpdate
(‘
————————————————————————–
—————————————- Parameter List Information ——
OPEN crsParam
select @First = 1
FETCH NEXT FROM crsParam INTO @strParam, @length, @cname
WHILE @@FETCH_STATUS = 0
BEGIN
set @colCount = @colCount+1
If (@strParam = ‘VARCHAR’) OR (@strParam = ‘NCHAR’) OR (@strParam = ‘CHAR’) OR (@strParam = ‘NVARCHAR’)
if @totalcolumn <> @colCount
if @length = -1
print ‘ @’ + @cname + ‘ varchar(max),’
else
print ‘ @’ + @cname + ‘ ‘ + @strParam + ‘ (‘ + ltrim(rtrim(str(@length))) + ‘),’
else
if @length = -1
print ‘ @’ + @cname + ‘ varchar(max),’
else
print ‘ @’ + @cname + ‘ ‘ + @strParam + ‘ (‘ + ltrim(rtrim(str(@length))) + ‘)’
Else
begin
if @totalcolumn = @colCount
begin
if @IDentityColumnName = @cname
print ‘ @’ + @cname + ‘ ‘ + @strParam + ‘ OUT’
else
print ‘ @’ + @cname + ‘ ‘ + @strParam + ”
end
else
begin
if @IDentityColumnName = @cname
print ‘ @’ + @cname + ‘ ‘ + @strParam + ‘ OUT,’
else
print ‘ @’ + @cname + ‘ ‘ + @strParam + ‘,’
end
end
if @IDentityColumnName <> @cname
set @sqlUpdate = @sqlUpdate + ‘ [‘ + @cname + ‘] = @’ + @cname +’,’
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
set @first = 1
set @sqlWhere = ‘ WHERE ‘
OPEN obj_cursor_pk
FETCH NEXT FROM obj_cursor_pk INTO @cname
WHILE @@FETCH_STATUS = 0
BEGIN
if @first = 1
begin
set @first = 0
end
else
begin
set @sqlWhere = @sqlWhere + ‘, ‘
end
set @sqlWhere = @sqlWhere + ‘ ‘ + @cname
FETCH NEXT FROM obj_cursor_pk INTO @cname
END
CLOSE obj_cursor_pk
DEALLOCATE obj_cursor_pk
—————————————————————————-
print ‘)
AS
BEGIN TRAN
BEGIN’
IF @keyColumn = 1
BEGIN
SELECT @PKColumnName=syscolumns.name
FROM sysindexes, syscolumns, sysindexkeys
WHERE syscolumns.colid = sysindexkeys.colid
and syscolumns.id = sysindexes.id
and sysindexkeys.id = sysindexes.id
and syscolumns.id=object_id(@tablename)
and sysindexes.indid = 1 and sysindexkeys.indid=1
print ‘ IF @’+@PKColumnName+’>0
BEGIN’
if len(@sqlUpdate)>1
select @sqlUpdate = left(@sqlUpdate,len(@sqlUpdate)-1)
print @sqlUpdate
print @sqlWhere
end
print ‘ END
ELSE’
END
print ‘ BEGIN
INSERT INTO [‘ + @tablename +’] (‘ +left(@strAllColumns ,len(@strAllColumns)-1)+ ‘) ‘
print ‘ VALUES (‘ +left(@strAllColumnsVal,len(@strAllColumnsval)-1)+ ‘)
END
IF @@ERROR <> 0
BEGIN’
IF @IDentityColumnName is not null
print ‘ SET @’+@IDentityColumnName+’=-1’
print ‘ ROLLBACK TRAN
END
ELSE
BEGIN’
IF @IDentityColumnName is not null
print ‘ SET @’+@IDentityColumnName+’=@@Identity’
print ‘ COMMIT TRAN
END
END’
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Member](
[MemberId] [int] IDENTITY(1,1) NOT NULL,
[UserId] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Password] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmailId] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Prefix] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Street] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Pin] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TelRes] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TelOff] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DoB] [smalldatetime] NULL,
[Gender] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MaritalStatus] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Education] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Profession] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Designation] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Company] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Industry] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Image] [smallint] NULL,
[ProfileVisibility] [smallint] NULL,
[Aauthorized] [smallint] NULL,
[Biodata] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JoiningDate] [smalldatetime] NULL,
[LastModifiedDate] [smalldatetime] NULL,
[MemberRole] [tinyint] NULL CONSTRAINT [DF_Member_MemberRole] DEFAULT ((1)),
CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED
(
[MemberId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/*
Procedure Name : uspMemberInsertUpdate
Version : 1.0
Dated : Aug 27, 2008
Purpose : For insert / update for data into Member
Tables Accessed : Member
*/
CREATE PROCEDURE uspMemberInsertUpdate
(
@MemberId int OUT,
@UserId varchar (50),
@Password varchar (50),
@EmailId varchar (50),
@Prefix varchar (10),
@FirstName varchar (50),
@LastName varchar (50),
@Street varchar (500),
@City varchar (50),
@Pin varchar (10),
@State varchar (50),
@Country char (2),
@TelRes varchar (50),
@TelOff varchar (50),
@DoB smalldatetime,
@Gender nvarchar (2),
@MaritalStatus char (10),
@Education varchar (50),
@Profession varchar (50),
@Designation varchar (50),
@Company varchar (100),
@Industry varchar (50),
@Image smallint,
@ProfileVisibility smallint,
@Aauthorized smallint,
@Biodata varchar(max),
@JoiningDate smalldatetime,
@LastModifiedDate smalldatetime,
@MemberRole tinyint
)
AS
BEGIN TRAN
BEGIN
IF @MemberId>0
BEGIN
UPDATE [Member] SET [UserId] = @UserId, [Password] = @Password, [EmailId] = @EmailId, [Prefix] = @Prefix, [FirstName] = @FirstName, [LastName] = @LastName, [Street] = @Street, [City] = @City, [Pin] = @Pin, [State] = @State, [Country] = @Country, [TelRes] = @TelRes, [TelOff] = @TelOff, [DoB] = @DoB, [Gender] = @Gender, [MaritalStatus] = @MaritalStatus, [Education] = @Education, [Profession] = @Profession, [Designation] = @Designation, [Company] = @Company, [Industry] = @Industry, [Image] = @Image, [ProfileVisibility] = @ProfileVisibility, [Aauthorized] = @Aauthorized, [Biodata] = @Biodata, [JoiningDate] = @JoiningDate, [LastModifiedDate] = @LastModifiedDate, [MemberRole] = @MemberRole
WHERE MemberId = @MemberId
END
ELSE
BEGIN
INSERT INTO [Member] ([UserId],[Password],[EmailId],[Prefix],[FirstName],[LastName],[Street],[City],[Pin],[State],[Country],[TelRes],[TelOff],[DoB],[Gender],[MaritalStatus],[Education],[Profession],[Designation],[Company],[Industry],[Image],[ProfileVisibility],[Aauthorized],[Biodata],[JoiningDate],[LastModifiedDate],[MemberRole])
VALUES (@UserId,@Password,@EmailId,@Prefix,@FirstName,@LastName,@Street,
@City,@Pin,@State,@Country,@TelRes,@TelOff,@DoB,@Gender,@MaritalStatus,
@Education,@Profession,@Designation,@Company,@Industry,@Image,@ProfileVisibility,
@Aauthorized,@Biodata,@JoiningDate,@LastModifiedDate,@MemberRole)
END
IF @@ERROR <> 0
BEGIN
SET @MemberId=-1
ROLLBACK TRAN
END
ELSE
BEGIN
SET @MemberId=@@Identity
COMMIT TRAN
END
END
