Dynamic SQL Syntax H_ll Please Help

This forum has helped me get to this point a couple times with this project BUT I just can't seem to get the syntax correct. Either one of these two statements do exactly what I want them to do:
SELECT @.RtnValue = Column0 FROM MyTable WHERE RowIndex = @.RIndex
SELECT @.RtnValue = (SELECT Column0 FROM MyTable WHERE RowIndex = @.RIndex)

@.RtnValue is the value the program will work with.

The only problem is that Column0 and @.RIndex need to be dynamic so I can index through each Column and Row of the table.

This is the code I am trying to use to do this dynamically, naturally it will be in two while loop to index through each Column and Row

DECLARE @.RtnValue smallint
SET @.RtnValue = 0

DECLARE @.RIndex smallint
SET @.RIndex = 20

DECLARE @.ColumnName varchar(10)
SET @.ColumnName = 'Column0'

DECLARE @.MySelectString varchar(200)
SET @.MySelectString0 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = ' + @.RIndex + ' )'
--SET @.MySelectString1 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = 1 )'

EXEC( @.MySelectString0 )
--EXEC( @.MySelectString1 )

@.MySelectString0 produces this error:
Server: Msg 245, Level 16, State 1, Line 23
Syntax error converting the varchar value 'SELECT @.RtnValue = ( SELECT Column0 FROM MyTable WHERE RowIndex = ' to a column of data type smallint.

@.MySelectString1 produces this error:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable'@.RtnValue'.

I have tried many different combinations of syntax but can not seem to get the magic combination. Can someone tell me the correct syntax to get this to work.

Thank you in advance.

try something like this :

SET @.MySelectString0 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = ' + CONVERT(VARCHAR,@.RIndex) + ' )'

|||

Thank You for your responce I will try your solution. I sure hope it works I have been stuck on this problem to long Thanks again

Dynamic SQL H_ll Please Help Again

Still need help !

Can ANYONE tell me why this does not work ??

********
This Works when hard coded
DECLARE @.RtnValue smallint
SET @.RtnValue = 0

SELECT @.RtnValue = (SELECT MyColumn0 FROM MyTable WHERE RowIndex = 700)
********

????
This DOES NOT Work
DECLARE @.RtnValue smallint
SET @.RtnValue = 0

DECLARE @.MySelectString varchar(200)
SET @.MySelectString = 'SELECT @.RtnValue = ( SELECT MyColumn0 FROM MyTable WHERE RowIndex = 700)'
PRINT @.MySelectString

EXEC ( @.MySelectString )
????

!!!!!!!!
This is the error message when I run in SQL Query Analyzer
SELECT @.RtnValue = ( SELECT MyColumn0 FROM MyTable WHERE RowIndex = 700)
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable'@.RtnValue'.
!!!!!!!!!

I PRINT the @.MySelectString to varify and it look correct but when used in the EXEC() it does not work

Please help !

check out Books online about using sp_Executesql.
|||I too faced the similar issue, then I used a temporary table to insert the selected value of dynamic query and assigned it to the variable...though it is not a better approach..no other go as of now..!!!!|||To expand on what Dinakar said, check outthis code snippetfrom Nigel Rivett which shows how to set a variable from dynamicSQL. You need to use sp_executesql with an output parameter.
There is also a KB article on this topic which explains the concept in further detail:INF: Using Output Parameters with sp_executesql