generate save, delete, get, and list objects for a table

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