ALTER 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'
--Declare the Table variable
DECLARE @ColNames TABLE
(
Number INT IDENTITY(1,1), --Auto incrementing Identity column
ColName VARCHAR(300) , --The string value ,
DataType varchar(50) , --the datatype
IS_NULLABLE nvarchar(5) , --should we add =null in front
CHARACTER_MAXIMUM_LENGTH INT
)
--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT
DECLARE @PkColName varchar(200)
DECLARE @ProcName varchar(1000)
set @ProcName = '[dbo].[procGen_' + @TableName + '_Insert]'
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT
--Populate the TABLE variable using some logic
-- SELECT * from INFORMATION_SCHEMA.COLUMNS
INSERT INTO @ColNames
SELECT column_name , Data_type , IS_NULLABLE , CHARACTER_MAXIMUM_LENGTH
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 @ColNames
--A variable to hold the currently selected value from the table
DECLARE @ColName varchar(300);
DECLARE @DataType varchar(50)
DECLARE @IS_NULLABLE VARCHAR(5)
DECLARE @CHARACTER_MAXIMUM_LENGTH INT
DECLARE @CODESTR VARCHAR(1000)
--PRINT DROP PROCEDURE
PRINT 'IF OBJECT_ID(''' + @ProcName + ''') IS NOT NULL BEGIN
DROP PROC ' + @ProcName + '
END
GO'
print '
/****** Object: StoredProcedure ' + @ProcName + '*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE ' + @ProcName + '
@CurUserSessionId [int] ,
@CurPageTypeId [int] ,
@MsgOut [varchar](200) ,
@DebugMsgOut [varchar](200) ,
@ret [int] OUTPUT ,'
--@COLUMN_NAME [DATA_TYPE] (MAX_LENGTH) =NULL ,
WHILE @CurrentDelimiterPositionVar <= @Count --1st loop
BEGIN
--Load current value from the Table
SELECT @ColName = ColName FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @IS_NULLABLE = IS_NULLABLE FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @CHARACTER_MAXIMUM_LENGTH = CHARACTER_MAXIMUM_LENGTH FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
SET @CODESTR = '@' + @ColName + ' ['+ @DataType + '] '
--ADD THE (200)
IF @CHARACTER_MAXIMUM_LENGTH IS NOT NULL
BEGIN --IF @CHARACTER_MAXIMUM_LENGTH 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 != 'binary' and @DataType != 'varbinary'
begin --those with()
if @CHARACTER_MAXIMUM_LENGTH != -1
SET @CODESTR =
@CODESTR + '(' + CONVERT(VARCHAR , @CHARACTER_MAXIMUM_LENGTH ) + ')' else
SET @CODESTR = @CODESTR + '(max)'
end --those with(200)
else
begin
SET @CODESTR = @CODESTR
end
END --IF @CHARACTER_MAXIMUM_LENGTH IS NOT NULL
IF @IS_NULLABLE = 'YES'
SET @CODESTR = + @CODESTR + ' = NULL '
if @CurrentDelimiterPositionVar = 1
SET @CODESTR = @CODESTR + ' OUTPUT '
if @CurrentDelimiterPositionVar != @Count
SET @CODESTR = @CODESTR + ','
IF @DataType != 'timestamp'
PRINT @CODESTR
--DEBUGGING
--PRINT '@ColName - ' + @ColName
--PRINT '@DataType - ' + @DataType
--PRINT '@IS_NULLABLE - ' + @IS_NULLABLE
--PRINT '@CHARACTER_MAXIMUM_LENGTH - ' + CONVERT ( VARCHAR , @CHARACTER_MAXIMUM_LENGTH )
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END
print '
AS
BEGIN -- proc start
SET NOCOUNT ON;
'
SET @CurrentDelimiterPositionVar = 1 --START LOOP AGAIN
print 'INSERT INTO [dbo].[' + @TableName + ']( '
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count --2nd loop
BEGIN
--Load current value from the Table
SELECT @ColName = ColName FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @IS_NULLABLE = IS_NULLABLE FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
if @CurrentDelimiterPositionVar = 1
begin -- the primary key
set @CODESTR = ''
set @PkColName = @ColName
end --the primary key
ELSE
set @CODESTR = '[' + @ColName + ']' --null the codestring var
if @CurrentDelimiterPositionVar < @Count and @CurrentDelimiterPositionVar <> 1
set @CODESTR = @CODESTR + ' , '
set @CODESTR = @CODESTR + '--type of ' + @DataType
if @DataType != 'timestamp'
print @CODESTR
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END
print ') VALUES ( '
--AND START ALL OVER AGAIN
SET @CurrentDelimiterPositionVar = 1
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count --1st loop
BEGIN
--Load current value from the Table
SELECT @ColName = ColName FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
if @CurrentDelimiterPositionVar = 1
begin --the primary key
set @CODESTR = ''
set @PkColName = @ColName
end --the primary key
ELSE
set @CODESTR = '@' + @ColName
--Process the current value
if @CurrentDelimiterPositionVar < @Count and @CurrentDelimiterPositionVar <> 1
set @CODESTR = @CODESTR + ' ,'
if @DataType != 'timestamp'
print @CODESTR
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END
set nocount off
PRINT ')
SET @' + @pkColName + ' = @@IDENTITY
SET @Ret = 0
END
GO
'
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 [GenApp]
--GO
--SELECT NAME FROM SYS.tables where name like '%Msg%'
--EXEC [dbo].[procUtils_GenerateInsertProc] @TableName = N'Task'
Posts
