SqlSavior.com
use master
go

if exists (select * from sysobjects where name = 'sp_construct_all_standard_procedures' and type = 'P')
     begin
     print 'dropping procedure sp_construct_all_standard_procedures'
     drop procedure sp_construct_all_standard_procedures
     end
go

print 'creating procedure sp_construct_all_standard_procedures'
go

create procedure sp_construct_all_standard_procedures
as
begin

--
Copyright (C) Fred Williams SqlSavior.com 2001-2006
-- calls sp_construct_standard_procedures_for_table for each table in the current database

set nocount on

declare @cmd                nvarchar(200)
declare @table_name        varchar(60)        -- name of table being processed


declare table_cursor cursor for
     select        sysobjects.name
      from        sysobjects, sysusers
      where        sysobjects.type = 'U'
      and        sysobjects.uid = sysusers.uid
      and        sysusers.name = 'dbo'                -- only do tables owned by dbo
      order by sysobjects.name

open table_cursor

fetch table_cursor into @table_name

while @@fetch_status >= 0                -- for each user table in the database....
     begin

     select @cmd = N'exec sp_construct_standard_procedures_for_table ' + @table_name
     execute sp_executesql @cmd

     fetch table_cursor into @table_name
     end

close table_cursor
deallocate table_cursor


end        -- sp_construct_all_standard_procedures
go

grant execute on sp_construct_all_standard_procedures to public
go

---------------------------------------------------------------------------------------------------

use master
go

if exists (select * from sysobjects where name = 'sp_construct_standard_procedures_for_table' and type = 'P')
     begin
     print 'dropping procedure sp_construct_standard_procedures_for_table'
     drop procedure sp_construct_standard_procedures_for_table
     end
go

print 'creating procedure sp_construct_standard_procedures_for_table'
go


create procedure sp_construct_standard_procedures_for_table
@table_name        varchar(60)
as
begin                -- (C) Copyright Fred Williams, 2002
-2006. All Rights Reserved.

set nocount on
declare @crlf                char(2)
declare @tab                char(1)
declare @crlftab        char(3)
declare @msg                varchar(2000)

select @tab = char(9)                        -- tab character
select @crlf = char(13) + char(10)        -- carriage return, line feed
select @crlftab = @crlf + @tab

declare @temp_table_name        varchar(60)        -- name of table being processed
declare @table_id                int                -- id of table in sysobjects
declare @column_name                varchar(60)        -- name of column being processed
declare @key_id                        int                -- id in sysobjects for primary key
declare @has_key_flag                char(1)                -- T = table has a primary key or unique index, F = not
declare @index_id                int                -- indid in sysindexes for unique index
declare @key_column_name        varchar(60)        -- name of a key column
declare @last_column_name        varchar(60)        -- name of last column in parameter list, column list, or select list
declare @identity_column_name        varchar(60)        -- name of identity columnfor table
declare @column_id                int                -- id of column in syscolumns
declare @datatype                varchar(17)        -- data type of column, from syscolumns
declare @is_nullable_flag        char(1)                -- T = column allows null, F = not
declare @is_identity_flag        char(1)                -- T = column has identity property, F = not
declare @is_primary_key_flag        char(1)                -- T = column is part of primary key or unique index, F = not
declare @is_insert_param_flag        char(1)                -- T = column should be in parameter list for insert procedure, F
= not
declare @is_insert_list_flag        char(1)                -- T = column should be in column list and select list for insert
procedure, F = not
declare @is_update_param_flag        char(1)                -- T = column should be in parameter list for update procedure, F
= not
declare @key_order_by                int                -- used to preserve ordering of key columns
declare @i                        int                -- loop counter
declare @name_length                int                -- length of column name
declare @insert_user_id_col_name        varchar(60)        -- name of column in form: insert_%_user_id, if any
declare @update_user_id_col_name        varchar(60)        -- name of column in form: update_%_user_id, if any
declare @constraint_expression        varchar(1000)        -- first 255 characters of bound default or default constraint
declare @default_expression        varchar(255)        -- munged version of @constraint_expression, legal for parameter
default
declare @found_default_flag        char(1)                -- T = found a default for column, F = not
declare @has_active_flag        char(1)                -- T = table has a column called active_flag, F = not
declare        @has_owner                char(1)                -- T = table has a column called owner, F = not
declare @procedure_name                varchar(255)        -- stored procedure name to be generated





select @temp_table_name = NULL

select @temp_table_name = name from sysobjects where name = @table_name and type = 'U'



if @temp_table_name is null
     begin
     select @msg = '-- Error: table ' + @table_name + ' not found.' + @crlf
                 + '-- Usage: exec sp_construct_standard_procedures_for_table @table_name = ''table_name'''
     print @msg
     return -1
     end

select @table_name = @temp_table_name                -- preserve original capitalization
select @table_id = object_id(@table_name)



-- create a temporary table to hold all columns in the table being processed

create table #columns
(
column_name                varchar(60)        not null,
column_id                int                not null,
datatype                varchar(17)        not null,
is_nullable_flag        char(1)                not null,
is_identity_flag        char(1)                not null,
is_primary_key_flag        char(1)                not null default 'F',
is_insert_param_flag        char(1)                not null default 'T',
is_insert_list_flag        char(1)                not null default 'T',
is_update_param_flag        char(1)                not null default 'T',
key_order_by                int                null,
default_expression        varchar(255)        null
)

-- this index needed to avoid implicit conversion to static cursor that does not reflect updates to table
create unique index col_index on #columns (column_name)
create unique index col_index2 on #columns (column_id)


select @msg = '------------------------------------------------------------------------------------------'
     + @crlf + '-- Table: ' + @table_name + @crlf
     +     '------------------------------------------------------------------------------------------'
     + @crlf + @crlf
print @msg

-- get column information from syscolumns and systypes

insert #columns
(
     column_name,
     column_id,
     datatype,
     is_nullable_flag,
     is_identity_flag
)
select
     convert(varchar(60), c.name),
     colid,
     case t.name
             when 'varchar' then convert(varchar(17), 'varchar(' + convert(varchar, c.length) + ')' )
             when 'nvarchar' then convert(varchar(17), 'nvarchar(' + convert(varchar, c.length/2) + ')' )
             when 'numeric' then convert(varchar(17), 'numeric(' + convert(varchar, c.prec) + ',' + convert(varchar, c.scale)
+ ')' )
             when 'decimal' then convert(varchar(17), 'decimal(' + convert(varchar, c.prec) + ',' + convert(varchar, c.scale) +
')' )
             when 'char' then convert(varchar(17), 'char(' + convert(varchar, c.length) + ')' )
             when 'nchar' then convert(varchar(17), 'nchar(' + convert(varchar, c.length/2) + ')' )
             when 'binary' then convert(varchar(17), 'binary(' + convert(varchar, c.length) + ')' )
             when 'varbinary' then convert(varchar(17), 'varbinary(' + convert(varchar, c.length) + ')' )
             else convert(varchar(17), t.name)                        
             end,
     case isnullable
             when 1 then 'T'
             else 'F'
             end,
     case colstat & 1
             when 1 then 'T'
             else 'F'
             end
from syscolumns c, systypes t
where c.id = @table_id
and c.xtype = t.xtype
and t.xtype = t.xusertype
order by colid

select @index_id = null
select @key_id = null

-- look for a primary key

select @key_id = id from sysobjects where parent_obj = @table_id and xtype = 'PK'

if @key_id is null                -- look for a unique index
     select @index_id = min(indid) from sysindexes
             where id = @table_id and indid > 0 and indid < 255 and (status & 2) <> 0

if @key_id is null and @index_id is null
     select @has_key_flag = 'F'
else
     select @has_key_flag = 'T'

if @has_key_flag = 'T'
     begin

     if @index_id is null                -- find the index used to enforce the primary key
             select        @index_id = indid
             from        sysindexes
             where        name = object_name(@key_id)
             and        id = @table_id

     select        @i = 1
     select        @key_column_name = null
     select        @key_column_name = index_col(@table_name, @index_id, @i)

     while (@key_column_name is not null)
             begin
             update #columns
              set is_primary_key_flag = 'T', key_order_by = @i
              where column_name = @key_column_name
             select @i = @i + 1
             select @key_column_name = null
             select @key_column_name = index_col(@table_name, @index_id, @i)
             end

     end

-- find any column defaults whose expression is legal as a parameter default expression

declare temp_column_cursor insensitive cursor for
     select column_name, column_id from #columns order by column_id

open temp_column_cursor

fetch temp_column_cursor into @column_name, @column_id
while @@fetch_status >= 0
     begin

     -- look for a default constraint first

     select @constraint_expression = null
     select @found_default_flag = 'F'

     select        @constraint_expression = convert(varchar(255), text)
      from        sysobjects, syscomments
      where        sysobjects.parent_obj = @table_id
      and        sysobjects.id = syscomments.id
      and        sysobjects.xtype = 'D '
      and        col_name(@table_id, info) = @column_name
      and        syscomments.colid = 1        -- first line of text only


     if @constraint_expression is not null
             select @found_default_flag = 'T'

     -- if expression was truncated to 255 characters, don't bother with it

     if datalength(@constraint_expression) between 3 and 254
             begin

             -- trim off surrounding parenthesis

             select @constraint_expression =
              substring(@constraint_expression, 2, (datalength(@constraint_expression) - 2))

             end

     -- if no constraint style default found, look for an old-style bound default

     if @found_default_flag = 'F'
             begin

             select @constraint_expression = null

             select        @constraint_expression = convert(varchar(1000), text)
              from        syscolumns c,syscomments m
              where        c.id = @table_id
              and        m.id = c.cdefault
              and        ObjectProperty(c.cdefault, 'IsConstraint') = 0
              and        c.name = @column_name
     
             if @constraint_expression is not null                -- default follows "as" keyword
                     begin
                     select @found_default_flag = 'T'
                     select        @constraint_expression =
                             substring(@constraint_expression,
                                 patindex('% as %', @constraint_expression) + 4,
                                 datalength(@constraint_expression) - patindex('% as %', @constraint_expression) + 4)
                     end
             end

     -- get rid of carriage returns, linefeeds, and trailing blanks
     select @constraint_expression =
             rtrim(replace(replace(@constraint_expression, '' + char(10), ' '), '' + char(13), ''))

     -- check for some default expressions that are illegal as parameter default expressions
     -- update the #columns table if it seems OK

     if @found_default_flag = 'T'
        and @constraint_expression <> 'getdate()'                -- illegal
        and @constraint_expression <> 'current_timestamp'        -- illegal
        and (substring(@constraint_expression, 1, 1) = ''''        -- strings presumed OK
         or  charindex('(', @constraint_expression) = 0)        -- non-strings containing a '(' presumed illegal
             begin
             update        #columns
              set        default_expression = @constraint_expression
              where        column_id = @column_id
             end

     fetch temp_column_cursor into @column_name, @column_id
     end

close temp_column_cursor
deallocate temp_column_cursor

select @insert_user_id_col_name = NULL
select @update_user_id_col_name = NULL

-- find any columns named "insert_xxx_user_id" or "update_xxx_user_id"

select        @insert_user_id_col_name = column_name
from        #columns
where        column_name like 'insert_%'
and        column_name like '%_user_id'

select        @update_user_id_col_name = column_name
from        #columns
where        column_name like 'update_%'
and        column_name like '%_user_id'

-- set is_insert_param_flag FALSE for certain columns

update        #columns
set         is_insert_param_flag = 'F'
where        column_name in ('insert_datetime', 'update_datetime')
or        column_name = @update_user_id_col_name

-- set is_insert_list_flag FALSE for certain columns

update        #columns
set         is_insert_list_flag = 'F'
where        column_name in ('insert_datetime', 'update_datetime')
or        is_identity_flag = 'T'

select @identity_column_name = NULL
select @identity_column_name = column_name from #columns where is_identity_flag = 'T'

-- create a scrollable cursor over the #columns table, reused several times per table

declare column_cursor scroll cursor for
     select        column_name,
             datalength(column_name),
             datatype,
             is_nullable_flag,
             is_identity_flag,
             is_primary_key_flag,
             is_insert_param_flag,
             is_insert_list_flag,
             is_update_param_flag,
             key_order_by,
             default_expression
      from        #columns
      order by column_id

open column_cursor

if @has_key_flag = 'T'  -- declare a cursor on key columns
     begin
     declare key_cursor scroll cursor for
             select column_name, datatype from #columns where is_primary_key_flag = 'T' order by key_order_by

     open key_cursor
     end

--------------------- construct an insert procedure ------------------------------




-- create procedure name
select @procedure_name = @table_name + '_insert_auto'

-- construct drop and create portions

select        @msg = 'if exists (select * from sysobjects where name = ''' + @procedure_name + ''' and type = ''P'')'
     + @crlftab + 'begin' + @crlftab + 'print ''dropping procedure ' + @procedure_name + '''' + @crlftab
     + 'drop procedure '+ @procedure_name + @crlftab + 'end'
     + @crlf + 'go' + @crlf + @crlf
     + 'print ''creating procedure ' + @procedure_name + '''' + @crlf + 'go' + @crlf + @crlf
     + 'CREATE PROCEDURE ' +  @procedure_name

print @msg

select @last_column_name = column_name from #columns where column_id =
             (select max(column_id) from #columns where is_insert_param_flag = 'T')



fetch first from column_cursor
into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
     @key_order_by, @default_expression

-- print the parameter list for the insert procedure

while @@fetch_status >= 0
     begin

     if @is_insert_param_flag = 'T'
             begin
     
             select @msg = '@' + @column_name
             select @i = datalength(@msg)
             while @i < 64
                     begin
                     select @msg = @msg + ' '
                     select @i = datalength(@msg)
                     end
     
             select @msg = @msg + @datatype
             if @is_nullable_flag = 'T' and @default_expression is null
                     select @msg = @msg + ' = NULL'
             if @default_expression is not null
                     select @msg = @msg + ' = ' + @default_expression
             if @is_identity_flag = 'T'
                     select @msg = @msg + ' OUTPUT'
             if @column_name <> @last_column_name
                     select @msg = @msg + ','
             else
                     select @msg = @msg + ',' + @crlf
                             + '@return_result_flag                                             char(1) = ''F'',' + @crlf
                             + '@zdbi_result_number                                             tinyint = 1'

             select @msg = @msg + @crlf
             print @msg

             end

     fetch next from column_cursor
      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
             @key_order_by, @default_expression

     end        -- printing parameter list
             

select @msg = 'as' + @crlf + 'begin' + @crlf + @crlf
     + '--  Procedure Generated ' + cast(getdate() as varchar(30)) + @crlf + @crlf
     + @crlf + 'declare @err int' + @crlf
     + 'declare @row_count int' + @crlf + @crlftab + 'set nocount on' + @crlf  
     + @crlftab + 'insert ' + @table_name + @crlftab + '(' + @crlf

print @msg


select @last_column_name = column_name from #columns where column_id =
             (select max(column_id) from #columns where is_insert_list_flag = 'T')

fetch first from column_cursor
into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
     @key_order_by, @default_expression

-- print the column list for the select statement

while @@fetch_status >= 0
     begin
     if @is_insert_list_flag = 'T'
             begin

             select @msg = @tab + @tab + @column_name
             if @column_name <> @last_column_name
                     select @msg = @msg + ','
             select @msg = @msg + @crlf
             print @msg

             end

     fetch next from column_cursor
      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
             @key_order_by, @default_expression

     end        -- printing column list


select @msg = @tab + ')' + @crlftab + 'select' + @crlf
print @msg

-- print select list for the select statement

fetch first from column_cursor
into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
     @key_order_by, @default_expression

while @@fetch_status >= 0
     begin
     if @is_insert_list_flag = 'T'
             begin

             if @column_name = @update_user_id_col_name and @insert_user_id_col_name is not null
                     begin
                     if @column_name = @last_column_name
                             select @last_column_name = @insert_user_id_col_name
                     select @column_name = @insert_user_id_col_name
                     end

             select @msg = @tab + @tab + '@' + @column_name
             if @column_name <> @last_column_name
                     select @msg = @msg + ','
             select @msg = @msg + @crlf
             print @msg

             end

     fetch next from column_cursor
      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
             @key_order_by, @default_expression

     end        -- printing select list


select @msg = @crlftab + 'select @err = @@error, @row_count = @@rowcount' + @crlf + @crlf
print @msg

-- if the table has an identity column, set the output variable

if @identity_column_name is not null
     begin
     select @msg = @tab + 'select @' + @identity_column_name + ' = scope_identity()' + @crlf + @crlf
     print @msg
     end

if @has_key_flag = 'T'
     begin
     select @msg = @tab + 'if @return_result_flag = ''T'' and @err = 0 and @row_count = 1' + @crlftab + @tab +
'select '
                 + @crlftab + @tab + @tab + '@zdbi_result_number as zdbi_result_number,'
     print @msg

     fetch last from key_cursor into @last_column_name, @datatype

     fetch first from key_cursor into @column_name, @datatype
     while @@fetch_status >= 0
             begin

             if @datatype in ('smalldatetime', 'datetime')
                     select @msg = @tab + @tab + @tab + 'convert(varchar, @' + @column_name + ', 121) as ''' +
@column_name + '''' + @crlf
             else
                     select @msg = @tab + @tab + @tab + '@' + @column_name + ' as ''' + @column_name + '''' + @crlf

             if @column_name <> @last_column_name
                     select @msg = @msg + ', '
             fetch next from key_cursor into @column_name, @datatype
             end
     select @msg = @msg + @crlf
     print @msg

     end


select @msg = @tab + 'if @err = 0 and @row_count = 1' + @crlftab + @tab + 'return 0' + @crlftab + 'else'
      + @crlftab + @tab + 'return -1'
     + @crlf + @crlf + 'end' + @tab + '-- ' + @procedure_name + @crlf + 'go' + @crlf + @crlf + @crlf
print @msg

------------end insert -------------------



-- if the table has no key, skip construction of update and delete procedures

if @has_key_flag = 'F'
     goto the_end



-- determine if there are any updatable columns

select        @i = count(*) from #columns
where        is_primary_key_flag = 'F'
and        is_identity_flag = 'F'
and        column_name not in ('insert_datetime', 'update_datetime')
and        column_name <> isnull(@insert_user_id_col_name, '')        -- comparisons always FALSE when operand is
null
and        column_name <> isnull(@update_user_id_col_name, '')

if @i = 0
     goto construct_delete




-----------------------------------------------------------------------
-- if datetime stamp is part of the primary key we do not want to create an insert_update procedure

if exists (
     select *
     from #columns
     where is_primary_key_flag = 'T'
     and (column_name like '%datetime%' or datatype like '%datetime')
)
     goto construct_update


--------------------- construct an insert_update procedure ------------------------------


-- create procedure name
select @procedure_name = @table_name + '_insert_update_auto'

-- construct drop and create portions

select        @msg = 'if exists (select * from sysobjects where name = ''' + @procedure_name + ''' and type = ''P'')'
     + @crlftab + 'begin' + @crlftab + 'print ''dropping procedure ' + @procedure_name + '''' + @crlftab
     + 'drop procedure '+ @procedure_name + @crlftab + 'end'
     + @crlf + 'go' + @crlf + @crlf
     + 'print ''creating procedure ' + @procedure_name + '''' +@crlf + 'go' + @crlf + @crlf
     + 'CREATE PROCEDURE ' +  @procedure_name

print @msg

select @last_column_name = column_name from #columns where column_id =
             (select max(column_id) from #columns where is_insert_param_flag = 'T')


fetch first from column_cursor
into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
     @key_order_by, @default_expression

-- print the parameter list for the insert/update procedure

while @@fetch_status >= 0
     begin

     if @is_insert_param_flag = 'T'
             begin
     
             select @msg = '@' + @column_name
             select @i = datalength(@msg)
             while @i < 64
                     begin
                     select @msg = @msg + ' '
                     select @i = datalength(@msg)
                     end
     
             select @msg = @msg + @datatype
             if @is_nullable_flag = 'T' and @default_expression is null
                     select @msg = @msg + ' = NULL'
             if @default_expression is not null
                     select @msg = @msg + ' = ' + @default_expression
             if @is_identity_flag = 'T'
                     select @msg = @msg + ' OUTPUT'
             if @column_name <> @last_column_name
                     select @msg = @msg + ','
             else
                     select @msg = @msg + ',' + @crlf
                             + '@return_result_flag                                             char(1) = ''F'',' + @crlf
                             + '@zdbi_result_number                                             tinyint = 1'

             select @msg = @msg + @crlf
             print @msg

             end

     fetch next from column_cursor
      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
             @key_order_by, @default_expression

     end        -- printing parameter list
             


select @msg = 'as' + @crlf + 'begin' + @crlf + @crlf
     + '--  Procedure Generated ' + cast(getdate() as varchar(30)) + @crlf + @crlf
     + @crlf + 'declare @err int' + @crlf
     + 'declare @row_count int' + @crlf + @crlftab + 'set nocount on' + @crlf  
     + @crlftab + 'if not exists ( select * from ' + @table_name + @crlftab + @tab + @tab + ' where '  -- add check to see
if the row already exists in the database

-- print the where clause using the key columns

fetch last from key_cursor into @last_column_name, @datatype

fetch first from key_cursor into @column_name, @datatype
if @@fetch_status >= 0
     begin
     select @msg = @msg + @tab +  @column_name + ' = @' + @column_name + @crlf
     print @msg
     end

fetch next from key_cursor into @column_name, @datatype
     
while @@fetch_status >= 0
     begin
     select @msg = @tab + @tab + @tab + ' and' + @tab + @column_name + ' = @' + @column_name + @crlf
     print @msg
     fetch next from key_cursor into @column_name, @datatype
     end

select @msg = @tab + @tab + @tab + ')' + @crlftab + 'begin' + @crlf
print @msg

-- if the row does not exist in the database insert it

select @msg = @crlftab + @tab + 'insert ' + @table_name + @crlftab + @tab + '(' + @crlf
print @msg

select @last_column_name = column_name from #columns where column_id =
             (select max(column_id) from #columns where is_insert_list_flag = 'T')

fetch first from column_cursor
into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
     @key_order_by, @default_expression

-- print the column list for the select statement

while @@fetch_status >= 0
     begin
     if @is_insert_list_flag = 'T'
             begin

             select @msg = @tab + @tab + @tab + @column_name
             if @column_name <> @last_column_name
                     select @msg = @msg + ','
             select @msg = @msg + @crlf
             print @msg

             end

     fetch next from column_cursor
      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
             @key_order_by, @default_expression

     end        -- printing column list


select @msg = @tab + @tab + ')' + @crlftab + @tab +  'select' + @crlf
print @msg

-- print select list for the select statement

fetch first from column_cursor
into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
     @key_order_by, @default_expression

while @@fetch_status >= 0
     begin
     if @is_insert_list_flag = 'T'
             begin

             if @column_name = @update_user_id_col_name and @insert_user_id_col_name is not null
                     begin
                     if @column_name = @last_column_name
                             select @last_column_name = @insert_user_id_col_name
                     select @column_name = @insert_user_id_col_name
                     end
     
             select @msg = @tab + @tab + @tab + '@' + @column_name
             if @column_name <> @last_column_name
                     select @msg = @msg + ','
             select @msg = @msg + @crlf
             print @msg

             end

     fetch next from column_cursor
      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
             @key_order_by, @default_expression

     end        -- printing select list

select @msg = @crlftab + @tab + 'select @err = @@error, @row_count = @@rowcount' + @crlf + @crlf
print @msg


-- if the table has an identity column, set the output variable

if @identity_column_name is not null
     begin
     select @msg = @tab + @tab + 'select @' + @identity_column_name + ' = scope_identity()' + @crlf + @crlf
     print @msg
     end
     
-- end of insert portion, beginning of update
select @msg = @tab + 'end' + @crlftab + 'else' + @crlftab + 'begin' + @crlf
print @msg


------------ update portion  -------------


--   set is_update_param_flag FALSE for certain columns

update        #columns
set         is_update_param_flag = 'F'
where        (
     column_name = 'insert_datetime'
or        column_name = 'update_datetime'
or        column_name = @insert_user_id_col_name
     )
and        is_primary_key_flag = 'F'

select @msg = @crlftab + @tab + 'update ' + @table_name + @crlftab + @tab + ' set'
print @msg

-- re-use the param list, include update_datetime but not key columns
update #columns set is_update_param_flag = 'T'
where column_name = 'update_datetime'

update #columns set is_update_param_flag = 'F'
where is_primary_key_flag = 'T'

select @last_column_name = column_name from #columns where column_id =
             (select max(column_id) from #columns where is_update_param_flag = 'T'
                     and is_primary_key_flag = 'F')

fetch first from column_cursor
into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
     @key_order_by, @default_expression

while @@fetch_status >= 0
     begin
     if @is_update_param_flag = 'T' and @is_primary_key_flag = 'F'
             begin

             select @msg = @tab + @tab + @tab + @column_name + ' = '
                     + case @column_name
                             when 'update_datetime' then 'getdate()'
                             when 'update_los_user_id' then '@insert_los_user_id'  -- only for insert update procedure
                             else '@' + @column_name
                     end

             if @column_name <> @last_column_name
                     select @msg = @msg + ','
             select @msg = @msg + @crlf
             print @msg

             end

     fetch next from column_cursor
      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
             @key_order_by, @default_expression

     end        -- printing update set columns and values

select @msg = @tab + @tab + ' where' + @crlf
print @msg

-- print the where clause using the key columns

fetch last from key_cursor into @last_column_name, @datatype

fetch first from key_cursor into @column_name, @datatype
if @@fetch_status >= 0
     begin
     select @msg = @tab + @tab + @tab + @column_name + ' = @' + @column_name + @crlf
     print @msg
     end

fetch next from key_cursor into @column_name, @datatype
     
while @@fetch_status >= 0
     begin
     select @msg = @tab + @tab + ' and' + @tab + @column_name + ' = @' + @column_name + @crlf
     print @msg
     fetch next from key_cursor into @column_name, @datatype
     end

select @msg = @crlftab + @tab + 'select @err = @@error, @row_count = @@rowcount' + @crlf + @crlftab + 'end' +
@crlf + @crlf
print @msg


if @has_key_flag = 'T'
     begin
     select @msg = @tab + 'if @return_result_flag = ''T'' and @err = 0 and @row_count = 1' + @crlftab + @tab +
'select '
                 + @crlftab + @tab + @tab + '@zdbi_result_number as zdbi_result_number,'
     print @msg

     fetch last from key_cursor into @last_column_name, @datatype

     fetch first from key_cursor into @column_name, @datatype
     while @@fetch_status >= 0
             begin

             if @datatype in ('smalldatetime', 'datetime')
                     select @msg = @tab + @tab + @tab + 'convert(varchar, @' + @column_name + ', 121) as ''' +
@column_name + '''' + @crlf
             else
                     select @msg = @tab + @tab + @tab + '@' + @column_name + ' as ''' + @column_name + '''' + @crlf

             if @column_name <> @last_column_name
                     select @msg = @msg + ', '
             fetch next from key_cursor into @column_name, @datatype
             end
     select @msg = @msg + @crlf
     print @msg

     end

select @msg = @crlftab + 'if @err = 0 and @row_count = 1' + @crlftab + @tab + 'return 0' + @crlftab + 'else'
      + @crlftab + @tab + 'return -1'
     + @crlf + @crlf + 'end' + @tab + '-- ' + @procedure_name + @crlf + 'go' + @crlf + @crlf + @crlf
print @msg





---------------- construct update procedures -------------------------


construct_update:

-- first the _set_all style

-- build procedure name

select @procedure_name = @table_name + '_update_set_all_auto'


-- set is_update_param_flag FALSE for certain columns

update        #columns
set         is_update_param_flag = 'F'
where        (
     column_name = 'insert_datetime'
or        column_name = 'update_datetime'
or        column_name = @insert_user_id_col_name
     )
and        is_primary_key_flag = 'F'



-- construct drop and create portions

select        @msg = 'if exists (select * from sysobjects where name = ''' + @procedure_name + ''' and type = ''P'')'
     + @crlftab + 'begin' + @crlftab + 'print ''dropping procedure ' + @procedure_name + '''' + @crlftab
     + 'drop procedure '+ @procedure_name  + @crlftab + 'end'
     + @crlf + 'go' + @crlf + @crlf
     + 'print ''creating procedure ' + @procedure_name + '''' +@crlf + 'go' + @crlf + @crlf
     + 'CREATE PROCEDURE ' +  @procedure_name

print @msg

update #columns set is_update_param_flag = 'T'
where is_primary_key_flag = 'T'

select @last_column_name = column_name from #columns where column_id =
             (select max(column_id) from #columns where is_update_param_flag = 'T')


fetch first from column_cursor
into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
     @key_order_by, @default_expression

-- print the parameter list

while @@fetch_status >= 0
     begin

     if @is_update_param_flag = 'T'
             begin
     
             select @msg = '@' + @column_name
             select @i = datalength(@msg)
             while @i < 64
             begin
                     select @msg = @msg + ' '
                     select @i = datalength(@msg)
             end
     
             select @msg = @msg + @datatype
     
             if @column_name <> @last_column_name
                     select @msg = @msg + ','
             select @msg = @msg + @crlf
             print @msg

             end

     fetch next from column_cursor
      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
             @key_order_by, @default_expression

     end        -- printing parameter list

-- print update statement with set columns and values

select @msg = 'as' + @crlf + 'begin' + @crlf  + @crlf
     + '--  Procedure Generated ' + cast(getdate() as varchar(30)) + @crlf + @crlf
     + @crlf + 'declare @err int' + @crlf
     + 'declare @row_count int' + @crlf + @crlftab + 'set nocount on' + @crlf + @crlftab
     + 'update ' + @table_name + @crlftab + ' set'
print @msg

-- re-use the param list, include update_datetime but not key columns
update #columns set is_update_param_flag = 'T'
where column_name = 'update_datetime'

update #columns set is_update_param_flag = 'F'
where is_primary_key_flag = 'T'

select @last_column_name = column_name from #columns where column_id =
             (select max(column_id) from #columns where is_update_param_flag = 'T'
                     and is_primary_key_flag = 'F')

fetch first from column_cursor
into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
     @key_order_by, @default_expression

while @@fetch_status >= 0
     begin
     if @is_update_param_flag = 'T' and @is_primary_key_flag = 'F'
             begin

             select @msg = @tab + @tab + @column_name + ' = '
                     + case @column_name
                             when 'update_datetime' then 'getdate()'
                             else '@' + @column_name
                     end

             if @column_name <> @last_column_name
                     select @msg = @msg + ','
             select @msg = @msg + @crlf
             print @msg

             end

     fetch next from column_cursor
      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
             @key_order_by, @default_expression

     end        -- printing update set columns and values

select @msg = @tab + ' where' + @crlf
print @msg

-- print the where clause using the key columns

fetch last from key_cursor into @last_column_name, @datatype

fetch first from key_cursor into @column_name, @datatype
if @@fetch_status >= 0
     begin
     select @msg = @tab + @tab + @column_name + ' = @' + @column_name + @crlf
     print @msg
     end

fetch next from key_cursor into @column_name, @datatype
     
while @@fetch_status >= 0
     begin
     select @msg = @tab + ' and' + @tab + @column_name + ' = @' + @column_name + @crlf
     print @msg
     fetch next from key_cursor into @column_name, @datatype
     end

select @msg = @crlftab + 'select @err = @@error, @row_count = @@rowcount' + @crlf + @crlf
     + @tab + 'if @err = 0 and @row_count = 1' + @crlftab + @tab + 'return 0' + @crlftab + 'else'
     + @crlftab + @tab + 'return -1'
     + @crlf + @crlf + 'end' + @tab + '-- ' + @procedure_name + @crlf + 'go' + @crlf + @crlf + @crlf
print @msg


-- next construct another update procedure in the _set_passed style


-- build procedure name

select @procedure_name = @table_name + '_update_set_passed_auto'

-- set is_update_param_flag FALSE for certain columns

update        #columns
set         is_update_param_flag = 'F'
where        column_name = 'insert_datetime'
or        column_name = 'update_datetime'
or        column_name = @insert_user_id_col_name

-- re-use is_update_param_flag, but include primary key columns

update #columns set is_update_param_flag = 'T'
where is_primary_key_flag = 'T'

     -- construct drop and create portions

select        @msg = 'if exists (select * from sysobjects where name = ''' + @procedure_name + ''' and type = ''P'')'
     + @crlftab + 'begin' + @crlftab + 'print ''dropping procedure ' + @procedure_name + '''' + @crlftab
     + 'drop procedure '+ @procedure_name + @crlftab + 'end'
     + @crlf + 'go' + @crlf + @crlf
     + 'print ''creating procedure ' + @procedure_name + '''' +@crlf + 'go' + @crlf + @crlf
     + 'CREATE PROCEDURE ' +  @procedure_name

print @msg


select @last_column_name = column_name from #columns where column_id =
             (select max(column_id) from #columns where is_update_param_flag = 'T')

fetch first from column_cursor
into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
     @key_order_by, @default_expression

-- print the parameter list, defaulting most columns to null

while @@fetch_status >= 0
     begin

     if @is_update_param_flag = 'T'
             begin

             select @msg = '@' + @column_name
             select @i = datalength(@msg)
             while @i < 64
                     begin
                     select @msg = @msg + ' '
                     select @i = datalength(@msg)
                     end

             select @msg = @msg + @datatype

             -- do not default key columns or update_xxx_user_id to null
             if @is_primary_key_flag = 'F' and @column_name <> isnull(@update_user_id_col_name, '')
                     select @msg = @msg + ' = NULL'

             if @column_name <> @last_column_name
                     select @msg = @msg + ','
             select @msg = @msg + @crlf
             print @msg

             end

     fetch next from column_cursor
      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
             @key_order_by, @default_expression

     end        -- parameter list

-- print update statement with set columns and values

select @msg = 'as' + @crlf + 'begin' + @crlf + @crlf
     + '--  Procedure Generated ' + cast(getdate() as varchar(30)) + @crlf + @crlf
     + @crlf + 'declare @err int' + @crlf
     + 'declare @row_count int' + @crlf + @crlf
     + '-- NOTE: it is not possible to reset a column to NULL using this procedure' + @crlf
     + @crlftab + 'set nocount on' + @crlf + @crlftab
     + 'update ' + @table_name + @crlftab + ' set'
print @msg

-- re-use the param list, include update_datetime, but exclude key columns
update #columns set is_update_param_flag = 'T'
where column_name = 'update_datetime'

update #columns set is_update_param_flag = 'F'
where is_primary_key_flag = 'T'

select @last_column_name = column_name from #columns where column_id =
             (select max(column_id) from #columns where is_update_param_flag = 'T'
                     and is_primary_key_flag = 'F')

fetch first from column_cursor
into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
     @key_order_by, @default_expression

while @@fetch_status >= 0
     begin
     if @is_update_param_flag = 'T' and @is_primary_key_flag = 'F'
             begin

             select @msg = @tab + @tab + @column_name + ' = '
                     + case @column_name
                             when 'update_datetime' then 'getdate()'
                             when @update_user_id_col_name then '@' + @column_name
                             else 'isnull(@' + @column_name + ', ' + @column_name + ')'
                     end

             if @column_name <> @last_column_name
                     select @msg = @msg + ','
             select @msg = @msg + @crlf
             print @msg

             end

     fetch next from column_cursor
      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
             @key_order_by, @default_expression

     end        -- printing update set columns and values

select @msg = @tab + ' where' + @crlf
print @msg

fetch last from key_cursor into @last_column_name, @datatype

fetch first from key_cursor into @column_name, @datatype
if @@fetch_status >= 0
     begin
     select @msg = @tab + @tab + @column_name + ' = @' + @column_name + @crlf
     print @msg
     end

fetch next from key_cursor into @column_name, @datatype
     
while @@fetch_status >= 0
     begin
     select @msg = @tab + ' and' + @tab + @column_name + ' = @' + @column_name + @crlf
     print @msg
     fetch next from key_cursor into @column_name, @datatype
     end

select @msg = @crlftab + 'select @err = @@error, @row_count = @@rowcount' + @crlf + @crlf
     + @tab + 'if @err = 0 and @row_count = 1' + @crlftab + @tab + 'return 0' + @crlftab + 'else'
     + @crlftab + @tab + 'return -1'
     + @crlf + @crlf + 'end' + @tab + '-- ' + @procedure_name +  @crlf + 'go' + @crlf + @crlf + @crlf
print @msg



----------------- construct a delete procedure ------------------



construct_delete:

-- build procedure name

select @procedure_name = @table_name + '_delete_auto'

-- determine if a column called active_flag exists

if exists (select * from #columns where column_name = 'active_flag')
     select @has_active_flag = 'T'
else
     select @has_active_flag = 'F'

-- construct drop and create portions

select        @msg = 'if exists (select * from sysobjects where name = ''' + @procedure_name + ''' and type = ''P'')'
     + @crlftab + 'begin' + @crlftab + 'print ''dropping procedure ' + @procedure_name + '''' + @crlftab
     + 'drop procedure '+ @procedure_name + @crlftab + 'end'
     + @crlf + 'go' + @crlf + @crlf
     + 'print ''creating procedure ' + @procedure_name + '''' +@crlf + 'go' + @crlf + @crlf
     + 'CREATE PROCEDURE ' +  @procedure_name

print @msg

-- print the parameter list

fetch last from key_cursor into @last_column_name, @datatype

fetch first from key_cursor into @column_name, @datatype
while @@fetch_status >= 0
     begin

     select @msg = '@' + @column_name
     select @i = datalength(@msg)
     while @i < 64
             begin
             select @msg = @msg + ' '
             select @i = datalength(@msg)
             end

     select @datatype = datatype from #columns where column_name = @column_name

     select @msg = @msg + @datatype

     if @column_name <> @last_column_name
             select @msg = @msg + ','
     select @msg = @msg + @crlf
     print @msg

     fetch next from key_cursor into @column_name, @datatype
     end

select @msg = 'as' + @crlf + 'begin' + @crlf  + @crlf
     + '--  Procedure Generated ' + cast(getdate() as varchar(30)) + @crlf + @crlf
     + @crlf + 'declare @err int' + @crlf
     + 'declare @row_count int' + @crlf + @crlftab + 'set nocount on' + @crlf + @crlftab

-- if the table has an active_flag column, just set it to 'F', else delete the row

if @has_active_flag = 'T'
     select @msg = @msg + 'update ' + @table_name + ' set active_flag = ''F''' + @crlftab + ' where'
else
     select @msg = @msg + 'delete ' + @table_name + @crlftab + ' where'
print @msg

-- construct the where clause with the key columns

fetch first from key_cursor into @column_name, @datatype
if @@fetch_status >= 0
     begin
     select @msg = @tab + @tab + @column_name + ' = @' + @column_name + @crlf
     print @msg
     end

fetch next from key_cursor into @column_name, @datatype

while @@fetch_status >= 0
     begin
     select @msg = @tab + ' and' + @tab + @column_name + ' = @' + @column_name + @crlf
     print @msg
     fetch next from key_cursor into @column_name, @datatype
     end

select @msg = @crlftab + 'select @err = @@error, @row_count = @@rowcount' + @crlf + @crlf
     + @tab + 'if @err = 0 and @row_count = 1' + @crlftab + @tab + 'return 0' + @crlftab + 'else'
     + @crlftab + @tab + 'return -1'
     + @crlf + @crlf + 'end' + @tab + '-- ' + @procedure_name + @crlf + 'go' + @crlf + @crlf + @crlf
print @msg




-------------------- construct a select procedure -------------------

/*
determine if table has the owner column
this code currently assumes that if you have the owner column, you have all 4 owner columns:
      owned_by_los_group_id
     special_access_id
     special_access_permission
     public_permission
*/


if exists (select * from #columns where column_name = 'owned_by_los_group_id')
     select @has_owner = 'T'
else
     select @has_owner = 'F'



-- build procedure name

select @procedure_name = @table_name + '_select_auto'

-- construct drop and create portions

select        @msg = 'if exists (select * from sysobjects where name = ''' + @procedure_name + ''' and type = ''P'')'
     + @crlftab + 'begin' + @crlftab + 'print ''dropping procedure ' + @procedure_name + '''' + @crlftab
     + 'drop procedure '+ @procedure_name + @crlftab + 'end'
     + @crlf + 'go' + @crlf + @crlf
     + 'print ''creating procedure ' + @procedure_name + '''' +@crlf + 'go' + @crlf + @crlf
     + 'CREATE PROCEDURE ' +  @procedure_name

print @msg

-- print the parameter list using key columns
select @msg = ''

fetch last from key_cursor into @last_column_name, @datatype

fetch first from key_cursor into @column_name, @datatype
while @@fetch_status >= 0
     begin

     select @msg = @msg + '@' + @column_name
             
     -- add spaces after column name so that the datatype always starts in the 64th column.
     select @i = datalength(@column_name) + 1
     while @i < 64
             begin
             select @msg = @msg + ' '
             select @i = @i + 1
             end

     select @datatype = datatype from #columns where column_name = @column_name

     select @msg = @msg + @datatype

     if @column_name <> @last_column_name
             select @msg = @msg + ',' + @crlf
     else
             select @msg = @msg + ',' + @crlf
                     + '@zdbi_result_number                                             tinyint = 1'

     
     --select @msg = @msg + @crlf


     fetch next from key_cursor into @column_name, @datatype
     end

if @has_owner = 'T'
     begin
     if not exists (select * from #columns where column_name = 'los_user_id')
             select @msg = @msg + ',' + @crlf + '@los_user_id                                                    char(1) = ''T'''
     else
             select @msg = @msg + @crlf
     end
else
     select @msg = @msg + @crlf

print @msg


select @msg = 'as' + @crlf + 'begin' + @crlf + @crlf
     + '--  Procedure Generated ' + cast(getdate() as varchar(30)) + @crlf + @crlf
     + '--            DO NOT MODIFY          ' + @crlf + @crlf
     + '--  If you need a modification, copy the procedure, rename to _custom instead of _auto' + @crlf
     + '--  Add the Revision, Log to the beginning and check it into CVS when you are done. ' + @crlf + @crlf
     + '--  The select procedure when generated assumes you want to search the table based on  ' + @crlf
     + '--  the primary key.  It also automatically adds the ownership columns to the query if  ' + @crlf
     + '--  they exist on the table. ' + @crlf
     + @crlf + 'declare @err int' + @crlf
print @msg


if @has_owner = 'T'
     begin
     select @msg = 'declare @super_user_flag char(1)' + @crlf + @crlf +
                     + @tab + 'select @super_user_flag = LU.super_user_flag' + @crlf +
                             + @tab + 'from' + @tab + 'los_user as LU' + @crlf +
                     + @tab + 'where' + @tab + 'LU.los_user_id = @los_user_id' + @crlf + @crlf
     print @msg
     end
else
     print @crlf

select @msg = @tab + 'select' +
             @crlftab + @tab + '@zdbi_result_number as zdbi_result_number,' +
             '  -- description of result set, used to uniquely identify this result when it is part of multiple result sets'  

print @msg
select @last_column_name = column_name from #columns where column_id =
             (select max(column_id) from #columns)

fetch first from column_cursor
into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
     @key_order_by, @default_expression

while @@fetch_status >= 0
     begin

     if @datatype in ('smalldatetime', 'datetime')
             select @msg = @tab + @tab + 'convert(varchar, ' + @column_name + ', 121) as ''' + @column_name + ''''
     else
             select @msg = @tab + @tab + @column_name

     if @column_name <> @last_column_name
             select @msg = @msg + ','

     print @msg

     fetch next from column_cursor
      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
             @key_order_by, @default_expression

     end


select @msg = @tab + 'from' + @tab + @table_name + @crlftab + 'where'
print @msg

-- construct the where clause with the key columns

fetch first from key_cursor into @column_name, @datatype
if @@fetch_status >= 0
     begin
     select @msg = @tab + @tab + @column_name + ' = @' + @column_name + @crlf
     print @msg
     end

fetch next from key_cursor into @column_name, @datatype

while @@fetch_status >= 0
     begin
     select @msg = @tab + ' and' + @tab + @column_name + ' = @' + @column_name + @crlf
     print @msg
     fetch next from key_cursor into @column_name, @datatype
     end

if @has_owner = 'T'
     begin
     print
'        and
     (
             owned_by_los_group_id is null
       or
             owned_by_los_group_id in (select los_group_id from user_in_group where los_user_id = @los_user_id)
       or
       (
             special_access_id in (select los_group_id from user_in_group where los_user_id = @los_user_id)
         and special_access_permission in (''W'', ''R'')
       )
       or        public_permission in (''W'', ''R'')
       or        @super_user_flag = ''T''
     )
'
     end


select @msg = @crlftab + 'select @err = @@error' + @crlf + @crlf
     + @tab + 'if @err = 0' + @crlftab + @tab + 'return 0' + @crlftab + 'else'
     + @crlftab + @tab + 'return -1'
     + @crlf + @crlf + 'end' + @tab + '-- ' + @procedure_name + @crlf + 'go' + @crlf + @crlf + @crlf
print @msg






----------------- construct a select all procedure ----------------------




-- build procedure name

select @procedure_name = @table_name + '_select_all_auto'


select @i = count(*) from #columns where is_primary_key_flag = 'T'
/*
if ( @table_name like '%type'
  or exists (select * from #columns where column_name like '%_code' and is_primary_key_flag = 'T')
  or exists (select * from db_table where table_name = @table_name and has_initial_data_flag = 'T') -- if this is a
static table, then print select_all
)
-- ---- REMOVING line, if there are multiple pk's, then they must send them all, instead of limiting the gen. procs.
--   and (@i = 1) -- and there is only one column as the primary key
*/
begin

     -- construct drop and create portions

     select        @msg = 'if exists (select * from sysobjects where name = ''' + @procedure_name + ''' and type = ''P'')'
             + @crlftab + 'begin' + @crlftab + 'print ''dropping procedure ' + @procedure_name + '''' + @crlftab
             + 'drop procedure '+ @procedure_name + @crlftab + 'end'
             + @crlf + 'go' + @crlf + @crlf
             + 'print ''creating procedure ' + @procedure_name + '''' +@crlf + 'go' + @crlf + @crlf
             + 'CREATE PROCEDURE ' +  @procedure_name
     
     print @msg

     -- print the parameter list
     -- (only parameter for get_all_ is @active_rows_only_flag, if applicable

     select @msg = ''

     if @has_active_flag = 'T'         -- add an additional parameter "active_rows_only_flag", default 'T'
             select @msg = '@active_rows_only_flag                                                char(1) = ''T'',' + @crlf

     if @has_owner = 'T'
             select @msg = @msg + '@los_user_id                                                    char(1) = ''T'',' + @crlf

     select @msg = @msg + '@zdbi_result_number                                             tinyint = 1' + @crlf

     print @msg

     select @msg = 'as' + @crlf + 'begin' + @crlf + @crlf
             + '--  Procedure Generated ' + cast(getdate() as varchar(30)) + @crlf + @crlf
             + '--            DO NOT MODIFY          ' + @crlf + @crlf
             + '--  If you need a modification, copy the procedure, rename to _custom instead of _auto' + @crlf
             + '--  Add the Revision, Log to the beginning and check it into CVS when you are done. ' + @crlf + @crlf
             + '--  This procedure selects all of the rows from the entire table.  It is only generated ' + @crlf
             + '--  for tables that are set up as "static" tables.  Static tables are defined in db_table ' + @crlf
             + '--  where the has_initial_data_flag = ''T'', or the table name ends in "type" ' + @crlf
             + '--  If the @active_rows_only_flag is passed with ''T'', then only the active rows are returned. ' + @crlf

             + @crlf + 'declare @err int' + @crlf
     print @msg

     if @has_owner = 'T'
             begin
             select @msg = 'declare @super_user_flag char(1)' + @crlf + @crlf +
                             + @tab + 'select @super_user_flag = LU.super_user_flag' + @crlf +
                              + @tab + 'from' + @tab + 'los_user as LU' + @crlf +
                             + @tab + 'where' + @tab + 'LU.los_user_id = @los_user_id' + @crlf + @crlf
             print @msg
             end
     else
             print @crlf

     -- if the table has an active_flag, construct a procedure with two select statements,
     -- one to include only active rows if @active_rows_only_flag = 'T' (default), and the
     -- other select statement to include all rows regardless

     -- else if the table has no active_flag, construct the procedure with just one select statement


     if @has_active_flag = 'T'
             begin

             select @msg = @tab + 'if @active_rows_only_flag = ''T''' + @crlf +
                             @crlftab + @tab + 'select' +
                             @crlftab + @tab + @tab + '@zdbi_result_number as zdbi_result_number,' +
                             '  -- number for result set, used to identify this result when it is part of multiple result sets'  
             print @msg

             -- first select statement

             fetch first from column_cursor
              into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
                     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
                     @key_order_by, @default_expression

             while @@fetch_status >= 0
                     begin

                     if @datatype in ('smalldatetime', 'datetime')
                             select @msg = @tab + @tab + 'convert(varchar, ' + @column_name + ', 121) as ''' + @column_name
+ ''''
                     else
                             select @msg = @tab + @tab + @column_name

                     if @column_name <> @last_column_name
                             select @msg = @msg + ','

                     print @msg

                     fetch next from column_cursor
                      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
                             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
                             @key_order_by, @default_expression

                     end

             select @msg = @tab + @tab + 'from' + @tab + @table_name + @crlftab + @tab + 'where'
             print @msg

             -- include a where clause with active_flag = 'T'

             select @msg = @tab + @tab + @tab + 'active_flag = ''T'''
             print @msg

             if @has_owner = 'T'
                     begin
                     print
'        and
     (
             owned_by_los_group_id is null
       or
             owned_by_los_group_id in (select los_group_id from user_in_group where los_user_id = @los_user_id)
       or
       (
             special_access_id in (select los_group_id from user_in_group where los_user_id = @los_user_id)
         and special_access_permission in (''W'', ''R'')
       )
       or        public_permission in (''W'', ''R'')
       or        @super_user_flag = ''T''
     )
'
                     end

             select @msg = @crlftab + 'else' + @crlf + @crlftab + @tab + 'select' +
                     @crlftab + @tab + @tab + '@zdbi_result_number as zdbi_result_number,' +
                     '  -- number for result set, used to identify this result when it is part of multiple result sets'

             print @msg

             -- second select statement

             fetch first from column_cursor
              into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
                     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
                     @key_order_by, @default_expression

             while @@fetch_status >= 0
                     begin

                     if @datatype in ('smalldatetime', 'datetime')
                             select @msg = @tab + @tab + 'convert(varchar, ' + @column_name + ', 121) as ''' + @column_name
+ ''''
                     else
                             select @msg = @tab + @tab + @column_name

                     if @column_name <> @last_column_name
                             select @msg = @msg + ','

                     print @msg

                     fetch next from column_cursor
                      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
                             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
                             @key_order_by, @default_expression

                     end

             select @msg = @tab + @tab + 'from' + @tab + @table_name + @crlf
             print @msg

             if @has_owner = 'T'
                     begin
                     print
'        and
     (
             owned_by_los_group_id is null
       or
             owned_by_los_group_id in (select los_group_id from user_in_group where los_user_id = @los_user_id)
       or
       (
             special_access_id in (select los_group_id from user_in_group where los_user_id = @los_user_id)
         and special_access_permission in (''W'', ''R'')
       )
       or        public_permission in (''W'', ''R'')
       or        @super_user_flag = ''T''
     )
'
                     end



             end        -- if @has_active_flag = 'T'
     else
             begin

             select @msg = @tab + 'select' +
                             @crlftab + @tab + '@zdbi_result_number as zdbi_result_number,'
             print @msg

             fetch first from column_cursor
              into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
                     @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
                     @key_order_by, @default_expression

             while @@fetch_status >= 0
                     begin

                     if @datatype in ('smalldatetime', 'datetime')
                             select @msg = @tab + @tab + 'convert(varchar, ' + @column_name + ', 121) as ''' + @column_name
+ ''''
                     else
                             select @msg = @tab + @tab + @column_name

                     if @column_name <> @last_column_name
                             select @msg = @msg + ','

                     print @msg

                     fetch next from column_cursor
                      into        @column_name, @name_length, @datatype, @is_nullable_flag, @is_identity_flag,
                             @is_primary_key_flag, @is_insert_param_flag, @is_insert_list_flag, @is_update_param_flag,
                             @key_order_by, @default_expression

                     end

             select @msg = @tab + 'from' + @tab + @table_name + @crlf
             print @msg

             if @has_owner = 'T'
                     begin
                     print
'        and
     (
             owned_by_los_group_id is null
       or
             owned_by_los_group_id in (select los_group_id from user_in_group where los_user_id = @los_user_id)
       or
       (
             special_access_id in (select los_group_id from user_in_group where los_user_id = @los_user_id)
         and special_access_permission in (''W'', ''R'')
       )
       or        public_permission in (''W'', ''R'')
       or        @super_user_flag = ''T''
     )
'
                     end

             end        -- @has_active_flag = 'F'

     select @msg = @crlftab + 'select @err = @@error' + @crlf + @crlf
             + @tab + 'if @err = 0' + @crlftab + @tab + 'return 0' + @crlftab + 'else'
             + @crlftab + @tab + 'return -1'
             + @crlf + @crlf + 'end' + @tab + '-- ' + @procedure_name + @crlf + 'go' + @crlf + @crlf + @crlf
     print @msg

     end        -- constructing get_all procedure


close key_cursor
deallocate key_cursor

the_end:


     close column_cursor
     deallocate column_cursor


drop table #columns

end        -- sp_construct_standard_procedures_for_table
go


grant execute on sp_construct_standard_procedures_for_table to public
go
Stored Procedure Code Generators from SqlSavior.com

Note: These two procedures are provided as shareware. Permission is granted by the copyright owner for
all uses
except commercial sale. Code generated by these procedures may be used freely, but licensing or
selling programs or code
containing these procedures themselves or derivatives thereof is expressly
forbidden without written consent of the author
.

Usage:        Create these procedures in the master database, then run the first procedure to create procs
for every table, or the second procedure to create procs for an individual table. Feel free to modify the
source code to accom
modate your individual requirements. Kindly send enhancements, bug reports, or
fixes to fred.williams
<at>sqlsavior.com.