I have the following:
------
WHILE PATINDEX('%,%',@.Columns)<> 0BEGIN
SELECT @.Separator_position = PATINDEX('%,%',@.Columns)
SELECT @.array_Value =LEFT(@.Columns, @.separator_position - 1)
SET @.FieldTypeID = (SELECT FieldTypeIDFROM [Form].[Fields]WHERE FieldID = (CAST(@.array_ValueAS INT)))
SET @.FieldName = (SELECT [Name]FROM [Form].[Fields]WHERE FieldID = @.array_Value)
print'arry value' +CONVERT(VarChar(500), @.array_value)
print'FieldTypeID: ' +CONVERT(VARCHAR(500), @.FieldTypeID)
PRINT'FieldName: ' + @.FieldNameBEGIN
IF @.FieldTypeID = 1OR @.FieldTypeID = 2OR @.FieldTypeID = 3OR @.FieldTypeID = 9OR @.FieldTypeID = 10OR @.FieldTypeID = 7
BEGIN
SET @.InnerItemSelect =' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[ItemDetailFieldRecords] IDFR WHERE IDFR.ItemDetailID = ID.ItemDetailID AND IDFR.FieldID = ' + @.array_Value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[TaskFieldRecords] TFR WHERE TFR.TaskID = T.TaskID AND TFR.FieldID = ' + @.array_Value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FieldTypeID = 4OR @.FieldTypeID = 8--DropDownList/RadioButtonlist
BEGIN
SET @.InnerItemSelect =' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[ItemDetailFieldListRecords] IDFLR ON FFLV.FieldListValueID = IDFLR.FieldListValueID WHERE IDFLR.ItemDetailID = ID.ItemDetailID AND FFLV.FIeldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[TaskFieldListRecords] TFLR ON FFLV.FieldListValueID = TFLR.FieldListValueID WHERE TFLR.TaskID = T.TaskID AND FFLV.FIeldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FieldTypeiD = 5--Cascading
BEGIN
SET @.InnerItemSelect =' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[ItemDetailFieldCascadingRecords] IDFCR ON IDFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE IDFCR.ItemDetailID = ID.ItemDetailID AND LT.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[TaskFieldCascadingRecords] TFCR ON TFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE TFCR.TaskID = T.TaskID AND LT.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FieldTypeiD = 6--ListBox
BEGIN
SET @.InnerItemSelect =' (SELECT i.[CSV] FROM @.ItemDetailLV i WHERE i.ID = ID.ItemDetailID AND i.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT it.[CSV] FROM @.TaskLV it WHERE it.ID = T.TaskID AND it.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FieldTypeID = 11--Users
BEGIN
SET @.InnerItemSelect =' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[ItemDetailUserRecords] IDUR ON SU.UserID = IDUR.UserID WHERE IDUR.ItemDetailID = ID.ItemDetailID AND IDUR.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[TaskUserRecords] TUR ON SU.UserID = TUR.UserID WHERE TUR.TaskID = T.TaskID AND TUR.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FIelDTypeID = 12--Group
BEGIN
SET @.InnerItemSelect =' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[ItemDetailGroupRecords] IDGR ON SG.GroupID = IDGR.GroupID WHERE IDGR.ItemDetailID = ID.ItemDetailID AND IDGR.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[TaskGroupRecords] TGR ON SG.GroupID = TGR.GroupID WHERE TGR.TaskID = T.TaskID AND TGR.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
END
PRINT'Inner Item Select:' + @.InnerItemSelect
PRINT'Inner Task Select:' + @.InnerTaskSelect
SET @.IDSelect = @.IDSelect + @.InnerItemSelect +', '
SET @.TSelect = @.TSelect + @.InnerTaskSelect +', '
SELECT @.Columns = STUFF(@.Columns, 1, @.separator_position,'')
END
-----
That is only part of a large query that writs a SQL Query to a column in a Database. That Query (in the column) is just ran normally so I don't need to compile it each time I want to run it.
THe problem I have is @.FieldName might be: ryan's field.
That apostrophe is killing me because the SQL keeps it asryan's field, notryan''s field(note the 2 apostrophes). I cannot do: REPLACE(@.FieldName, ''', '''') because it's not closing the apostrophes. Is there an escape character that I can use to say only one: ' ?
Would the only solution be to put: ryan''s field into the Database, and just format it properly on the output?
Thanks.
What I normally do is replace it with an obsure character like a ^ or | and then properly format it on the output.
|||Thought about that, but I'm not a fan of putting obscure characters in the system. It makes it a pain later on... and seeing as we have more than 300 tables and 900 Stored procedures i'm not really looking forward to working with it that way.
You best bet would be to replace the single quotes with doubles before you concatenate.
|||I figured it out:
PRINT REPLACE(@.FieldName, '''', '''''')
I didn't think this would work as I assumed it woudl look for the double apostrophes, but it escapes it to the single, and then replaces with the 2. Worked like a charm :P
