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.
Exampledeclare @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