how to pass an array of values as a parameter of function????

• abi

  SSC-Addicted

  Points: 454

  More actions

  March 4, 2014 at 4:36 pm

  #283061

  Hello all,

  I am trying to find out a way to pass an array of pbaseid's to a function.

  I have a function defind something like this

  Fport(@portfoliobaseid, @reportingcurr, @rowno,@todate)

  (

  returning a table

  )

  trying to execute it like this but its giving error related to subquery must return single value

  select * from Fport((select pbaseid from dbo.pbaseid),'us',1,'12/31/2013')

  I think i need to find a way to pass this pbaseid one by one but i dont know how to do this ...

  I would really appreciate if somebody can point me to the right direction.....

  Thanks a lot in advance ...

  abi

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  March 4, 2014 at 4:49 pm

  #1694134

  When using table-valued functions you need to use the APPLY operator. Your example query should look like this:

  select FP.* from dbo.pbaseid as PBI CROSS APPLY Fport(PBI.pbaseid,'us',1,'12/31/2013') as FP;

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question