sql-server - SQL函数错误[函数内无效使用副作用运算符 'select']

Question

任何人都可以帮我在 sql server 2012 中创建函数吗？我是 sql 新手，它显示错误 [在函数中无效使用副作用运算符“select”]

CREATE FUNCTION Get_RearEnd_Outflow( @I_TXN_ID INT)
RETURNS int
AS BEGIN
  DECLARE @V_COUNT INT
  SELECT COUNT(*) INTO @V_COUNT FROM IRR_CHARGES_M
  RETURN @V_COUNT
END

Answer 1

correct syntax应该是

CREATE FUNCTION Get_RearEnd_Outflow( @I_TXN_ID INT)
RETURNS int
AS BEGIN
  DECLARE @V_COUNT INT
  SELECT @V_COUNT = (SELECT COUNT(*) FROM IRR_CHARGES_M)
  RETURN @V_COUNT
END

但是SET is the better way在 T-SQL 中分配变量

  SET @V_COUNT = (SELECT COUNT(*) FROM IRR_CHARGES_M)

编辑2:对于多项分配，您可以使用SELECT

对于多个刺客

SELECT  @variableOne = someColumn,  @variableTwo = someColumn from tblTable

编辑:来自关于选择的 MSDN 链接

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable. For more information, see SET @local_variable.