sql select into into in function

When I try to alter the function below I get the following error message:

当我尝试更改下面的函数时,我收到以下错误消息:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

当未使用EXISTS引入子查询时,只能在选择列表中指定一个表达式。

I guess it is probably because of select into select. But why does this select into select work separately ( not in function ) but not in function.

我想这可能是因为select into select。但是为什么这个选择单独选择(不在函数中)但不在函数中。

ALTER FUNCTION [dbo].[Getcurrentexchangerate] (@CurrencyFromId INT, 
                                               @CurrencyToId   INT) 
returns DECIMAL(13, 10) 
AS 
  BEGIN 
      DECLARE @rate DECIMAL (13, 10) 
      DECLARE @dw INT 

      SET @dw = (SELECT Datepart(dw, Getdate())) 

      IF( @dw != 2 ) -- Monday       
        BEGIN 
            SET @rate = (SELECT TOP (1) [rate] 
                         FROM   currencyconversionrate 
                         WHERE  [currencyfromid] = @CurrencyFromId 
                                AND [currencytoid] = @CurrencyToId 
                         ORDER  BY id DESC) 
        END 
      ELSE 
        BEGIN 
            SET @rate = (SELECT * 
                         FROM   (SELECT TOP(2) Row_number() 
                                                 OVER ( 
                                                   ORDER BY id DESC) AS 
                                               rownumber, 
                                               rate 
                                 FROM   currencyconversionrate 
                                 WHERE  ( [currencyfromid] = 2 
                                          AND [currencytoid] = 5 ) 
                                 ORDER  BY id DESC) AS Rate 
                         WHERE  rownumber = 2) 
        END 

      IF( @rate IS NULL ) 
        BEGIN 
            SET @rate = 1 
        END 

      RETURN @rate 
  END

1 个解决方案

#1

See your "else" part

看看你的“其他”部分

 SET @rate = (SELECT * 
                         FROM   (SELECT TOP(2) Row_number() 
                                                 OVER ( 
                                                   ORDER BY id DESC) AS 
                                               rownumber, 
                                               rate 
                                 FROM   currencyconversionrate 
                                 WHERE  ( [currencyfromid] = 2 
                                          AND [currencytoid] = 5 ) 
                                 ORDER  BY id DESC) AS Rate 
                         WHERE  rownumber = 2)

You're trying to select all fields from currencyconversionrate table, you can't do that, or do you want to select "RATE" column only?

您正尝试从currencyconversionrate表中选择所有字段,您不能这样做,或者您只想选择“RATE”列?

Try changing to below:

尝试更改为:

 SET @rate = (SELECT rate
                         FROM   (SELECT TOP(1) Row_number() 
                                                 OVER ( 
                                                   ORDER BY id DESC) AS 
                                               rownumber, 
                                               rate 
                                 FROM   currencyconversionrate 
                                 WHERE  ( [currencyfromid] = 2 
                                          AND [currencytoid] = 5 ) 
                                 ORDER  BY id DESC) AS Rate 
                         WHERE  rownumber = 2)

#1