>
>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).
>
>Why not reference @lcFieldID like @lcTableSource?
>
>SELECT @lcDynamicSQL = 'SELECT ' + @lcFieldID + ' = c.CustomerID'
>...
>
>Also, Would you post the finished SP? I curious how your calculating the previous and next 50 from a specific ID.
>
>-Mike
I'll try to get it to you after I finish, but what I am doing now is
declare a scrollable cursor to select "namefield" from table where "namefield" <= search value ORDERED by "namefield" (which is the indexed field).
Open the cursor and
Fetch absolute -50 from the cursor into a variable. This gives me the low value of the search.
Then I declare another cursor select from table where "namefield" >= NewLowValue from above... order by "namefield".
Then within a loop of 100, I fetch next 1 and insert into a temp table of keyID, name, other details as I need them.
It works great and pretty quick too... Now I need to make it generic for other tables, and optional field parameter list of items to include.