How to store multiple value in a variable in user defined function

• deba_20032004

  Default port

  Points: 1476

  More actions

  August 11, 2011 at 11:23 pm

  #99412

  Dear Sir,

  I have written a function which return multiple value but I want to store in a variable but how to store plz. help ..

  [Code]

  CREATE FUNCTION dbo.GetClg_Rate

  (@Co As Varchar(02),

  @Itemcd As Varchar(10),

  @Date As Datetime)

  RETURNS NUMERIC(14,4)

  AS

  BEGIN

  --This Query Getting closing rate of last date within given period

  --!

  DECLARE @Retval AS NUMERIC(14,4)

  SET @Retval=(SELECT CLNG_RATE FROM RATES

  WHERE ITEMCD=@Itemcd AND

  [DATE]=(SELECT MAX([DATE]) FROM RATES

  WHERE [DATE]<=@Date AND CLNG_RATE<>0))

  --!

  RETURN (@Retval)

  END

  [/Code]

• Lynn Pettis

  SSC Guru

  Points: 442205

  More actions

  August 12, 2011 at 12:31 am

  #1367725

  How are you currently using this function and how do you envision using this function?

• Sean Lange

  SSC Guru

  Points: 286491

  More actions

  August 12, 2011 at 10:05 am

  #1368060

  By definition a function canNOT return more than one value. That value could be a table of course which is referred to as a TVF (table valued function).

  Look at the online documentation about specifics.

  If you have specific usage questions we can help.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/