running OPENROWSET from sql function with parameter

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  February 11, 2022 at 4:09 pm

  #3982496

  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'' ' )

   

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  February 12, 2022 at 4:10 pm

  #3982814

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• frederico_fonseca

  SSCoach

  Points: 16205

  More actions

  February 12, 2022 at 7:03 pm

  #3982849

  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.