header image

Archive for 9548

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

under: 9548

 

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

under: 9548

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

StrAll – Helps to construct SQL Statement… Ultimately save the times

StrAll <TableName>

CREATE Procedure StrAll
(@obj varchar(255))
as
declare @fldname varchar(255), @len int, @datatype varchar(50), @nullable bit
declare @sqlUpdate varchar(4000)
declare @sqlDeclare varchar(4000)
declare @sqlInsertFld varchar(4000)
declare @sqlInsertVal varchar(4000)
declare @sqlWhere varchar(4000)
declare @sqlSelect varchar(4000)
declare @first int

DECLARE obj_cursor CURSOR FOR
 select name , length, ( select max(name) from systypes where xtype = c.xtype) datatype, isnullable
 from syscolumns c where id=object_id(@obj)
 order by colorder

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(@obj)
 and sysindexes.indid = 1 and sysindexkeys.indid=1

begin
 select @first = 1, @sqlUpdate = 'UPDATE ' + @obj + ' SET ', @sqlDeclare = 'declare ',
        @sqlInsertFld = ' INSERT INTO ' + @obj + ' (', @sqlInsertVal = ') VALUES (',  @sqlSelect = ' SELECT '
 OPEN obj_cursor
 FETCH NEXT FROM obj_cursor INTO @fldname, @len, @datatype, @nullable
 WHILE @@FETCH_STATUS = 0
 BEGIN
  if @first = 1
  begin
   select @first = 0
  end
  else
  begin
   set @sqlDeclare = @sqlDeclare + ', '
   set @sqlUpdate = @sqlUpdate + ','
   set @sqlInsertFld = @sqlInsertFld + ','
   set @sqlInsertVal = @sqlInsertVal + ','
   set @sqlSelect = @sqlSelect + ', '
  end
   set @sqlDeclare = @sqlDeclare +
'@' + @fldname + ' ' + @datatype + '(' + ltrim(rtrim(@len)) + ')'
   set @sqlInsertFld = @sqlInsertFld + @fldname
   set @sqlInsertVal = @sqlInsertVal +
'@' + @fldname
   set @sqlSelect = @sqlSelect + @fldname
   set @sqlUpdate = @sqlUpdate + ' ' + @fldname + '= @' + @fldname
  FETCH NEXT FROM obj_cursor INTO @fldname, @len, @datatype, @nullable
 END
 CLOSE obj_cursor
 DEALLOCATE obj_cursor
 set @first = 1
 set @sqlWhere = ' WHERE '
 OPEN obj_cursor_pk
 FETCH NEXT FROM obj_cursor_pk INTO @fldname
 WHILE @@FETCH_STATUS = 0
 BEGIN
  if @first = 1
  begin
   set @first = 0
  end
  else
  begin
   set @sqlWhere = @sqlWhere + ', '
  end
   set @sqlWhere = @sqlWhere + ' ' + @fldname
  FETCH NEXT FROM obj_cursor_pk INTO @fldname
 END
 CLOSE obj_cursor_pk
 DEALLOCATE obj_cursor_pk

 set @sqlInsertVal = @sqlInsertFld + @sqlInsertVal + ' )'
 set @sqlSelect = @sqlSelect + ' FROM ' + @obj
 select @sqlDeclare = Replace(@sqlDeclare,'int(4)','int')

 print ''
 print 'Select……..'
 print @sqlSelect
 print ''
 print 'Declare……..'
 print @sqlDeclare
 print ''
 print 'Insert……..'
 print @sqlInsertVal
 print ''
 print 'Update……..'
 print @sqlUpdate + @sqlWhere
 print ''
 print 'Delete……..'
 print 'DELETE FROM ' + @obj + ' ' + @sqlWhere

END
GO

under: 9548

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

Use Northwind
Go
create procedure des(@tablename varchar(100))
as
select name,(select max(name) from systypes where xtype=sc.xtype) datatype,
length,prec,scale,case isnullable when 1 then ‘Y’ when 0 then ‘N’ else ‘X’ end nullable
from syscolumns sc where id= ( select id from sysobjects where
name=@tablename)
GO
des Products


Output:


















































































name


datatype


length


prec


scale


nullable


ProductID


int


4


10


0


N


ProductName


sysname


80


40


NULL


N


SupplierID


int


4


10


0


Y


CategoryID


int


4


10


0


Y


QuantityPerUnit


sysname


40


20


NULL


Y


UnitPrice


money


8


19


4


Y


UnitsInStock


smallint


2


5


0


Y


UnitsOnOrder


smallint


2


5


0


Y


ReorderLevel


smallint


2


5


0


Y


Discontinued


bit


1


1


0


N



Cheers!!!

under: 9548

Categories