construct

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