Auto generating data retrieval SP will be done by just a second...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE procedure [dbo].[SelectSP]
( @tablename varchar(100))
as
declare @cname varchar(100),
@strAllColumns varchar(5000)
declare crsColumn cursor for
select name from syscolumns where id=object_id(@tablename)
----------------------------------------------------------------------
begin
----------------------------------------------------------------------
---------------------------------------- Header Information ----------
print '/*
Procedure Name : usp' + replace(@tablename,'tbl','') + 'InsertUpdate
Version : 1.0
Dated : ' + convert(varchar(12),getdate(),107) + '
Purpose : For retrieving data from ' + @tablename +'
Tables Accessed : ' + @tablename + '
*/'
print 'CREATE PROCEDURE uspGet' + replace(@tablename,'tbl','')
OPEN crsColumn
FETCH NEXT FROM crsColumn INTO @cname
select @strAllColumns = ''
WHILE @@FETCH_STATUS = 0
BEGIN
select @strAllColumns = @strAllColumns + '[' + @cname + '],'
FETCH NEXT FROM crsColumn INTO @cname
END
CLOSE crsColumn
DEALLOCATE crsColumn
----------------------------------------------------------------------------
print 'AS
BEGIN
BEGIN'
print ' SELECT ' +left(@strAllColumns ,len(@strAllColumns)-1)+ '
FROM [' + @tablename +']'
print ' END
IF @@ERROR <> 0
BEGIN
RETURN -1
END
ELSE
BEGIN
RETURN 0
END
END'
end
To test by executing the below statement in NorthWind database data retrieval SP will be ready.
SelectSP Customers
/*
Procedure Name : uspCustomersInsertUpdate
Version : 1.0
Dated : Aug 27, 2008
Purpose : For retrieving data from Customers
Tables Accessed : Customers
*/
CREATE PROCEDURE uspGetCustomers
AS
BEGIN
BEGIN
SELECT [CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax]
FROM [Customers]
END
IF @@ERROR <> 0
BEGIN
RETURN -1
END
ELSE
BEGIN
RETURN 0
END
END
set pagesize 2000
set linesize 2000
set long 4000
--create table gensql(line number(4) not null primary key,txt varchar2(4000))
delete gensql
/
commit
/
declare
cursor cl
is
select 'CREATE TABLE '||TABLE_NAME||' ( ' tbl,
column_name clmn,
SUBSTR(decode(data_type,'VARCHAR2','VARCHAR2('||TO_CHAR(DATA_LENGTH)||')',
'CHAR','CHAR('||TO_CHAR(DATA_LENGTH)||')',
'NUMBER', DECODE(DATA_PRECISION, NULL, 'NUMBER',
'NUMBER('||TO_CHAR(DATA_PRECISION)||','||TO_CHAR(DATA_SCALE)||')'),
'DATE','DATE'),1,14) data_type,
decode(nullable, 'N', 'Not Null', null) null_stat
from cols
where table_name not in (select tname from tab where tabtype='VIEW');
flag number(1) :=0;
lineno number(4) :=0;
tbl varchar2(100);
clmn varchar2(100);
data_type varchar2(30);
null_stat varchar2(15);
lstcol varchar2(100);
begin
for c in cl loop
tbl:=c.tbl;
clmn:=c.clmn;
data_type := c.data_type;
null_stat := c.null_stat;
if flag = 0 then
-- dbms_output.put_line(tbl);
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,tbl);
flag:=1;
end if;
if lstcol <> tbl then
-- dbms_output.put_line(');');
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,');');
-- dbms_output.put_line(tbl);
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,tbl);
-- dbms_output.put_line(' '||clmn||' '||data_type||' '||null_stat);
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,' '||clmn||' '||data_type||' '||null_stat);
else
-- dbms_output.put_line(' '||clmn||' '||data_type||' '||null_stat);
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,', '||clmn||' '||data_type||' '||null_stat);
end if;
lstcol:=tbl;
end loop;
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,');');
end;
/
declare
flag number(2) := 0;
tname varchar2(100); cname varchar2(100);
tbl varchar2(100); con varchar2(100); str varchar2(2000);
lineno number(4);
cursor cmain is
select TABLE_NAME,CONSTRAINT_NAME
from user_constraints
where CONSTRAINT_TYPE='P';
cursor cdetail(k varchar2) is
select TABLE_NAME,column_name from user_cons_columns where CONSTRAINT_NAME=k;
begin
begin
select nvl(max(line),0) into lineno from gensql;
exception
when no_data_found then null;
end;
for cm in cmain loop
tbl:=cm.TABLE_NAME; con:=cm.CONSTRAINT_NAME;
str:='alter table '|| tbl ||' add constraints '||con||' primary key (';
open cdetail(con);
fetch cdetail into tname,cname;
loop
exit when cdetail%NOTFOUND;
if flag = 0 then
str:=str||cname;
flag:=1;
else
str:=str||','||cname;
end if;
fetch cdetail into tname,cname;
end loop;
str:=str||') ;';
flag:=0;
close cdetail;
-- dbms_output.put_line(str);
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,str);
end loop;
end;
/
declare
flag number(2) := 0;
tname varchar2(100); cname varchar2(100);
tbl varchar2(100); con varchar2(100); rcon varchar2(100); str varchar2(2000);
lineno number(4);
cursor cmain is
select TABLE_NAME,R_CONSTRAINT_NAME,CONSTRAINT_NAME
from user_constraints
where R_CONSTRAINT_NAME is not null;
cursor cdetail(k varchar2) is
select TABLE_NAME,column_name from user_cons_columns where CONSTRAINT_NAME=k;
begin
begin
select nvl(max(line),0) into lineno from gensql;
exception
when no_data_found then null;
end;
for cm in cmain loop
tbl:=cm.TABLE_NAME; con:=cm.CONSTRAINT_NAME; rcon:=cm.R_CONSTRAINT_NAME;
str:='alter table '|| tbl ||' add constraints '||con||' foreign key (';
open cdetail(con);
fetch cdetail into tname,cname;
loop
exit when cdetail%NOTFOUND;
if flag = 0 then
str:=str||cname;
flag:=1;
else
str:=str||','||cname;
end if;
fetch cdetail into tname,cname;
end loop;
str:=str||') ';
flag:=0;
close cdetail;
open cdetail(rcon);
fetch cdetail into tname,cname;
loop
exit when cdetail%NOTFOUND;
if flag = 0 then
str:=str||' references '|| tname || ' (';
str:=str||cname;
flag:=1;
else
str:=str||','||cname;
end if;
fetch cdetail into tname,cname;
end loop;
str:=str||') ;';
flag:=0;
close cdetail;
-- dbms_output.put_line(str);
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,str);
end loop;
end;
/
declare
tname varchar2(100); ttype varchar2(100);
txt varchar2(2000);
lineno number(4);
cursor cmain is
select distinct NAME,TYPE
from user_source;
cursor cdetail(k varchar2) is
select text from user_source where NAME=k order by line;
begin
begin
select nvl(max(line),0) into lineno from gensql;
exception
when no_data_found then null;
end;
for cm in cmain loop
tname:=cm.NAME; ttype:=cm.type;
txt:='create or replace '||ttype||' '||tname|| ' is ';
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,txt);
open cdetail(tname);
fetch cdetail into txt;
loop
exit when cdetail%NOTFOUND;
lineno:=lineno+1;
insert into gensql(line,txt) values(lineno,txt);
fetch cdetail into txt;
end loop;
close cdetail;
end loop;
end;
/
spool c:\tables.txt
select txt from gensql order by line
/
select 'create or replace view '||view_name||' as ',text from user_views
/
select 'CREATE OR REPLACE TRIGGER '||TRIGGER_NAME||
decode(instr(trigger_type,'BEFORE'),1,' BEFORE ')||
decode(instr(trigger_type,'AFTER'),1,' AFTER ')
||TRIGGERING_EVENT|| ' ON ' ||TABLE_NAME||' '
|| REFERENCING_NAMES || ' '||
decode(instr(trigger_type,'EACH ROW'),7,' FOR EACH ROW',8,' FOR EACH ROW')
|| ' '||
DECODE(NVL(WHEN_CLAUSE,'X'),'X','',' WHEN '||WHEN_CLAUSE) trigger_head
,Trigger_body
from user_triggers
/
spool off
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