February 11, 2022 at 4:09 pm
Following function works if the parameter to harcoded as shown below.
But i would like to replace the value with parameter.
PO12345 - should be replaced by @IpRef.
I am not able to figure out how to do that.
--select * from dbo.ki_GetSupplyUsage ('PO12345')
alter FUNCTION ki_GetSupplyUsage(@IpRef VARCHAR(100))
RETURNS TABLE
AS
return
SELECT * FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=localhost; Trusted_Connection=yes', 'exec oshk_app.dbo.ki_dfdf ''.PO12345'' ' )
February 12, 2022 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 12, 2022 at 7:03 pm
you can't - dynamic sql required to do it and its not possible on user functions - see https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine?redirectedfrom=MSDN&view=sql-server-ver15
and even if you could it likely would not be advisable at all - going across a linked server to get data like that is likely to be very bad for performance - better to replicate the data used by that proc locally.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply