Replace, the character, and yea...

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: ' + @.FieldName
BEGIN
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