I'm not quite sure how to phrase this. Here is the problem:

I have 1-n items that I need to join to a different system (AS400) to get some data.

The openrowset takes forever if I specify the where criteria outside of the openrowset, e.g.:

select * from openrowset('my connection string', 'select code, myfield from myTable') 
where code = @code

My idea was to create a function that takes in the item number and uses dynamic sql to inject it into the openrowset string, a la:

declare @cmd varchar(1000)
set @cmd = 'select * from openrowset('my connection string',
        ''select code, myfield from myTable where code = ' + @code + ''')'

Apparently I can't use the insert.. exec.. strategy inside of a function. Is there any better way to achieve this? I was going to use this in joins where I needed the external data using cross apply.

I'm not married to tvf and cross apply, but I do need a method of getting this data quickly. Thanks for any help.