EDIT - Adding Dynamic SQLProcedure to recreate:
In SSSM :
I am just porting this to a cloud database. Please understand that this was not written by me and has been around for a long time now.
EXEC [spAddEditFormItemView] 30032,'VP_BENCHMARKING_V05'
Commands completed successfully.
Completion time: 2023-11-27T16:11:18.1762097-05:00
In the trigger it forces a rollback with a horrible error message.
CREATEALTER PROCEDURE [dbo].[spAddEditFormItemView] (
@FormItemID int,
@Schema nvarchar(100)
)
AS
DECLARE @Existing bit
, @FormItemFriendlyName varchar(200)
, @FormItemTypeID int
, @ObjectType varchar(100)
, @ObjectTypeID int
, @DataTypeID int
, @FormItemSystemName varchar(200)
, @SystemName varchar(200)
, @SourceType varchar(50)
, @TableName varchar(100)
, @DataColumn varchar(50)
, @DropdownSQL varchar(2000)
, @ObjectTableName varchar(100)
, @ObjectKeyField varchar(100)
, @FieldValue varchar(50)
SELECT @Existing = isnull(Existing, 0)
, @FormItemFriendlyName = FormItemFriendlyName
, @FormItemSystemName = FormItemSystemName
, @FormItemTypeID = FormItemTypeID
, @DataTypeID = DataTypeID
, @ObjectTypeID = ObjectTypeID
, @FormItemTypeID = FormItemTypeID
, @SourceType = SourceType
, @DropdownSQL = FormItemDropdownSource
FROM tblFormItems
WHERE FormItemID = @FormItemID
IF @ObjectTypeID = 4
BEGIN
SET @ObjectTableName = 'tblLeaseAbstract'
SET @ObjectKeyField = 'LeaseAbstractID'
END
ELSE
BEGIN
SELECT @ObjectTableName = TableName
, @ObjectKeyField = KeyField
FROM tblObjectType
WHERE ObjectTypeID = @ObjectTypeID
END
SELECT @DataColumn = Case when @DataTypeID IN ( 6, 5, 206 )
then 'FormDataFloat'
when @DataTypeID = 7 then 'FormDataDate'
when @DataTypeID = 200 then 'FormDataText'
when @DataTypeID = 201
then 'FormDataVarChar400'
when @DataTypeID = 202 then 'FormDataVarChar50'
when @DataTypeID IN ( 2, 3 ) then 'FormDataInt'
else 'FormDataText'
end
SELECT @TableName = DynamicFormDataTable
FROM tblObjectType
WHERE ObjectTypeID = @ObjectTypeID
--create view if it is not there...must know objectType and formItemType first
if (@Existing = 0
and @FormItemFriendlyName <> ''
and @FormItemTypeID <> 8 --and @FormItemTypeID <> 9 --Calculated=9
)
/*Modified 2/17/2011 to create form item views for existing listbox form items*/
OR (@FormItemTypeID =1 AND @SourceType = 'sql')
BEGIN
select @ObjectType = ObjectType
from tblObjectType
where objectTypeID = @ObjectTypeID
select @SystemName = REPLACE(@FormItemSystemName, ' ', '')
select @FormItemSystemName = 'vwFormItem_'
+ cast(@ObjectTypeID as varchar) + '_'
+ REPLACE(@FormItemSystemName, ' ', '')
if not ( exists ( select *
from dbo.sysobjects
where id = object_id(@FormItemSystemName)
and OBJECTPROPERTY(id, 'IsView') = 1 ) )
BEGIN
IF ( ( @FormItemTypeID = 1
OR @FormItemTypeID = 5
)
and @SourceType = 'tbldropdown'
)
BEGIN
EXEC
( 'CREATE VIEW '+@Schema+'.' + @FormItemSystemName + '
AS
SELECT ObjectID
, tblDropdownValue.Dropdownvalue as ['
+ @FormItemFriendlyName + ']
, tblDropdownValue.DropdownDisplay as ['
+ @FormItemFriendlyName + 'Display]
FROM tblDropdownValue
inner join ' + @TableName
+ ' fd on fd.DropdownValueID = tblDropdownValue.DropdownValueID
WHERE FormItemID = ' + @FormItemID
)
END
ELSE
IF ( @FormItemTypeID = 1
AND @SourceType = 'sql'
AND ( @DropdownSQL LIKE 'SELECT DropdownValueID%'
OR @DropdownSQL LIKE 'SELECT tblDropdownValue.DropdownValueID%'
)
)
BEGIN
EXEC
( 'CREATE VIEW '+@Schema+'.' + @FormItemSystemName
+ '
AS
SELECT ObjectID
, tblDropdownValue.DropdownDisplay AS ['
+ @FormItemFriendlyName + ']
, tblDropdownValue.DropdownValue AS ['
+ @FormItemFriendlyName + 'Value]
FROM ' + @TableName
+ ' LEFT JOIN tblDropdownValue ON tblDropdownValue.DropdownValueID = '
+ @TableName + '.' + @DataColumn
+ ' WHERE FormItemID = ' + @FormItemID
)
END
ELSE
IF ( @FormItemTypeID = 18 )
BEGIN
EXEC
( 'CREATE VIEW '+@Schema+'.'
+ @FormItemSystemName + '
AS
SELECT
ObjectID
, dbo.udfGetMultiSelectAsTextValues(' + @DataColumn
+ ')as [' + @SystemName + ']
, ' + @DataColumn + ' as [' + @SystemName + 'Values]
FROM ' + @TableName + '
WHERE FormItemID = ' + @FormItemID
)
END
ELSE
IF ( @FormItemTypeID = 10 )
BEGIN
EXEC
( 'CREATE VIEW '+@Schema+'.'
+ @FormItemSystemName + '
as
SELECT ObjectID,
CONVERT(varchar(8000),Clause) as [' + @SystemName
+ '],
SourceDocument as [' + @FormItemFriendlyName + ' Doc],
SourceSection as [' + @FormItemFriendlyName + ' Sec],
SourcePage as [' + @FormItemFriendlyName + ' Page]
FROM tblClauses
WHERE FormItemID = ' + @FormItemID
)
END
ELSE
IF ( @FormItemTypeID = 9 )
BEGIN
IF @DataColumn IN ( 'FormDataText', 'FormDataVarChar50', 'FormDataVarChar400' )
BEGIN
SET @FieldValue = '''Calculation - Update Form Item View'''
END
ELSE
IF @DataColumn IN (
'FormDataFloat',
'FormDataInt' )
BEGIN
SET @FieldValue = '99999999'
END
ELSE
BEGIN
SET @FieldValue = '''1/1/1900'''
END
EXEC
( 'CREATE VIEW '+@Schema+'.'
+ @FormItemSystemName + '
AS
SELECT ' + @ObjectKeyField + ' AS ObjectID ' + ', '
+ @FieldValue + ' AS ['
+ @SystemName + ']
FROM ' + @ObjectTableName
)
END
ELSE
BEGIN
EXEC
( 'CREATE VIEW '+@Schema+'.'
+ @FormItemSystemName + '
AS
SELECT
ObjectID
, ' + @DataColumn + ' as [' + @SystemName + ']
FROM ' + @TableName + '
WHERE FormItemID = ' + @FormItemID
)
END
END
END
AS
DECLARE @SQL NVARCHAR(MAX) = 'e2e2e2e2e'
BEGIN TRY
EXEC (@SQLCommand)
END TRY
BEGIN CATCH
DECLARE @X INT
END CATCH
