Creación automática de Storeds Procedure

A continuación se da un código T-SQL que genera los siguientes stored procedures de manera automática indicándole una Tabla y su Primary Key:
- Insert
- Update
- Delete
- Read (Select Unitario)
- Count
- List (Select)
- List Paginado

Código:

use empleo89
go

declare @TableName sysname
declare @PK varchar(1024)
declare @IdentityInsert int
declare @ColName sysname
declare @ColType sysname
declare @ColStatus tinyint
declare @DebugMode bit
declare @ColList nvarchar(4000)
declare @ValList nvarchar(4000)
declare @SQL1 nvarchar(1000)
declare @SQL2 nchar(10)
declare @SQL3 nchar(1000)

set @TableName = 'Candidate' -- ''
set @PK= 'IdUser' -- limit scope of inserts
set @DebugMode = 0 -- set to 1 if you only want a script

set @IdentityInsert = 0 -- set to 1 if you want to force IDENTITY_INSERT statements

set @ColList = ''
set @ValList = ''
set @SQL1 = 'select replace(''insert into ' + @TableName + ' ('
set @SQL2 = ') values ('
set @SQL3 = ')'', ''''''null'''''', ''null'') from ' + @TableName


declare csrColumns cursor local fast_forward for
select c.name,
case t.name when 'nvarchar' then t.name +'('+ cast(c.prec as varchar) +')'
else t.name
end
, c.status
from syscolumns c
inner join sysobjects o
on o.id = c.id
inner join systypes t
on t.xtype=c.xtype
where o.name = @TableName
and o.xtype in ('U')
and t.name <> 'sysname'
order by ColID

--print ''+ @TableName+''
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

print 'CREATE PROCEDURE '+ @TableName +'_Add ('
while @@fetch_status = 0
begin
set @ColList = ' @'+@ColName +' ' + @ColType

if @ColName = @PK SET @ColList = @ColList + ' OUTPUT'

fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @@fetch_status = 0 print @ColList + ','
else print @ColList
end

close csrColumns

print ')'
print 'as'
print 'begin'
set @ColList = ''
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

print 'Insert into ['+ @TableName +'] ('
while @@fetch_status = 0
begin
set @ColList = ' '+@ColName

if @ColName = @PK SET @ColList = ''
set @ValList = @ColName
fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @@fetch_status = 0 and @ValList <> @PK print @ColList + ','
else print @ColList
end

close csrColumns



print ') values ('

set @ColList = ''
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

while @@fetch_status = 0
begin
set @ColList = ' @'+@ColName

if @ColName = @PK SET @ColList = ''
set @ValList = @ColName
fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @@fetch_status = 0 and @ValList <> @PK print @ColList + ','
else print @ColList
end

print ')'
close csrColumns

print 'set @' + @PK + '=@@IDENTITY'
print 'end'
print 'go'


PRINT '------------------------------------------------------'

open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

print 'CREATE PROCEDURE '+ @TableName +'_Mod ('
while @@fetch_status = 0
begin
set @ColList = ' @'+@ColName +' ' + @ColType

fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @@fetch_status = 0 print @ColList + ','
else print @ColList
end

close csrColumns

print ')'
print 'as'
print 'begin'
set @ColList = ''
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

print 'Update ['+ @TableName +'] with (rowlock) set '
while @@fetch_status = 0
begin
set @ColList = ' '+@ColName + ' = @'+@ColName

if @ColName = @PK SET @ColList = ''

set @ValList = @ColName
fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @@fetch_status = 0 and @ValList <> @PK print @ColList + ','
else print @ColList
end

close csrColumns

print 'where ' + @PK + ' =@' +@PK
print 'end'
print 'go'

print '-------------------------------------------------------'

open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

print 'CREATE PROCEDURE '+ @TableName +'_Del ('
while @@fetch_status = 0
begin
SET @ColList = ''
if @ColName = @PK set @ColList = ' @'+@ColName +' ' + @ColType

fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @ColList<>'' print @ColList

end

close csrColumns

print ')'
print 'as'
print 'begin'
set @ColList = ''
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

print 'Delete from ['+ @TableName +'] with (rowlock) '
print 'where ' + @PK + ' =@' +@PK
print 'end'
print 'go'

close csrColumns
print '-------------------------------------------------------'


open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

print 'CREATE PROCEDURE '+ @TableName +'_Lst('
while @@fetch_status = 0
begin
if @ColType like 'nvarchar%' or @ColType like 'varchar%' set @ValList = ''''''
else set @ValList = '0'

if @ColType like '%time%'
begin
set @ColList = ' @'+@ColName +'From ' + @ColType + '=' + @ValList + ','
set @ColList = @ColList +' @'+@ColName +'To ' + @ColType + '=' + @ValList
end
else set @ColList = ' @'+@ColName +' ' + @ColType + '=' + @ValList




fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @@fetch_status = 0 print @ColList + ','
else print @ColList
end

close csrColumns

print ')'
print 'as'
print 'begin'

print 'DECLARE @strSQL nvarchar(4000)'

print 'SET @strSQL = '''
print 'SELECT'

open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

while @@fetch_status = 0
begin
set @ColList = ' '+@ColName

set @ValList = @ColName
fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @@fetch_status = 0 print @ColList + ','
else print @ColList
end

close csrColumns

print 'from ['+ @TableName +'] WITH (NOLOCK)'
print 'WHERE '''
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

declare @count int
set @count =0
while @@fetch_status = 0
begin
if @ColType like 'nvarchar%' or @ColType like 'varchar%' set @ValList = ''''''
else set @ValList = '0'

if @ColType like '%time%' set @ColList = 'if @'+@ColName +'From <> ' + @ValList + ' and @' +@ColName +'To <> ' + @ValList
else set @ColList = 'if @'+@ColName +' <> ' + @ValList

print @ColList

if @count =0 set @ValList = ' SET @strSQL = @strSQL + '' '
else set @ValList = ' SET @strSQL = @strSQL + '' AND '

if not (@ColType like '%time%') print @ValList + @ColName + '='' + CAST(@' + @ColName + ' AS varchar(50))'
else print @ValList + @ColName + ' between ''+ CAST(@' + @ColName + 'From AS varchar(50)) + ''AND CAST(@' + @ColName + 'To AS varchar(50))'''

fetch next from csrColumns into @ColName, @ColType, @ColStatus
SET @count =1
end

close csrColumns

print 'EXECUTE sp_executeSQL @strSQL'


print 'end'
print 'go'



print '-------------------------------------------------------'



open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

print 'CREATE PROCEDURE '+ @TableName +'_Cnt('
while @@fetch_status = 0
begin
if @ColType like 'nvarchar%' or @ColType like 'varchar%' set @ValList = ''''''
else set @ValList = '0'

if @ColType like '%time%'
begin
set @ColList = ' @'+@ColName +'From ' + @ColType + '=' + @ValList + ','
set @ColList = @ColList +' @'+@ColName +'To ' + @ColType + '=' + @ValList
end
else set @ColList = ' @'+@ColName +' ' + @ColType + '=' + @ValList




fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @@fetch_status = 0 print @ColList + ','
else print @ColList
end

close csrColumns

print ')'
print 'as'
print 'begin'

print 'DECLARE @strSQL nvarchar(4000)'
print 'DECLARE @Where nvarchar(2000)'

print 'SET @strSQL = '''
print 'SELECT Count(*) from ['+ @TableName +'] WITH (NOLOCK)'''
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

set @count =0
while @@fetch_status = 0
begin
if @ColType like 'nvarchar%' or @ColType like 'varchar%' set @ValList = ''''''
else set @ValList = '0'

if @ColType like '%time%' set @ColList = 'if @'+@ColName +'From <> ' + @ValList + ' and @' +@ColName +'To <> ' + @ValList
else set @ColList = 'if @'+@ColName +' <> ' + @ValList

print @ColList

if @count =0 set @ValList = ' SET @Where = @Where + '' '
else set @ValList = ' SET @Where = @Where + '' AND '

if not (@ColType like '%time%') print @ValList + @ColName + '='' + CAST(@' + @ColName + ' AS varchar(50))'
else print @ValList + @ColName + ' between ''+ CAST(@' + @ColName + 'From AS varchar(50)) + ''AND CAST(@' + @ColName + 'To AS varchar(50))'''

fetch next from csrColumns into @ColName, @ColType, @ColStatus
SET @count =1
end

close csrColumns

print 'if @Where<>'''''
print ' set @strSQL = @strSQL + '' WHERE '' + @Where'
print 'EXECUTE sp_executeSQL @strSQL'


print 'end'
print 'go'

print '------------------------------------------------------------'
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

print 'CREATE PROCEDURE '+ @TableName +'_Get('
while @@fetch_status = 0
begin

if @ColName = @PK print '@'+@ColName + ' ' + @ColType

fetch next from csrColumns into @ColName, @ColType, @ColStatus

end

close csrColumns

print ')'
print 'as'
print 'begin'

print 'DECLARE @strSQL nvarchar(4000)'

print 'SET @strSQL = '''
print 'SELECT'

open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

while @@fetch_status = 0
begin
set @ColList = ' '+@ColName

set @ValList = @ColName
fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @@fetch_status = 0 print @ColList + ','
else print @ColList
end

close csrColumns

print 'from ['+ @TableName +'] WITH (NOLOCK)'
print 'WHERE '
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

print @PK + '='' + CAST(@' + @PK + ' AS varchar(50))'

print 'EXECUTE sp_executeSQL @strSQL'


print 'end'
print 'go'

print '-------------------------------------------------------'

close csrColumns
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

print 'CREATE PROCEDURE '+ @TableName +'_LstPager('
print ' @PageNumber int =0,'
print ' @PageSize int =0,'
print ' @Order nvarchar(100) = '''','
while @@fetch_status = 0
begin


if @ColType like 'nvarchar%' or @ColType like 'varchar%' set @ValList = ''''''
else set @ValList = '0'

if @ColType like '%time%'
begin
set @ColList = ' @'+@ColName +'From ' + @ColType + '=''1900/01/01'','
set @ColList = @ColList +' @'+@ColName +'To ' + @ColType + '=''1900/01/01'''
end
else set @ColList = ' @'+@ColName +' ' + @ColType + '=' + @ValList




fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @@fetch_status = 0 print @ColList + ','
else print @ColList
end

close csrColumns

print ')'
print 'as'
print 'begin'


print 'DECLARE @SQL nvarchar(2000), '
print ' @Where nvarchar(1000),'
print ' @OrderBy nvarchar(100),'
print ' @FieldsTmp nvarchar(1000),'
print ' @Fields nvarchar(1000),'
print ' @TextQuery nvarchar(500)'


-- ORDER
print 'SET @OrderBy = '' ORDER BY A.'' + @Order'

-- FIELDS
print 'SET @FieldsTmp = '''
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

while @@fetch_status = 0
begin
set @ColList = ' TMP_RESULT.'+@ColName

fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @@fetch_status = 0 print @ColList + ','
else print @ColList
end
print ''''

print 'SET @Fields = '''
close csrColumns
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

while @@fetch_status = 0
begin
set @ColList = ' A.'+@ColName

fetch next from csrColumns into @ColName, @ColType, @ColStatus
if @@fetch_status = 0 print @ColList + ','
else print @ColList
end
print ''''

-- WHERE
print 'SET @Where = '''''
close csrColumns
open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

set @count =0
while @@fetch_status = 0
begin
if @ColType like 'nvarchar%' or @ColType like 'varchar%' set @ValList = ''''''
else if @ColType like '%time%' set @ValList = '''1900/01/01'''
else set @ValList = '0'

if @ColType like '%time%' set @ColList = 'if @'+@ColName +'From <> ' + @ValList + ' and @' +@ColName +'To <> ' + @ValList
else set @ColList = 'if @'+@ColName +' <> ' + @ValList

print @ColList

if @count =0 set @ValList = ' SET @Where = @Where + '' '
else set @ValList = ' SET @Where = @Where + '' AND '

if not (@ColType like '%time%') print @ValList + @ColName + '='' + CAST(@' + @ColName + ' AS varchar(50))'
else print @ValList + @ColName + ' between ''+ CAST(@' + @ColName + 'From AS varchar(50)) + ''AND CAST(@' + @ColName + 'To AS varchar(50))'''

fetch next from csrColumns into @ColName, @ColType, @ColStatus
SET @count =1
end

close csrColumns

print 'if @Where <> '''''
print 'BEGIN'
print ' SET @Where = ''WHERE ''+ @Where'
print 'END'

-- SELECT
print 'SET @SQL = ''SELECT'' + @FieldsTmp + ''FROM ('
print ' SELECT TOP '' + CAST((@PageSize * (@PageNumber)) as nvarchar) + @Fields + '',ROW_NUMBER() OVER('' + @OrderBy + '') AS RowNumber '
print ' FROM ['+ @TableName +'] A WITH(NOLOCK) '''
print 'SET @SQL = @SQL + @Where + @OrderBy'
print 'SET @SQL = @SQL + '') AS TMP_RESULT '''
print 'SET @SQL = @SQL + '' WHERE RowNumber > '' + CAST((@PageSize * (@PageNumber-1)) as nvarchar) + '' AND RowNumber <= '' + CAST((@PageSize * (@PageNumber)) as nvarchar)' print '--PRINT @SQL' print 'exec sp_executesql @SQL' -- Ejecutamos el SQL que devuelve el contador total de los anúncios print 'EXECUTE ' + @TableName +'_Cnt' open csrColumns fetch next from csrColumns into @ColName, @ColType, @ColStatus while @@fetch_status = 0 begin if @ColType like '%time%' begin set @ColList = ' @'+@ColName +'From ,' set @ColList = @ColList +' @'+@ColName +'To ' end else set @ColList = ' @'+@ColName fetch next from csrColumns into @ColName, @ColType, @ColStatus if @@fetch_status = 0 print @ColList + ',' else print @ColList end print 'end' print 'go' print '-- Stored creado por Marcos Palacios, por favor, link a este artículo desde su web--' deallocate csrColumns

Split con Sql Server

sStored que permite realizar un split, devolviendo la posición deseada, emula una función que antiguamente se instalaba en Sql Server 2000 y tenia los mismos parámetros.

Código:
create procedure split
(
@Original varchar(50),
@Regex varchar(5),
@iCount int,
@Output varchar(50) OUTPUT
)
AS
BEGIN


declare @xml xml
set @xml = cast('' + replace(@Original, @Regex,'') + '' as xml)

select @Output = @xml.query(N'//word[sql:

variable("@iCount")]').value('.','varchar(50)')

END

Uso:
declare @Word1 varchar(50)
exec split 'aaaa bbbb cccc', ' ', 2, @Word1 OUTPUT
select @Word1