Although there may be an easier solution, here is what I need, and the approach I am currently taking.

Scenario:
Customer or Patient or Vendor Tables exist. each have their own field names to identify KEY ID, and either name, contact, etc. I need to find in a 100 record result set 50 names prior, and 50 names after a given search name. Ex: If I search for "Lar", The list may return a set from "Kry" and go to "Law" where the closest record to "Lar" will be in the middle.

Right now, I have a stored procedure that will process this but is hard-coded and tested for a specific table with variable names, etc. And it works ok.

Problem:
I am trying to make the process generic by parsing a field list of columns to return. However, when working with @variableNames in a Dynamic SQL exec, it tells me that the variable must be declared first.

Example

declare @lcTableSource varChar(20)
declare @lcFieldID     int
declare @lcFieldName   varChar(20)
declear @lcDynamicSQL  varChar(200)

select @lcTableSource = 'MyDataBase.dbo.Customer'

select @lcDynamicSQL = 'select @lcFieldID = c.customerid'
      + ' from ' + @lcTableSource + ' c'
      + ' where c.name like ''Test'' '
exec( @lcDynamicSQL )

the last exec() statement gives me the error saying that variable @lcFieldID must be declared... The @lcTableSource will actually be part of a parameter list and qualified for legitimate values. The final result will be more complex because I may need many columns of unknown data types (until passed in).

Any help would be greatly appreciated...

TIA