Dynamic openrowset in T-Sql Function or viable alternative?
- by IronicMuffin
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.
