Abu's blog : SQL Generator

T-SQL : Auto generating data retrieval SP

abu — Wed, 27 Aug 2008 17:52:00 GMT

Auto generating data retrieval SP will be done by just a second...

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

CREATE procedure [dbo].[SelectSP]

( @tablename varchar(100))

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

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

Generating Auto generated SELECT SQL from Oracle Database

abu — Wed, 27 Aug 2008 12:03:00 GMT

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

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

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

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

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

Auto generated stored procedure by SP [T-SQL]

abu — Wed, 27 Aug 2008 05:18:00 GMT

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

-- InsertUpdateSP Member

create procedure [dbo].[InsertUpdateSP]

( @tablename varchar(100))

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

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 ')

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'

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

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]

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

)

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

All the DML statement and declare statement are generated by this SP - StrAll

abu — Fri, 20 Oct 2006 12:49:00 GMT

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

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

abu — Tue, 20 Sep 2005 01:01:00 GMT

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.

Describing the Table in Simple Format - Oracle guys are habituated to get the similer type output through 'desc' command

abu — Mon, 19 Sep 2005 16:15:00 GMT

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!!!