/*==========================================================================================
**
** FILE  : uspsys_GenAdd.sql
** Author: 
**
**==========================================================================================
**
** (c) The contents of this file , and of any file or document derived from it , are copyright 
** to IRIS. Unlicensed alteration, change or copying in any form, 
** whether written, by photocopy, by print or by any other methods of reproduction is 
** strictly prohibited.
**
**==========================================================================================
**
** PURPOSE OF FILE :
**
** DETAILED DESCRIPTION OF FUNCTIONALITY
** LIST OF PARAMETERS USED
** In	: 
**	@table_name_in sysname = null
** Out	:  
**
** LIST OF RETURN VALUES AND THEIR DEFINITIONS 
** 0	:   Successful
**=========================================================================================*/

if exists( select * from sysobjects where id = object_id('dbo.uspsys_GenAdd') and type = 'P ') 
    drop proc dbo.uspsys_GenAdd
go

create procedure uspsys_GenAdd
	@table_name_in sysname = null
as
	set nocount on
	
	declare @error_number int,
		@row_count int,
		@table_name sysname,
		@str varchar(255),
		@str2 varchar(255)

	if @table_name_in is null
	begin
		select @table_name_in = '%'
	end

	-- create a temp table to hold output of sp_columns
	create table #t1 (
		table_qualifier sysname not null,
		table_owner sysname not null,
		table_name sysname not null,
		column_name sysname not null,
		data_type int not null,
		type_name sysname not null,
		prec int not null,
		length int null,
		scale int null,
		radix int null,
		nullable int null,
		remarks sysname null,
		column_def sysname null,
		sql_data_type int null,
		sql_datetime_sub int null,
		char_octet_length int null,
		ordinal_position int not null,
		is_nullable sysname null,
		ss_data_type int not null
	)

	insert into #t1
		exec sp_columns @table_name_in


	create table #t2 (
		column_name sysname not null,
		type_name varchar(255) not null,
	)


	declare cur_tables cursor for
		select name table_name
			from sysobjects
			where type = 'U ' and
				name like @table_name_in

	open cur_tables
	fetch cur_tables
		into @table_name

	while ( @@fetch_status = 0 )
	begin
		-- build a table having column_name and datatype for the table
		delete from #t2
		insert into #t2
			select column_name, 
					type_name = case when type_name = 'int identity' then 'int'
						when type_name in ( 'numeric', 'decimal' ) then 
							type_name + '(' + convert( varchar, prec ) + ', ' + convert( varchar, isnull( scale, 0 ) ) + ')'
						when type_name in ( 'char', 'varchar', 'varbinary' ) then
							type_name + '(' + convert( varchar, prec ) + ')'
						else
							type_name
						end
				from #t1
				where #t1.table_name = @table_name 
				order by ordinal_position
		
		-- print the header
		select @str = '** FILE  : usp_' + @table_name + 'Add.sql'
		print '/*=========================================================================================='
		print '**'
		print @str
		print '** Author: '
		print '**'
		print '**=========================================================================================='
		print '**'
		print '** (c) The contents of this file , and of any file or document derived from it , are copyright '
		print '** to IRIS. Unlicensed alteration, change or copying in any form, '
		print '** whether written, by photocopy, by print or by any other methods of reproduction is '
		print '** strictly prohibited.'
		print '**'
		print '**=========================================================================================='
		print '**'
		print '** PURPOSE OF FILE :'
		print '**'
		print '** DETAILED DESCRIPTION OF FUNCTIONALITY'
		print '** LIST OF PARAMETERS USED'
		print '** In	: '

		-- generate input parameter list comment
		declare cur_param cursor for
			select COMMENT = '** ' + char(9) + '@' + column_name + ' ' + type_name
				from #t2
				where type_name not in ( 'timestamp' )
		open cur_param
		fetch cur_param
			into @str
		while ( @@fetch_status = 0 )
		begin
			print @str
			fetch cur_param
				into @str
		end
		close cur_param
		deallocate cur_param

		--
		print '**'
		print '** LIST OF RETURN VALUES AND THEIR DEFINITIONS '
		print '** 0	:   Successful'
		print '**=========================================================================================*/'
		print ''
		select @str = "if exists( select * from sysobjects where id = object_id('dbo.usp_" + @table_name + "Add') and type = 'P ') "
		print @str
	    select @str = char(9) + "drop proc dbo.usp_" + @table_name + "Add"
		print @str
		print 'go'
		print ''
		select @str = 'create procedure usp_' + @table_name + "Add"
		print @str

		-- print the parameter list
		declare cur_param cursor for
			select COMMENT = char(9) + '@' + column_name + ' ' + type_name
				from #t2
				where type_name not in ( 'timestamp' )

		open cur_param
		fetch cur_param
			into @str
		while ( @@fetch_status = 0 )
		begin
			select @str2 = @str
			fetch cur_param
				into @str
			if ( @@fetch_status = 0 )
			begin
				select @str2 = @str2 + ', '
			end
			print @str2
		end
		close cur_param
		deallocate cur_param

		print 'as'
		print '	set nocount on'
		print '	set ansi_defaults on'
	
		print '	declare @error_number int,'
		print '		@row_count int'
		print ''

		-- now the actual statement
		select @str =  char(9) + 'insert into ' + @table_name + '('
		print @str
		declare cur_param cursor for
			select column_name 
				from #t2
		open cur_param
		fetch cur_param
			into @str
		while ( @@fetch_status = 0 )
		begin
			select @str2 = char(9) + char(9) + char(9) + @str
			fetch cur_param
				into @str
			if ( @@fetch_status = 0 )
			begin
				select @str2 = @str2 + ', '
			end
			print @str2
		end
		select @str = char(9) + char(9) + ')'
		print @str
		close cur_param
		select @str = char(9) + char(9) + 'values ('
		print @str
		open cur_param
		fetch cur_param
			into @str
		while ( @@fetch_status = 0 )
		begin
			select @str2 = char(9) + char(9) + char(9) + '@' + @str
			fetch cur_param
				into @str
			if ( @@fetch_status = 0 )
			begin
				select @str2 = @str2 + ', '
			end
			print @str2
		end
		select @str = char(9) + char(9) + ')'
		print @str
		close cur_param
		deallocate cur_param

		-- now the error check
		print '	-- error checking'
		print '	select @error_number = @@error, '
		print '		@row_count = @@rowcount '
		print ''
		print '	if ( @error_number <> 0 ) or ( @row_count <> 1 )'
		print '	begin'
		print "		exec ErrorHandler @error_number, '" + @table_name + "', 'adding'"
		print '		return 1'
		print '	end'
		print ''
		print '	return 0'
		print ''
		print 'go'
		print ''
		print ''
		
		fetch cur_tables
			into @table_name
	end

	close cur_tables
	deallocate cur_tables
		
		
	-- error checking
	select @error_number = @@error, 
		@row_count = @@rowcount 


	-- return non-zero for error

	return 0 -- for no error

go