header image

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

Posted by: | October 20, 2006 | No Comment |

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