declaring multiple variables within a custom function

Question

I am trying to create a function. The function will receive two parameters & I would like it to return a string.

For example

    Paramter 1          Parameter 2
    @CurrName           @Currency

    SEP17 3600 CALL     GBP
    AUG17 4000 CALL     EUR

So my function will take two parameters, please note parameter 1 @CurrName will always be 3 letters (the month) and 2 digits which we don't care about.

So I want if the @FX is GBP the start of the string to be returned to be "UKX" and if it is EUR then "SXE5"

The output I want from this is as such

 1st one ->      UKX 9 3600
 2nd one -)      SXE5 8 4000

However I am having trouble with the very first bit as it doesn't seem to like the fact I have declared the variable @tickStart, "select statements included within a function cannot return data to a client".

   CREATE FUNCTION ufOptionName 
  (
    -- Add the parameters for the function here
    @CurrName nvarchar(30),
    @FX nvarchar(3)
  )
  RETURNS nvarchar(30)
  AS
  BEGIN
  -- Declare the return variable here
DECLARE @newName nvarchar(30),
@tickStart nvarchar(10)

select case     
    when @FX = 'EUR' then set @tickStart = 'SX5E'
    when @FX = 'GBP' then set @tickStart = 'UKX'
    else set @tickStart = 'US'
end

-- Return the result of the function
RETURN @newName

END
GO

artm · Accepted Answer · 2017-07-17 07:19:34Z

1

Use this:

SET @tickStart = case     
    when @FX = 'EUR' then 'SX5E'
    when @FX = 'GBP' then 'UKX'
    else 'US'
end

answered Jul 17, 2017 at 7:19

artm

8,5943 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

declaring multiple variables within a custom function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related