Insert Statements Creator
Method 1
CREATE Function dbo.udf_GetNoOfOccurence
(
@pSourceString VarChar(Max),
@pSearchString VarChar(Max)
)Returns Int
As
Begin
Declare @lCount Int
Select @pSourceString = @pSourceString + @pSearchString, @lCount = 0
While PatIndex('%'+ @pSearchString + '%', @pSourceString) > 0
Begin
Select @lCount = @lCount + 1
Select @pSourceString = SubString(@pSourceString, PatIndex('%'+ @pSearchString + '%', @pSourceString) + 1, Len(@pSourceString))
End
Return @lCount -1
End
Select dbo.udf_GetNoOfOccurence('R,A,B,C', ',')
*/
CREATE Procedure dbo.usp_GInsertStatement
(
@pTableName VarChar(500),
@pValueList VarChar(Max),
@pColumnList VarChar(Max) = Null,
@pIdentityInsert Bit = 0,
@pDebug Bit = 0,
@pErrorFlag Char(1) Output,
@pErrorDesp VarChar(100) Output
)
As
Begin
Set NoCount On
/*
--To Execute
Declare @lTableName VarChar(500),
@lValueList VarChar(Max),
@lColumnList VarChar(Max),
@lIdentityInsert Bit,
@lDebug Bit,
@lErrorFlag Char(1),
@lErrorDesp VarChar(100)
Select @lTableName ='State',
@lValueList ='ZZ,Z,Z', --'Z, YY' --'YY, YY, 7'
@lColumnList ='StateID, StateCode, StateName', --'CustomerID, ContactName, UniverstalID'
@lIdentityInsert =0,
@lDebug = 0
Exec usp_GInsertStatement @lTableName, @lValueList, @lColumnList, @lIdentityInsert, @lDebug, @lErrorFlag Output, @lErrorDesp Output
Select @lDebug DebugFlag, @lErrorFlag ErrorFlag, @lErrorDesp ErrorDescription
*/
Declare @lIdentityColumn VarChar(100),
@lNoOfColumns Int,
@lNoOfValues Int,
@lColumnID Int,
@lColumnDataType VarChar(500),
@lColumnList VarChar(Max),
@lValueList VarChar(Max),
@lColumnName VarChar(500),
@lValue VarChar(500),
@lIdInsertStatement VarChar(500)
--To Check TableName and ValueList is not blank.
If (lTrim(rTrim(@pTableName)) = '' Or lTrim(rTrim(@pValueList)) = '' )
Begin
Select @pErrorFlag = 'Y', @pErrorDesp = 'TableName/ ValueList can''t be blank.'
GoTo ErrorHandler
End
Select @pTableName = QuoteName(@pTableName)
--To Check Identity_Insert is On/Off
Select @lIdentityColumn = ''
Select @lIdentityColumn = Name From sys.identity_columns Where object_id = Object_ID(@pTableName)
If @lIdentityColumn <> ''
Begin
If @pIdentityInsert = 1 --Or PatIndex('%' + @lIdentityColumn + '%', @lColumnList) > 0
Begin
Select @lIdInsertStatement = 'SET IDENTITY_INSERT ' + @pTableName + ' On'
End
Else
Begin
Select @lIdInsertStatement = 'SET IDENTITY_INSERT ' + @pTableName + ' Off'
End
End
If @pDebug = 1
Select @lIdentityColumn 'IdentityColumn', @lIdInsertStatement '@lIdInsertStatement'
--To Replace single-code into double single-code from valuelist or columnlist, if exist
Select @pValueList = Replace(lTrim(rTrim(@pValueList)), '''', ''''''),
@pColumnList = Replace(lTrim(rTrim(IsNull(@pColumnList, ''))), '''', '''''')
--To Get the ColumnList, if user has not provide
If @pColumnList = ''
Begin
If (@lIdentityColumn <> '' OR @pIdentityInsert = 0)
Begin
Select @pColumnList = @pColumnList + QuoteName(Name) + ', '
From SysColumns
Where ID = Object_ID(@pTableName)
And [Name] <> @lIdentityColumn
Order By ColID
End
Else
Begin
Select @pColumnList = @pColumnList + QuoteName(Name) + ', '
From SysColumns
Where ID = Object_ID(@pTableName)
Order By ColID
End
Select @pColumnList = SubString(lTrim(rTrim(@pColumnList)), 1, Len(lTrim(rTrim(@pColumnList))) -1)
End
/*
Else
Begin
If (@lIdentityColumn <> '' And @pIdentityInsert = 0 And PatIndex('%' + @lIdentityColumn + '%', @pColumnList) >0)
Begin
Select 'Rahul', @pColumnList, @lIdentityColumn
Select @pColumnList = ' ' + @pColumnList
Select @pColumnList = SubString(@pColumnList, 1, (PatIndex('%' + @lIdentityColumn + '%', @pColumnList) -4)) + SubString(@pColumnList, (PatIndex('%' + @lIdentityColumn + '%', @pColumnList) + Len(@lIdentityColumn) + 1), Len(@pColumnList))
End
End
*/
--Patch For Add/Remove Identity Column from ColumnList Or ValueList according to @pIdentityInsert
--===============================================================================================
Declare @lMaxValue Int,
@lDelPos Int,
@lTemp Int,
@lTempValueList VarChar(Max),
@lTempColumnList VarChar(Max)
Declare @lTblMaxValue Table
(
MaxValue Int
)
If (@lIdentityColumn <> '' And @pIdentityInsert = 0 And PatIndex('%' + @lIdentityColumn + '%', @pColumnList)>0)
Begin
--If Identity column exist in columnlist and identityinsert is set to 0, Remove identity column from columnlist and valuelist
--Remove QuoteName From ColumnList
--========================================
Select @lTempColumnList = '', @pColumnList = @pColumnList + ', '
While PatIndex('%,%', @pColumnList)> 0
Begin
If PatIndex('%]%', SubString(lTrim(rTrim(@pColumnList)), 1, (PatIndex('%,%', lTrim(rTrim(@pColumnList))) -1) )) > 0
Select @lTempColumnList = @lTempColumnList + SubString(SubString(lTrim(rTrim(@pColumnList)), 1, (PatIndex('%,%', lTrim(rTrim(@pColumnList))) -1) ), 2, (Len(SubString(lTrim(rTrim(@pColumnList)), 1, (PatIndex('%,%', lTrim(rTrim(@pColumnList))) -1) )) -2
)) + ', '
Select @pColumnList = SubString(@pColumnList, PatIndex('%,%', @pColumnList) + 1, Len(@pColumnList))
End
Select @pColumnList = SubString(@lTempColumnList, 1, Len(@lTempColumnList) -1)
--========================================
--Remove Value of ID Column From ValueList
--========================================
Select @lTemp = 1, @lTempValueList = ''
Select @lDelPos = dbo.udf_GetNoOfOccurence(SubString(@pColumnList, 1, (PatIndex('%' + @lIdentityColumn + '%', @pColumnList) - 1)), ',')
While @lTemp <= @lDelPos
Begin
Select @lTempValueList = @lTempValueList + SubString(@pValueList, 1, PatIndex('%,%', @pValueList))
Select @pValueList = SubString(@pValueList, PatIndex('%,%', @pValueList) + 1, Len(@pValueList))
Select @lTemp = @lTemp + 1
End
If PatIndex('%,%', @pValueList) > 0
Begin
Select @lTempValueList = @lTempValueList + SubString(@pValueList, PatIndex('%,%', @pValueList) + 1, Len(@pValueList))
End
Else
Begin
Select @lTempValueList = Reverse(lTrim(rTrim(@lTempValueList)))
Select @lTempValueList = Reverse(SubString(@lTempValueList, PatIndex('%,%', @lTempValueList) + 1, Len(@lTempValueList)))
End
Select @pValueList = @lTempValueList
--========================================
--Remove IdentityColumn from ColumnList
--=====================================
Select @pColumnList = SubString(@pColumnList, 1, (PatIndex('%' + @lIdentityColumn + '%', @pColumnList) - 1)) +
SubString(@pColumnList, (PatIndex('%' + @lIdentityColumn + '%', @pColumnList) + Len(@lIdentityColumn) + 1), (Len(@pColumnList) -1))
If SubString(lTrim(rTrim(Reverse(@pColumnList))), 1,1) = ','
Begin
Select @pColumnList = Reverse(SubString(lTrim(rTrim(Reverse(@pColumnList))), 2,Len(lTrim(rTrim(Reverse(@pColumnList))))))
End
Else
Begin
Select @pColumnList = lTrim(rTrim(@pColumnList))
End
--=====================================
--Add QuoteName in ColumnList
--=====================================
Select @lTempColumnList = '', @pColumnList = @pColumnList + ', '
While PatIndex('%,%', @pColumnList)> 0
Begin
Select @lTempColumnList = @lTempColumnList + QuoteName(lTrim(rTrim(SubString( @pColumnList, 1, (PatIndex('%,%', @pColumnList) -1))))) + ', '
Select @pColumnList = SubString(@pColumnList, PatIndex('%,%', @pColumnList) + 1, Len(@pColumnList))
End
Select @pColumnList = SubString(@lTempColumnList, 1, Len(@lTempColumnList) -2)
--=====================================
End
Else If (@lIdentityColumn <> '' And @pIdentityInsert = 1 And PatIndex('%' + @lIdentityColumn + '%', @pColumnList)=0)
Begin
--If Identity column not exist in columnlist and identityinsert is set to 1, Add identity column from columnlist and valuelist
Select @pColumnList = @pColumnList + ', ' + QuoteName(@lIdentityColumn)
Insert Into @lTblMaxValue(MaxValue)
Exec ('Select Case When Max(QuoteName(' + @lIdentityColumn + ')) Is Null Then 0 Else Max(QuoteName(' + @lIdentityColumn + ')) + 1 End From ' + @pTableName)
Select @lMaxValue = MaxValue
From @lTblMaxValue
Select @pValueList = @pValueList + ', ' + Cast(@lMaxValue As VarChar)
Delete From @lTblMaxValue
End
--===============================================================================================
If @pDebug = 1
Select @pColumnList ColumnList, @pValueList ValueList, dbo.udf_GetNoOfOccurence(@pColumnList, ','), dbo.udf_GetNoOfOccurence(@pValueList, ',')
--To Check Column name or number of supplied values does not match table definition
Select @lNoOfColumns = 0, @lNoOfValues = 0
If dbo.udf_GetNoOfOccurence(@pColumnList, ',') <> dbo.udf_GetNoOfOccurence(@pValueList, ',')
Begin
Select @pErrorFlag = 'Y', @pErrorDesp = 'Column name or number of supplied values does not match table definition.'
GoTo ErrorHandler
End
--To Update the ValuList accourding to Insert statement
Select @lColumnDataType = '', @lColumnList = @pColumnList + ',', @lValueList = @pValueList + ',', @lColumnName = '', @pValueList = ''
While PatIndex('%,%',@lColumnList) > 0
Begin
Select @lColumnName = SubString(@lColumnList, 1, (PatIndex('%,%', @lColumnList) -1))
Select @lColumnList = SubString(@lColumnList, (PatIndex('%,%', @lColumnList) +1), Len(@lColumnList))
IF PatIndex('%]%', @lColumnName) > 0
Begin
Select @lColumnName = SubString(lTrim(rTrim(@lColumnName)), 2, Len(lTrim(rTrim(@lColumnName))) -2)
If @pDebug = 1
Select @lColumnName 'ColumnName'
End
Select @lValue = SubString(@lValueList, 1, (PatIndex('%,%', @lValueList) -1))
Select @lValueList = SubString(@lValueList, (PatIndex('%,%', @lValueList) +1), Len(@lValueList))
Select @lColumnDataType = lTrim(rTrim(ST.[Name]))
From SysColumns SC
Inner Join SysTypes ST On ST.XUsertype = SC.Xtype
Where Id = Object_ID(lTrim(rTrim(@pTableName)))
And SC.[Name] = lTrim(rTrim(@lColumnName))
If @pDebug = 1
Select @lColumnDataType DataType, lTrim(rTrim(@lColumnName)) ColumnName, lTrim(rTrim(@pTableName)) TableName
Select @pValueList = @pValueList +
CASE
WHEN @lColumnDataType IN ('char','varchar','nchar','nvarchar')
THEN
--'REPLACE(RTRIM(' + @lValue + '),'''''''','''''''''''')'
'REPLACE(RTRIM(''' + @lValue + '''),'''''''','''''''''''')'
WHEN @lColumnDataType IN ('datetime','smalldatetime')
THEN
'RTRIM(CONVERT(char,''' + @lValue + ''',112))'
WHEN @lColumnDataType IN ('uniqueidentifier')
THEN
'REPLACE(CONVERT(char(255),RTRIM(' + @lValue + ')),'''''''','''''''''''')'
WHEN @lColumnDataType IN ('text','ntext')
THEN
'REPLACE(CONVERT(char(8000),''' + @lValue + '''),'''''''','''''''''''')'
WHEN @lColumnDataType IN ('binary','varbinary')
THEN
'RTRIM(CONVERT(char,''' + 'CONVERT(int,''' + @lValue + ''')''))'
WHEN @lColumnDataType IN ('float','real','money','smallmoney')
THEN
'LTRIM(RTRIM(' + 'CONVERT(char, ''' + @lValue + ''',2)' + '))'
ELSE
'LTRIM(RTRIM(' + 'CONVERT(char, ''' + @lValue + ''')' + '))'
END +
', '
End
Select @pValueList = SubString(@pValueList, 1, Len(@pValueList) -2)
If @pDebug = 1
Select @lIdInsertStatement, 'Insert Into ' + @pTableName + ' (' + @pColumnList + ') Values( ' + @pValueList + ') '
--To Execute Insert Statement
If @lIdentityColumn <> ''
Begin
Exec(
@lIdInsertStatement + ' ' +
'Insert Into ' + @pTableName + ' (' + @pColumnList + ') Values( ' + @pValueList + ') '
)
If @@Error > 0
Begin
Select @pErrorFlag = 'Y', @pErrorDesp = 'Error on Executing Insert Statement.'
GoTo ErrorHandler
End
End
Else
Begin
Exec ('Insert Into ' + @pTableName + ' (' + @pColumnList + ') Values( ' + @pValueList + ') ')
If @@Error > 0
Begin
Select @pErrorFlag = 'Y', @pErrorDesp = 'Error on Executing Insert Statement.'
GoTo ErrorHandler
End
End
Select @pErrorFlag = 'N', @pErrorDesp = ''
Set NoCount Off
Return
ErrorHandler:
Set NoCount Off
End
No comments:
Post a Comment