Relating a 'Field name' to a data as fieldname>>> help

[cklarise]

hi everyone!

i have some what a definition table of the fields of the other table
name as DefinitionTable with data as follows

FieldType FieldName1 FieldDesc TxtColor FontStyle
8 FIELD1 ABC #1111 [fsbold]
8 FIELD2 CDE #2222 [fsitalic]
8 FIELD3 EFG #3333 [fsbold]

OrigTable
OrigKey FieldType FIELD1 FIEDL2 FIELD3 Results
1 8 test1 test2 test3 ok

so the e.g. FIELD1 is a data in DefinitionTable and a field name in OrigTable..
my problem is how to get the TxtColor in merging this table.. my sp in fetching the data but only the fielddescription is

create procedure Definition
@SearchKey int,
@FieldTypeint
as
begin
DECLARE @FieldName varchar(10),
@FieldDesc varchar(30),
@TextColor int,
@mysql varchar(4000)
SET NOCOUNT ON


SELECT @mysql = 'select o.origkey, '

DECLARE MyCursor CURSOR READ_ONLY FOR
SELECT FieldName1, FieldDesc FROM DefinitionTable
WHERE FieldType = @ResultType

Open MyCursor
FETCH NEXT FROM MyCursor INTO @FieldName, @FieldDesc
WHILE @@FETCH_STATUS = 0
BEGIN
select @mysql = @mysql + 'o.' + @FieldName + ' as ' + @FieldDesc + ', '
FETCH NEXT FROM MyCursor INTO @FieldName, @FieldDesc
END

CLOSE MyCursor
DEALLOCATE MyCursor

select @mysql = @mysql + ' o.results from OrigTable o where
FieldType = ' + @ResultType

execute(@mysql)

end


the result is Ok as
OrigKey ABC CDE EFG Result
8 test1 test2 test3 ok

But the problem is how to include the TxtColor or concatenate TxtColor with this output below

OrigKey ABC CDE EFG Result
8 test1+##1111 test2+#222 test3+#3333 ok


thanx,
mygt

[rmiao]

On which rdbms?

[cklarise]

thanx for reply..

i use ms sql..

[MAK]

Update the following
--@TextColor int,
@TextColor varchar(100),


--DECLARE MyCursor CURSOR READ_ONLY FOR SELECT FieldName1, FieldDesc FROM DefinitionTable WHERE FieldType = @ResultType

DECLARE MyCursor CURSOR READ_ONLY FOR SELECT FieldName1, FieldDesc,convert(varchar(10),txtcolor) as TextColor FROM DefinitionTable WHERE FieldType = @ResultType

and
then include the @TEXTCOLOR in the select statement

[shamshe]

Cklarise,

here is what you need:
create procedure Definition
@SearchKey int,
@FieldType int,
@ResultType VARCHAR(50)
as
begin
DECLARE @FieldName varchar(10),
@FieldDesc varchar(30),
@TxtColor int,
@TextColor VARCHAR(20),
@mysql varchar(4000)
SET NOCOUNT ON

---SET @TextColor ='+'
SELECT @mysql = 'select o.origkey, '

DECLARE MyCursor CURSOR READ_ONLY FOR
SELECT FieldName1, FieldDescription, TxtColor FROM DefinitionTable
WHERE FieldType = @ResultType

Open MyCursor
FETCH NEXT FROM MyCursor INTO @FieldName, @FieldDesc, @TxtColor
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TextColor =convert(varchar(10),@TxtColor)
select @mysql = @mysql + 'o.' + @FieldName + ' as ' + @FieldDesc + ', ' + QUOTENAME(@TextColor,'''')+ ' as TxtColor,'
FETCH NEXT FROM MyCursor INTO @FieldName, @FieldDesc, @TxtColor

END

CLOSE MyCursor
DEALLOCATE MyCursor

select @mysql = @mysql + ' o.results from OrigTable o where
FieldType = ' + @ResultType

execute(@mysql)

end

[cklarise]

i have done different way, but it seems your solutions here both is more efficient.. i will apply this..

thanks a lot guys!