Has been answered before ...
/****** Object: StoredProcedure [dbo].[procUtils_GenerateInsertProc] Script Date: 03/20/2010 13:06:13 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[procUtils_GenerateInsertProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[procUtils_GenerateInsertProc]
GO
/****** Object: StoredProcedure [dbo].[procUtils_GenerateInsertProc] Script Date: 03/20/2010 13:06:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec procUtils_GenerateInsertProc 'Whatever'
--exec sp_HelpText procUtils_GenerateInsertProc
CREATE PROCEDURE [dbo].[procUtils_GenerateInsertProc]
@TableName [varchar](50)
WITH EXECUTE AS CALLER
AS
BEGIN -- proc start
SET NOCOUNT ON;
BEGIN TRY --begin try
--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name
--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'
--SELECT column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature' --SELECT * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Task'
--Decalre a variable to remember the position of the current delimiter
DECLARE @ProcName varchar(1000)
set @ProcName = '[dbo].[procGen_' + @TableName + '_Insert]'
DECLARE @CurrentDelimiterPositionVar INT
DECLARE @PkColumnName varchar(200)
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT
DECLARE @ColumnName varchar(300);
DECLARE @DataType varchar(50)
DECLARE @IsNullable bit
DECLARE @MaxLength INT
DECLARE @IsComputed BIT
set @IsComputed = 0
DECLARE @IsPrimaryKey BIT
set @IsPrimaryKey = 0
DECLARE @CODESTR VARCHAR(max)
--PRINT DROP PROCEDURE
set @CODESTR = ' '
--Declare the Table variable
DECLARE @ColumnNames TABLE
(
Number INT IDENTITY(1,1), --Auto incrementing Identity column
TableName varchar(300) , --the name of the table
ColumnName VARCHAR(300) , --The string value ,
DataType varchar(50) , --the datatype
IsNullable bit , --should we add =null in front
MaxLength INT , --VARCHAR(LENGHTi)
IsComputed bit , --whether or not this table is computed
IsPrimaryKey bit --whether or not this table is computed
)
--Populate the TABLE variable using some logic
-- SELECT * from INFORMATION_SCHEMA.COLUMNS
INSERT INTO @ColumnNames
(
TableName ,
ColumnName ,
DataType ,
IsNullable ,
MaxLength ,
IsComputed ,
IsPrimaryKey )
SELECT
TableName ,
ColumnName ,
DataType ,
IsNullable ,
MaxLength ,
IsComputed ,
IsPrimaryKey
from viewMeta_TableColumns
--debug where TableName = 'Whatever'
where TableName = @TableName
--SELECT column_name , Data_type , IsNullable , MaxLength
--from INFORMATION_SCHEMA.COLUMNS
--where TABLE_NAME=@TableName
--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1
--Determine the number of rows in the Table
SELECT @Count=max(Number) from @ColumnNames
--A variable to hold the currently selected value from the table
set @CODESTR = @CODESTR + 'IF OBJECT_ID(''' + @ProcName + ''') IS NOT NULL
BEGIN
DROP PROC ' + @ProcName + '
END
GO'
set @CODESTR = @CODESTR + '
/****** Object: StoredProcedure ' + @ProcName + '*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE ' + @ProcName + '
@CurUserSessionId [int] ,
@CurPageTypeId [int] ,
@MsgOut [varchar](200) OUTPUT ,
@DebugMsgOut [varchar](200) OUTPUT,
@Ret [int] OUTPUT ,' + CHAR(13)
--@COLUMN_NAME [DATA_TYPE] (MAX_LENGTH) =NULL ,
WHILE @CurrentDelimiterPositionVar <= @Count --1st loop
BEGIN
--Load current value from the Table
SELECT @ColumnName = ColumnName FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @MaxLength = MaxLength FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar
set @IsNullable = ( select IsNullable FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar )
set @IsPrimaryKey = ( select IsPrimaryKey FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar )
if ( @DataType = 'timestamp' or @IsComputed = 1)
begin
set @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1
continue
end
set @CODESTR = @CODESTR + '@' + @ColumnName + ' ['+ @DataType + '] '
--ADD THE (200)
IF @MaxLength IS NOT NULL
BEGIN --IF @MaxLength IS NOT NULL
--xml
if @DataType <> 'xml' and @DataType <> 'sql_variant' and
@DataType <> 'text' and @DataType <> 'ntext' and @DataType <> 'image' and
@DataType <> 'hierarchyid' and @DataType <> 'bit' and @DataType <> 'varbinary' and
@DataType <> 'int' and @DataType <> 'uniqueidentifier' and @DataType <> 'tinyint' and
@DataType <> 'timestamp' and @DataType <> 'uniqueidentifier' and @DataType <> 'smallint' and
@DataType <> 'bigint' and @DataType <> 'smallmoney' and @DataType <> 'money' and
@DataType <> 'real' and @DataType <> 'smalldatetime' and @DataType <> 'datetime'
begin --those with()
if @MaxLength <> -1
SET @CODESTR = @CODESTR + '(' + CONVERT(VARCHAR , @MaxLength ) + ')'
else
SET @CODESTR = @CODESTR + '(max)'
end --those with(200)
else
begin
SET @CODESTR = @CODESTR --DO NOTHING
end
END --IF @MaxLength IS NOT NULL
IF @IsNullable = 1
SET @CODESTR = + @CODESTR + ' = NULL '
if @IsPrimaryKey = 1
SET @CODESTR = @CODESTR + ' OUTPUT '
if @CurrentDelimiterPositionVar <> @Count
SET @CODESTR = @CODESTR + ','
--DEBUGGING
--set @CODESTR = @CODESTR + '@ColumnName - ' + @ColumnName
--set @CODESTR = @CODESTR + '@DataType - ' + @DataType
--set @CODESTR = @CODESTR + '@IsNullable - ' + @IsNullable
--set @CODESTR = @CODESTR + '@MaxLength - ' + CONVERT ( VARCHAR , @MaxLength )
set @CODESTR = @CODESTR + CHAR(13)
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1
END
SET @CODESTR = @CODESTR + '
WITH EXECUTE AS CALLER
AS
BEGIN -- proc start
SET NOCOUNT ON;
BEGIN TRY --begin try
--
set @Ret = 1 --assume false from the beginning
declare @MsgKey [nvarchar](max)
declare @MsgTxt [nvarchar](max)
exec procUtils_GetMsgTxtByKeyAndUserSessionId
@UserSessionId =2 ,
@MsgKey = ''MsgOkTheAddingOfItemIsOk'' ,
@MsgTxt = ''''
set @MsgOut = replace (@MsgTxt , ''{0}'' , ''' + @TableName + ''' )
declare @thisProcName varchar(300)
set @thisProcName= ( SELECT OBJECT_NAME(@@PROCID))
'
SET @CurrentDelimiterPositionVar = 1 --START LOOP AGAIN
set @CODESTR = @CODESTR + '
--Action !!!
INSERT INTO [dbo].[' + @TableName + ']( ' + CHAR(13)
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count --2nd loop
BEGIN
--Load current value from the Table
SELECT @ColumnName = ColumnName FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @MaxLength = MaxLength FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar
set @IsNullable = ( select IsNullable FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar )
set @IsPrimaryKey = ( select IsPrimaryKey FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar )
if @IsPrimaryKey = 1
begin -- the primary key
set @PkColumnName = @ColumnName
end --the primary key
if ( @DataType = 'timestamp' or @IsComputed = 1 or @IsPrimaryKey = 1 )
begin
set @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1
continue
end
--select
if @CurrentDelimiterPositionVar <= @Count
BEGIN
set @CODESTR = @CODESTR + '[' + @ColumnName + ']' --null the codestring var
if @CurrentDelimiterPositionVar <> @Count
set @CODESTR = @CODESTR + ', --type of ' + @DataType + CHAR(13) --WITH COMMA
ELSE
set @CODESTR = @CODESTR + ' --type of ' + @DataType + CHAR(13) --NO COMMA
END -- IF SHOULD PRINT COLUMN
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END --eof while 2
set @CODESTR = @CODESTR + ') VALUES ( '
--AND START ALL OVER AGAIN
SET @CurrentDelimiterPositionVar = 1
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count --WHILE 3
BEGIN
--Load current value from the Table
SELECT @ColumnName = ColumnName FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @MaxLength = MaxLength FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar
set @IsNullable = ( select IsNullable FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar )
set @IsPrimaryKey = ( select IsPrimaryKey FROM @ColumnNames
WHERE Number = @CurrentDelimiterPositionVar )
if ( @DataType = 'timestamp' or @IsComputed = 1 or @IsPrimaryKey = 1)
begin
set @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1
continue
end
set @CODESTR = @CODESTR + '@' + @ColumnName
if @CurrentDelimiterPositionVar <= @Count
BEGIN
IF @CurrentDelimiterPositionVar <> @Count
set @CODESTR = @CODESTR + ' , --type of ' + @DataType --all others with comma
else
set @CODESTR = @CODESTR + ' --type of ' + @DataType --the last one without comma
END -- IF SHOULD NOT PRINT COLUMN
--increase the counter
set @CODESTR = @CODESTR + CHAR(13)
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END
set nocount off
SET @CODESTR = @CODESTR + ')
SET @' + @pkColumnName + ' = @@IDENTITY
set @Ret = @@ERROR
set @DebugMsgOut = ''TODO:REMOVE INSERT OK ''
END TRY
BEGIN CATCH
EXEC @ret = [dbo].[procUtils_GetMsgTxtByKeyAndUserSessionId]
@UserSessionId = 2,
@MsgKey = N''ErrorMsgMenuRetrievalFailed'',
@MsgTxt = @MsgOut OUTPUT
set @ret = 1
set @msgOut = @MsgTxt
set @debugMsgOut = '' Error number: '' + CAST(ERROR_NUMBER() AS varchar(100)) +
''Error message: '' + ERROR_MESSAGE() + ''Error severity: '' +
CAST(ERROR_SEVERITY() AS varchar(10)) +
''Error state: '' + CAST(ERROR_STATE() AS varchar(100)) +
''XACT_STATE: '' + CAST(XACT_STATE() AS varchar(100))
-- record the error in the database
set @debugMsgOut = @debugMsgOut + @msgOut
INSERT INTO [dbo].[LogStore] ( [Date],[Thread],[Level],[Logger],[Message])
values ( getdate() , N''8'', N''DEBUG'', @thisProcName , @debugMsgOut )
END CATCH
END --procedure end
GO
'
print @codestr
END TRY --end try
BEGIN CATCH
print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +
CAST(ERROR_SEVERITY() AS varchar(1000)) +
'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +
'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))
END CATCH
END --procedure end
--USE [Gaf]
--GO
--SELECT NAME FROM SYS.tables where name like '%Msg%'
--EXEC [dbo].[procUtils_GenerateInsertProc] @TableName = N'Whatever'
GO
