SQL String in SQL Serv. Stored Procedure

Can a concatenated SQL string be used in a SQL Server Stored Procedure as it can in VB or MS Access VBA? In other words, in VB I can set a string
strSQL = “SELECT * FROM MyTable WHERE Name = ” & Table.FieldName & “;”
Is there a way to do it in aStored Procedure so that variables can be imbedded which represent fieldnames which are in another table as entries.
CASE SELECT is not a reasonable alternative, since there are up to 50 different field names in the second table.