USE master
go
-- ** BEGIN ** 
-- If Shiloh, then create as system SP for the ANSI views to function correctly
IF CHARINDEX( '8.00' , @@version ) > 0
BEGIN
        EXEC sp_configure 'allow updates' , 1
        EXEC sp_MS_upd_sysobj_category 1
END
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_genstmt'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_genstmt
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_genstmt'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_genstmt >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_genstmt >>>'
END
go
CREATE PROCEDURE sp_genstmt
(
@table_name varchar(30),
@stmt_type char(1) = ''
)
--WITH ENCRYPTION
AS
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	11 October 1999						*/
/*	Description:	Create this SP in the database where the tables are 	*/
/*			present. This SP can be used to generate INSERT stmts.	*/
/*			with the variable list, INSERT statement itself. More	*/
/*			options to be added later. Just saves typing time and	*/
/*			mistakes.						*/
/********************************************************************************/
/*	Resources  :	http://www.umachandar.com/resources.htm         	*/
/********************************************************************************/
select stmt from
(
select '@' + column_name + ' ' + data_type +
	case when charindex('char', data_type) > 0
		then '(' + ltrim(str(character_octet_length)) + ')'
		else ''
	end +
	case when ordinal_position < (select max(ordinal_position)
					from information_schema.columns
					where table_name = @table_name)
		then ','
		else ''
	end as stmt,
	(select max(ordinal_position) from information_schema.columns
		where table_name = @table_name) + 3 + ordinal_position as pos
from information_schema.columns where table_name = @table_name
) as a

select stmt from
(
select 'Insert Into ' + @table_name + '(' as stmt,
	(select min(ordinal_position) from information_schema.columns
	where table_name = @table_name) + -1 as pos
union
select column_name +
	case when ordinal_position < (select max(ordinal_position)
					from information_schema.columns
					where table_name = @table_name)
		then ','
		else ''
	end as stmt,
	ordinal_position as pos
from information_schema.columns where table_name = @table_name
union
select ')' as stmt, (select max(ordinal_position) from information_schema.columns
			where table_name = @table_name) + 1 as pos
union
select 'Values(' as stmt, (select max(ordinal_position) from information_schema.columns
				where table_name = @table_name) + 2 as pos
union
select '@' + column_name +
	case when ordinal_position < (select max(ordinal_position)
					from information_schema.columns
					where table_name = @table_name)
		then ','
		else ''
	end as stmt,
	(select max(ordinal_position) from information_schema.columns
	where table_name = @table_name) + 3 + ordinal_position as pos
from information_schema.columns where table_name = @table_name
union
select ')' as stmt, 2*(select max(ordinal_position) from information_schema.columns
			where table_name = @table_name) + 4 as pos
) as a
order by pos
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_genstmt'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.sp_genstmt To Public
    PRINT '<<< CREATED PROCEDURE dbo.sp_genstmt >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_genstmt >>>'
go
-- ** END ** 
IF CHARINDEX( '8.00' , @@version ) > 0 EXEC sp_MS_upd_sysobj_category 2
go