SQL Function error [invalid use of side-effecting operator 'select' within a function]

Question

Can any one help me to create function in sql server 2012 ,I M new in sql it shows error [invalid use of side-effecting operator 'select' within a function]

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

The correct syntax should be

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

however SET is the better way to assign variable in T-SQL

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

EDIT 2: For multiple assignment you can use SELECT

For multiple assin

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

EDIT: From the MSDN link about slect

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

Answer 2

    CREATE FUNCTION Get_RearEnd_Outflow()
     RETURNS int
     AS
     BEGIN
     DECLARE @COUNT INT;
    SET @COUNT =(SELECT COUNT(*) FROM IRR_CHARGES_M);
return @COUNT
    END
     GO

or use :

CREATE FUNCTION Get_RearEnd_Outflow()
     RETURNS int
     AS
     BEGIN
    return  (SELECT COUNT(*) FROM IRR_CHARGES_M)

    END
     GO