generate save, delete, get, and list objects for a table
exec usp_DBA_createSPs 'MyTable', 'tmp', 'col1, col2'
creates
proc_tmp_MyTable_save - does update if row exists (based on key) and insert if none. returns ID of row updated/inserted
proc_tmp_MyTable_delete delete (based on key) with insert to deleted table
fTbl_tmp_MyTable_get - no paging, with "where" templates for each parameter type
fTbl_tmp_MyTable_list - provide paging
I have written this script while back. I have used one of the scripts I have found online.Sorry I do not remember where I've got script originally
It was changed by adding more logic to SP, adding functions, as well as fixing size limitation and other bugs.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****************************************************
generate procedure functions templates as
select @save = 'proc_' + @Prefix + '_' + @tablename + +'_save'
,@del = 'proc_' + @Prefix + '_' + @tablename +'_delete'
,@get = 'fTbl_' + @Prefix + '_' + @tablename +'_get'
,@list = 'fTbl_' + @Prefix + '_' + @tablename +'_list'
for example
exec usp_DBA_createSPs 'MyTable', 'tmp', 'col1, col2'
creates
proc_tmp_MyTable_save - does update if row exists (based on key) and insert if none. returns ID of row updated/inserted
proc_tmp_MyTable_delete
fTbl_tmp_MyTable_get - no paging, with "where" templates for each parameter type
fTbl_tmp_MyTable_list - provide paging
****************************************************/ALTER procedure usp_DBA_createSPs (
@tablename varchar(50),
@Prefix varchar(40) = 'tmp'
,@id_cols varchar(256) = null -- csv list of columns used as natural key for table. By default will use identity column or unique key.
)
as
declare @save varchar(128), @get varchar(128), @del varchar(128), @list varchar(128)
select @save = 'proc_' + @Prefix + '_' + @tablename + +'_save'
,@del = 'proc_' + @Prefix + '_' + @tablename +'_delete'
,@get = 'fTbl_' + @Prefix + '_' + @tablename +'_get'
,@list = 'fTbl_' + @Prefix + '_' + @tablename +'_list'
declare @dropproc varchar(255)
if exists (select * from sysobjects where id = object_id(@save))
begin
select @dropproc = 'drop procedure ' + @save
exec (@dropproc)
end
if exists (select * from sysobjects where id = object_id(@del))
begin
select @dropproc = 'drop procedure ' + @del
exec (@dropproc)
end
if exists (select * from sysobjects where id = object_id(@get) )
begin
select @dropproc = 'drop function dbo.' + @get
exec (@dropproc)
end
if exists (select * from sysobjects where id = object_id(@list) )
begin
select @dropproc = 'drop function dbo.' + @list
exec (@dropproc)
end
-- begin support for value_listing
declare @vlcolumns varchar(2000)
declare @vlcolumns1 varchar(2000)
-- end supoprt for value_listing
-- begin support for set_listing
declare @slcolumns varchar(2000)
-- end support for set_listing
-- support for create procedures
declare @insert varchar(8000)
declare @update varchar(8000)
declare @select varchar(8000)
declare @delete varchar(8000)
declare @head varchar(8000)
declare @mid varchar(8000)
declare @end varchar(8000)
declare @where varchar(8000)
declare @orderBy varchar(8000)
-- end support for create procedures
parm_listing:
/*---------------------------------------------------------------------------*//* parm_listing begin
-- param swith data type *//*---------------------------------------------------------------------------*/ declare @plparm varchar(2000), @plparm1 varchar(2000), @plparmDef varchar(2000)
declare @tblretparam varchar(2000), @tblretparam1 varchar(2000)
select @plparm1 = '', -- all params in one line as variable with type
@plparmDef = char(9), -- all params in column as vars with type and defaults
@plparm = char(9), -- all params in column as variable with type
@tblretparam1 = '', -- all params in one line as name with type
@tblretparam = char(9) -- all params in one line as name with type
select @plparm1 = @plparm1 +
char(64) + column_name +
space(1) + data_type +
case
when character_maximum_length is null then space(1)
when character_maximum_length is not null and character_maximum_length > 8000 then space(1)
else char(40) + convert(varchar(8),character_maximum_length) + char(41)
end
+ ',',
@plparmDef = @plparmDef +
char(64) + column_name +
char(9) + data_type +
case
when character_maximum_length is null then space(1)
when character_maximum_length is not null and character_maximum_length > 8000 then space(1)
else char(40) + convert(varchar(8),character_maximum_length) + char(41)
end + ' = null' +
char(10) + char(9) + ',',
@plparm = @plparm +
char(64) + column_name +
char(9) + data_type +
case
when character_maximum_length is null then space(1)
when character_maximum_length is not null and character_maximum_length > 8000 then space(1)
else char(40) + convert(varchar(8),character_maximum_length) + char(41)
end +
char(10) + char(9) + ',',
@tblretparam = @tblretparam +
column_name +
char(9) + data_type +
case
when character_maximum_length is null then space(1)
when character_maximum_length is not null and character_maximum_length > 8000 then space(1)
else char(40) + convert(varchar(8),character_maximum_length) + char(41)
end
+ char(10) +char(9) + ',',
@tblretparam1 = @tblretparam1 +
column_name +
space(1) + data_type +
case
when character_maximum_length is null then space(1)
when character_maximum_length is not null and character_maximum_length > 8000 then space(1)
else char(40) + convert(varchar(8),character_maximum_length) + char(41)
end
+ ','
from information_schema.columns
where table_name = @tablename
order by ordinal_position
--strip off last comma
select @plparm = convert(varchar(2000),substring(@plparm, 1, len(@plparm)-3) )
select @plparm1 = convert(varchar(2000),substring(@plparm1, 1, len(@plparm1)-1) )
select @plparmDef = convert(varchar(2000),substring(@plparmDef, 1, len(@plparmDef)-1))
select @tblretparam = convert(varchar(2000),substring(@tblretparam, 1, len(@tblretparam)-1))
select @tblretparam1 = convert(varchar(2000),substring(@tblretparam1, 1, len(@tblretparam1)-1))
/*---------------------------------------------------------------------------*//* parm_listing end *//*---------------------------------------------------------------------------*/
column_listing:
/*---------------------------------------------------------------------------*//* column_listing begin *//*---------------------------------------------------------------------------*/declare @clcolumns varchar(2000), @clcolumns1 varchar(2000), @clcolumnsT varchar(2000)
select @clcolumns = '', @clcolumns1 = '', @clcolumnsT=''
select
@clcolumns = @clcolumns +char(10) + char(9) + column_name + ','
,@clcolumns1 = @clcolumns1 + column_name + ', '
,@clcolumnsT=@clcolumnsT+'t.'+column_name + ', '
from information_schema.columns
where table_name = @tablename
order by ordinal_position
--strip off last comma
select @clcolumns = convert(varchar(2000),substring(@clcolumns, 1, len(@clcolumns)-1) )
select @clcolumns1 = convert(varchar(2000),substring(@clcolumns1, 1, len(@clcolumns1)-1) )
select @clcolumnsT = convert(varchar(2000),substring(@clcolumnsT, 1, len(@clcolumnsT)-1) )
/*---------------------------------------------------------------------------*//* column_listing end *//*---------------------------------------------------------------------------*/
value_listing:
/*---------------------------------------------------------------------------*//* value_listing begin *//*---------------------------------------------------------------------------*/select @vlcolumns = '', @vlcolumns1 = ''
select @vlcolumns = @vlcolumns +
char(10) + char(9) + '@' + column_name +
', ',
@vlcolumns1 = @vlcolumns1 +
'@' + column_name +
', '
from information_schema.columns
where table_name = @tablename
order by ordinal_position
--strip off last comma
select @vlcolumns = convert(varchar(2000),substring(@vlcolumns, 1, len(@vlcolumns)-1) )
select @vlcolumns1 = convert(varchar(2000),substring(@vlcolumns1, 1, len(@vlcolumns1)-1))
/*---------------------------------------------------------------------------*//* value_listing end *//*---------------------------------------------------------------------------*/
key_listing:
/*---------------------------------------------------------------------------*//* key_listing begin *//*---------------------------------------------------------------------------*/declare @klcolumns varchar(2000), @klcolumns1 varchar(2000), @keyColTbl varchar(300), @keyColSelect varchar(300), @keyColJoin varchar(600)
select @klcolumns = '',@klcolumns1 = '' , @keyColTbl = '', @keyColSelect ='', @keyColJoin=''
if (@id_cols is null)
begin
select
@klcolumns = @klcolumns +keys.column_name + ' = @' + keys.column_name + ' and ' +char(10),
@klcolumns1 = @klcolumns1 +keys.column_name + ' = @' + keys.column_name + ' and '
,@keyColJoin = @keyColJoin + 'i.'+cols.column_name+'=t.'+cols.column_name + ' AND '
,@keyColTbl = @keyColTbl +cols.column_name + char(9) + cols.data_type +
case when cols.character_maximum_length is null then space(1)
when cols.character_maximum_length is not null and cols.character_maximum_length > 8000 then space(1)
else char(40) + convert(varchar(8),cols.character_maximum_length) + char(41)
end
+ char(10) +char(9) + ','
,@keyColSelect = @keyColSelect + cols.column_name + ','
from information_schema.columns as cols
join information_schema.key_column_usage as keys
on cols.table_name = keys.table_name
and cols.column_name = keys.column_name
join information_schema.table_constraints as consts
on keys.constraint_name = consts.constraint_name
and consts.constraint_type = 'primary key'
where keys.table_name = @tablename
end
else -- use @id_cols
begin
select @klcolumns = @klcolumns + item + ' = @' + item + ' and ' + char(10)
,@klcolumns1 = @klcolumns1 + item + ' = @' + item + ' and '
,@keyColJoin = @keyColJoin +'i.'+item + ' = t.' + item + ' and '
,@keyColTbl = @keyColTbl + item + char(9)+ 'int'+ char(10) +char(9) + ','
,@keyColSelect = @keyColSelect + item + ','
from DBCommon.dbo.fn_parseString(@id_cols, ',')
end
--strip off last 'and' or ','
if len(ltrim(rtrim(@klcolumns)) ) > 1 begin
select
@klcolumns = convert(varchar(2000), substring(@klcolumns, 1, len(@klcolumns)-5) )
,@klcolumns1 = convert(varchar(2000),substring(@klcolumns1, 1, len(@klcolumns1)-4) )
,@keyColJoin = convert(varchar(2000),substring(@keyColJoin, 1, len(@keyColJoin)-4) )
,@keyColTbl = convert(varchar(2000),substring(@keyColTbl, 1, len(@keyColTbl)-1) )
,@keyColSelect = convert(varchar(2000),substring(@keyColSelect, 1, len(@keyColSelect)-1))
end
else begin
print 'No key columns defined in table. Define key columns or call sp with parameter @id_cols=''key1, key2, ..'' to define key columns by hand'
return
end
/*---------------------------------------------------------------------------*//* key_listing end *//*---------------------------------------------------------------------------*/
set_listing:
/*---------------------------------------------------------------------------*//* set_listing begin *//*---------------------------------------------------------------------------*/set @slcolumns = 'set '
select
@slcolumns =@slcolumns +char(9) +column_name +char(9)+'= isnull(@' +column_name+','+column_name+')' + ',' + char(10)
from information_schema.columns
where table_name = @tablename
order by ordinal_position
select @slcolumns = convert(varchar(2000),substring(@slcolumns, 1, len(@slcolumns)-2) )--strip off last comma
--/*---------------------------------------------------------------------------*/--/* set_listing end */--/*---------------------------------------------------------------------------*/
--/*---------------------------------------------------------------------------*/--/* empty listings start */--/*---------------------------------------------------------------------------*/declare @valEmpty varchar(2000)
set @valEmpty=''
select @valEmpty=@valEmpty
+char(10)+char(9)+qts+null_subst_val+qts+','
from information_schema.columns c left outer join dbcommon..dbtypes t on c.data_type = t.type_nm
where table_name = @tablename
select @valEmpty = convert(varchar(2000),substring(@valEmpty, 1, len(@valEmpty)-1) )
--/*---------------------------------------------------------------------------*/--/* empty listings end */--/*---------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------*//* update begin . Update is part of save (insert) sp *//*---------------------------------------------------------------------------*/select @update = char(10)
+ 'update ' +char(9)+@tablename+ space(1)
+ char(10) +@slcolumns + space(1)
+ char(10) + 'where '
+ char(10) + @klcolumns1
+ char(10)
--print @update
/*---------------------------------------------------------------------------*//* update end *//*---------------------------------------------------------------------------*/
print 'Start Creation ' + @save
--/*---------------------------------------------------------------------------*/--/* insert begin */--/*---------------------------------------------------------------------------*/select @head =
'/*****************************************************************'
+ char(10) + '** $Header:$'
+ char(10) + '**'
+ char(10) + '** Name : ' + @save +
+ char(10) + '** Created By : ' + system_user
+ char(10) + '** Created on : ' + convert(varchar(20), getdate(), 101)
+ char(10) + '** Desc : updates row or creates new if not exists in ' + @tablename + ' table.'
+ char(10) + '** Dev Notes : '
+ char(10) + '** called by : java class '
+ char(10) + '** depend on : none'
+ char(10) + '** Param : ' + @klcolumns1 + ' primary keys'
+ char(10) + '** Returns : id of row saved/inserted'
+ char(10) + '**'
+ char(10) + '** (c)All rights reserved, Your Company Name'
+ char(10) + '*******************************************************************************'
+ char(10) + '** Change History'
+ char(10) + '** ' + convert(varchar(20), getdate(), 101) +char(9) + 'Edit template as needed. '
+ char(10) + '*******************************************************************************'
+ char(10) + '** $Log$'
+ char(10) + '*******************************************************************************/'
+ char(10) + 'create procedure ' + @save + ' ('
+ char(10) +@plparmDef +
+ ',@returnData bit = 1 -- skip last select statement'
+ char(10) +')'
+ char(10) +'as'
+ char(10)
set @insert =
+ char(10) +'set nocount on'
+ char(10) + 'declare @rowID int'
+ char(10) +' if exists ( select 1 from ' + @tablename + ' where ' + @klcolumns1 + ') '
+ char(10) +'BEGIN'
+ char(10)+char(9) + @update
+ char(10)+char(9) +'--set @rowID = @identity_col -- PUT PROER COLUMN NAME HERE'
+ char(10) +'END'
+ char(10) +'else'
+ char(10) +'BEGIN'
+ char(10) +char(9) + 'insert ' + @tablename + ' ('
+ char(10) +char(9) + @clcolumns1
+ char(10) +char(9)+')'
+ char(10) +char(9) + 'values (' + @vlcolumns
+ char(10) +char(9)+')'
+ char(10)+char(9) +'set @rowID = SCOPE_IDENTITY()'
+ char(10) +'END'
+ char(10)
+ char(10) + '-- return updated row id'
+ char(10) + 'if (@returnData = 1)'
+ char(10) + char(9) + 'select @rowID row_id '
+ char(10) +
+ char(10) + 'return @rowID'
+ char(10)
+ char(10) +'set nocount off'
+char(10)
--print @head
--print @insert
declare @test varchar(7000)
set @test = ' '
select @test = @test +
''' ,@'+column_name+t.compare+''''+rtrim(qts)+ltrim(qts)
+' + str(isnull('+column_name+','+qts+null_subst_val+qts+')) +'
+rtrim(qts)+rtrim(ltrim(qts))
from information_schema.columns c left outer join dbcommon..dbtypes t on c.data_type = t.type_nm
where table_name =@tablename
select @test = char(10) + ' select top 1 ' + @test + ' from ' + @tablename
select @test =
+char(10) + '/*==================== TEST ===================='
+char(10) + '-- select top 10 * from ' + @tablename
+char(10) + '-- exec dbo.' + @save + ' ' + @vlcolumns1
+char(10) + @test
+char(10) + '==================== END ======================*/'
print str(len(@head))
print str(len(@insert))
print str(len(@test))
--print @head + @insert + @test
exec (@head + @insert + @test)
/*---------------------------------------------------------------------------*//* insert end *//*---------------------------------------------------------------------------*/print 'Finished ' + @save
print 'Start Creation ' + @del
/*---------------------------------------------------------------------------*//* delete begin *//*---------------------------------------------------------------------------*/select @head =
'/*****************************************************************'
+ char(10) + '** $Header:$'
+ char(10) + '**'
+ char(10) + '** Name : ' + @del +
+ char(10) + '** Created By : ' + system_user
+ char(10) + '** Created on : ' + convert(varchar(20), getdate(), 101)
+ char(10) + '** Desc : deletes data from ' + @tablename
+ char(10) + '** Dev Notes : '
+ char(10) + '** called by : java class '
+ char(10) + '** depend on : none'
+ char(10) + '** Param : ' + @klcolumns1 + ' primary keys'
+ char(10) + '** Returns : # of rows deleted'
+ char(10) + '**'
+ char(10) + '** (c)All rights reserved, Your Company Name'
+ char(10) + '*******************************************************************************'
+ char(10) + '** Change History'
+ char(10) + '** ' + convert(varchar(20), getdate(), 101) +char(9) + 'Edit template as needed. '
+ char(10) + '*******************************************************************************'
+ char(10) + '** $Log$'
+ char(10) + '*******************************************************************************/'
+ char(10) + 'create procedure ' + @del + ' ('
+ char(10) +@plparmDef
+ char(10) +' )'
+ char(10) + 'as'
+ char(10) + 'set nocount on'
set @delete =
+ char(10) + '-- Move data to delted table'
+ char(10) + char(9) + 'insert into ' + @tablename +'_del ('
+ char(10) +char(9) + char(9) + @clcolumns1
+ char(10) +char(9) + ')'
+ char(10) +char(9) + 'select'
+ char(10) +char(9) + char(9) + @clcolumns1
+ char(10) +char(9) + 'from' + char(9) + @tablename
+ char(10) +char(9) + 'where' + char(9) + @klcolumns1
+ char(10)
+ char(10)
+ char(10) +'delete' + char(9) + @tablename
+ char(10) +char(9) + 'where '
+ char(10) +char(9) + @klcolumns1
+ char(10) +'return @@rowcount'
+ char(10) +'set nocount off'
+ char(10)
+ char(10) + '/*==================== TEST ===================='
+ char(10) + '-- select top 10 * from ' + @tablename
+ char(10) + '-- exec dbo.' + @del + ' ' + @vlcolumns1
+ char(10) + '==================== END ======================*/'
exec (@head + @delete)
-- print @head
--print @delete
/*---------------------------------------------------------------------------*//* delete end *//*---------------------------------------------------------------------------*/print 'Finished ' + @del
print 'Start Creation ' + @list
/*---------------------------------------------------------------------------*//* list begin *//*---------------------------------------------------------------------------*/-- extra params for list function
declare @listPar varchar(512), @idxCol varchar(512), @listSel varchar(128), @idxSel varchar(128), @idxSel_val varchar(128)
set @listPar =
+char(10)+char(9)+',@page int = 1 -- page to show'
+char(10)+char(9)+',@pageSize int = 20 -- number of records per page'
+char(10)+char(9)+',@orderBy varchar(10) = ''name'' -- pay, status, expiration, type, state, etc '
set @idxCol =
+char(10)+char(9)+',total_number int -- total number of rows in result'
+char(10)+char(9)+',idx int identity(1,1) -- index column'
set @listSel =
+char(10)+char(9)+',@page,@pageSize,@orderBy'
set @idxSel = ',total_number '
set @idxSel_val = ',@total_number '
select @head =
'/*****************************************************************'
+ char(10) + '** $Header:$'
+ char(10) + '**'
+ char(10) + '** Name : ' + @list +
+ char(10) + '** Created By : ' + system_user
+ char(10) + '** Created on : ' + convert(varchar(20), getdate(), 101)
+ char(10) + '** Desc : gets data from ' + @tablename + ' table with paging and custom sorting.'
+ char(10) + '** Dev Notes : Code given to join criteria with "AND". See commenetd lines for "FIND" and "OR" type joins'
+ char(10) + '** : 2 tables are used: (1) table for search with only id columns and search column, (2) - table for indexing'
+ char(10) + '** called by : java class '
+ char(10) + '** depend on : none'
+ char(10) + '** Param : ' + @klcolumns1 + 'primary keys'
+ char(10) + '** Returns : table - data macthed criteria given.'
+ char(10) + '**'
+ char(10) + '** (c)All rights reserved, Your Company Name'
+ char(10) + '*******************************************************************************'
+ char(10) + '** Change History'
+ char(10) + '** ' + convert(varchar(20), getdate(), 101) +char(9) + 'Edit template as needed. '
+ char(10) + '*******************************************************************************'
+ char(10) + '** $Log$'
+ char(10) + '*******************************************************************************/'
,@mid=
+char(10) + 'create function ' + @list + ' ('
+char(10) + @plparm +@listPar
+char(10) +' )'
+char(10) + ' RETURNS @tblret TABLE ('
+ @tblretparam
+ @idxCol
+char(10) +') as '
+char(10)+ 'BEGIN'
+char(10)
+char(10)+'-- create indexed table for paging'
+char(10)+'declare @total_number int'
+char(10)+'declare @tblID table (idx int identity(1,1), '
+char(10)+char(9)+@keyColTbl + ')'
+char(10)
+char(10)+'--======================================'
+char(10)+'-- Template code for flexible "where" and "order by" for each data type present in table. Edit code as needed'
+char(10)+'--======================================'
, @select='-- all search parameters need to be set to null if empty'
select @select = @select
+char(10)+'if (ltrim(rtrim(convert(varchar(20),@'+column_name+'))) = '+qts+null_subst_val+qts+') '
+char(10)+char(9)+'set @'+column_name+' = null'
-- get columns one of each type as template
from ( select convert(varchar(128),min(column_name)) column_name, min(ordinal_position) ordinal_position, convert(varchar(128),data_type) data_type,
default_val, empty_val, null_subst_val, qts
from information_schema.columns c left outer join dbcommon..dbtypes t on c.data_type = t.type_nm
where table_name = @tablename
group by data_type, default_val,empty_val, null_subst_val, qts ) t
select @select = @select
+char(10)
+char(10)+'insert into @tblID (' + @keyColSelect+ ')'
+char(10)+char(9)+'SELECT '+@keyColSelect
+char(10)+char(9)+'FROM '+@tablename
-- create select with search and order by
declare @sqlWhere varchar(2000), @sqlOrder varchar(2000)
select @sqlWhere =+char(10)+char(9)+'WHERE ',
@sqlOrder=char(10)+char(9)+' ORDER BY '
-- construst where and order
select @sqlWhere=@sqlWhere+
+char(10)+char(9)+'isnull('+column_name+','+qts+null_subst_val+qts+') '+t.compare+' COALESCE(@'+column_name+', isnull('+column_name+','+qts+null_subst_val+qts+')) --' + data_type
+char(10)+char(9)+'AND'
,@sqlOrder = @sqlOrder
+char(10)+char(9)+'case when @orderBy = '''+column_name+''' then '+column_name+' end,'
-- get columns one of each type as template
from ( select convert(varchar(128),min(column_name)) column_name, min(ordinal_position) ordinal_position, convert(varchar(128),data_type) data_type,
default_val, empty_val, null_subst_val, qts, compare
from information_schema.columns c left outer join dbcommon..dbtypes t on c.data_type = t.type_nm
where table_name = @tablename
group by data_type, default_val,empty_val, null_subst_val, qts,compare ) t
-- trim 'and' and ','
if len(ltrim(rtrim(@sqlWhere)) ) > 1 begin
select
@sqlWhere = substring(@sqlWhere, 1, len(@sqlWhere)-6) --strip off last 'and'
,@sqlOrder = substring(@sqlOrder, 1, len(@sqlOrder)-1)
end
declare @selPaged varchar(8000)
--paged results
set @selPaged =
+char(10)+char(9)+'SELECT @total_number = @@rowcount '
+char(10)
+char(10)+char(9)+'if (isnull(@page,0) <= 0)'
+char(10)+char(9)+char(9)+'SELECT @page = 1'
+char(10)+char(9)+char(9)+char(9)+', @pageSize = @total_number + 1'
+char(10)
+char(10)+char(9)+'-- summary row'
+char(10)+char(9)+'INSERT INTO @tblret('
+char(10)+char(9) +char(9)+ @clcolumns1
+char(10)+char(9) +char(9)+ @idxSel
+char(10)+char(9)+')'
+char(10)+char(9)+'SELECT'
+char(10)+char(9) +char(9)+ @valEmpty
+char(10)+char(9) +char(9)+ @idxSel_val
+char(10)+char(9)+'INSERT INTO @tblret('
+char(10)+char(9) +char(9)+ @clcolumns1
+char(10)+char(9) +char(9)+ @idxSel
+char(10)+char(9)+')'
+char(10)+char(9)+'SELECT'
+char(10)+char(9) +char(9)+ @clcolumnsT
+char(10)+char(9) +char(9)+ @idxSel_val
+char(10)+char(9)+char(9)+'FROM @tblID i inner join '+@tablename+' t on '+@keyColJoin
+char(10)+char(9)+char(9)+'WHERE idx between ((@page - 1)*@pageSize + 1) and (@page * @pageSize)'
+char(10)+char(9)+char(9)+' ORDER BY idx'
set @end = char(10) + 'RETURN'
+char(10) + 'END'
+char(10) + '/* ==================== TEST ===================='
+char(10) + '-- select top 10 * from ' + @tablename
+char(10) + '-- select * from dbo.' + @list + '(' + @vlcolumns1 + ')'
+char(10) + '==================== END ======================*/'
--print isnull(@head,'')+isnull(@mid,'')+isnull(@select,'')+isnull(@sqlWhere,'') + isnull(@sqlOrder,'')+isnull(@selPaged,'')+isnull(@end,'')
exec (@head+@mid+@select+@sqlWhere + @sqlOrder+@selPaged+@end)
--print @mid
--print @select
--print @sqlWhere
--print @sqlOrder
--print @selPaged
--print @end
print len(@select)
print len(@sqlWhere)
print len(@sqlOrder)
print len(@selPaged)
/*---------------------------------------------------------------------------*//* list end *//*---------------------------------------------------------------------------*/print 'Finished ' + @list
print 'Start Creation ' + @get
/*---------------------------------------------------------------------------*//* select begin *//*---------------------------------------------------------------------------*/select @head =
'/*****************************************************************'
+ char(10) + '** $Header:$'
+ char(10) + '**'
+ char(10) + '** Name : ' + @get +
+ char(10) + '** Created By : ' + system_user
+ char(10) + '** Created on : ' + convert(varchar(20), getdate(), 101)
+ char(10) + '** Desc : gets data from ' + @tablename + ' table. Only few rows are returned. No paging or custom ordering. See _list finctions for paged results'
+ char(10) + '** Dev Notes : Code given to join criteria with "AND". See commenetd lines for "FIND" mode and "OR" type joins'
+ char(10) + '** called by : java class '
+ char(10) + '** depend on : none'
+ char(10) + '** Param : ' + @klcolumns1 + 'primary keys'
+ char(10) + '** Returns : table - data macthed criteria given.'
+ char(10) + '**'
+ char(10) + '** (c)All rights reserved, Your Company Name'
+ char(10) + '*******************************************************************************'
+ char(10) + '** Change History'
+ char(10) + '** ' + convert(varchar(20), getdate(), 101) +char(9) + 'Edit template as needed. '
+ char(10) + '*******************************************************************************'
+ char(10) + '** $Log$'
+ char(10) + '*******************************************************************************/'
+ char(10)
set @select=
+char(10) + 'create function ' + @get + ' ('
+char(10) + @plparm + ' )'
+char(10) + ' RETURNS @tblret TABLE ('
+ @tblretparam
+ ') as '
+char(10)+ 'BEGIN'
+char(10)
+char(10)+'-- get data based on primary/unique keys'
+char(10)+char(9) +'--"FIND"--declare @found int --"FIND"--'
+char(10)+char(9) +'declare @otherCrit int --"AND"--'
+char(10)+char(9) + 'select @otherCrit = 0'
+char(10)
+char(10)+char(9) +'insert into @tblret ('
+char(10)+char(9) +char(9)+ @clcolumns1
+char(10)+char(9) +char(9)+ ')'
+char(10)+char(9) + 'select ' +@clcolumns1
+char(10)+char(9) + 'from' +char(9) +@tablename
+char(10)+char(9) + 'where' +char(9) +@klcolumns1
+char(10)+char(9) + '--"FIND"--select @found = @@rowcount -- text for "FIND"'
+char(10)+char(9) + 'select @otherCrit = 1 -- "AND"--'
+char(10)+'--==========================================================='
+char(10)+'-- Template code for different column types that will reduce results returned in @tblRet'
+char(10)+'-- Edit code as needed'
-- create update for top 3 columns
declare @sqlLoop varchar(8000)
set @sqlLoop = ''
--char(10)+ '--for "FIND"--if ( isnull(@found,0) = 0 and isnull(@' +'column_name'+','+'qts'+'default_val'+'qts'+')>'+'qts+default_val+qts'+ ')-- "FIND"'
--+char(10)+char(9) + '--"FIND"--select @found = @@rowcount'
select @sqlLoop = @sqlLoop +
-------------------------------------------------------------------------
+char(10)
+char(10)+'--Check if column '+ column_name + ' need to be searched'
+char(10)+ 'if ( isnull(@' + column_name+','+qts+default_val+qts+')>'+qts+default_val+qts+ ') --"AND"--'
+char(10)+ 'begin '
+char(10)+char(9)+ 'if (@otherCrit > 0) -- "AND" '
+char(10)+char(9)+char(9)+ 'delete from @tblret where ' + column_name + ' != @' + column_name
+char(10)+char(9)+ 'else'
+char(10)+char(9)+char(9)+'insert into @tblret ('
+char(10)+char(9) +char(9)+ @clcolumns1
+char(10)+char(9) +char(9)+ ')'
+char(10)+char(9)+char(9) + 'select ' +@clcolumns1
+char(10)+char(9)+char(9) + 'from' +char(9) +@tablename
+char(10)+char(9)+char(9) + 'where' +char(9) +column_name + ' = @' + column_name +
+char(10)+char(9) + 'set @otherCrit = 1 -- "AND"--'
+char(10)+ 'end'
---------------------------------------------------------------------------
-- get columns one of each type as template
from ( select top 5 convert(varchar(128),min(column_name)) column_name, min(ordinal_position) ordinal_position, convert(varchar(128),data_type) data_type, min(default_val) default_val, qts
from information_schema.columns c left outer join dbcommon..dbtypes t on c.data_type = t.type_nm
where table_name = @tablename
group by data_type, qts ) t
set @end = char(10) + 'RETURN'
+char(10) + 'END'
+char(10) + '--==================== TEST ===================='
+char(10) + '-- select top 10 * from ' + @tablename
+char(10) + '-- select * from dbo.' + @get + '(' + @vlcolumns1 + ')'
+char(10) + '--==================== END ======================'
exec (@head + @select+@sqlLoop+@end)
print len(@head)
print len(@select)
print len(@sqlLoop)
print len(@end)
--print @head
--print @select
--print @sqlLoop
--print @end
print 'Finished ' + @get
/*---------------------------------------------------------------------------*//* select end *//*---------------------------------------------------------------------------*//*****************************
exec usp_DBA_createSPs 'major_ind_cdt','tmp','code'
*****************************/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 
