header image

Auto generated stored procedure by SP [T-SQL]

Posted by: | August 27, 2008 | No Comment |

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

under: 9548