Technical Article

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

Read 614 times
(20 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating