How can I write SQL select statement inside scalar type user-defined function?-CodePudding

I am trying to create a function in SQL Server using the following, but I think I am missing something in either in syntax or query

CREATE FUNCTION DEMO.Get_Rate_For_Absence 
    (@company_id_ VARCHAR,
     @emp_no_ VARCHAR,
     @account_date_ DATE)
RETURN DECIMAL(10, 2) AS
BEGIN
    DECLARE @RATE_ DECIMAL(10, 2)
    SET @RATE_ = SELECT rate
                 FROM DEMO.Employee
                 WHERE COMPANY_ID = '@company_id_ '
                   AND Emp_no = '@emp_no_ '
                   AND ORG_CODE = '@wage_code_'
                   AND ACCOUNT_DATE = '@account_date_'

    RETURN @RATE
END

The SQL statement that I am trying to write inside function code block is:

SELECT DISTINCT rate 
FROM DEMO.Employee 
WHERE Company_ID = @company_id_ 
  AND EMP_NO = @emp_no_       
  AND ACCOUNT_DATE = @account_date_

CodePudding user response：

Something like:

CREATE OR ALTER FUNCTION DEMO.Get_Rate_For_Absence
    (@company_id VARCHAR(200),
     @emp_no VARCHAR(200),
     @account_date DATE)
RETURNS DECIMAL(10, 2) AS
BEGIN
    DECLARE @RATE DECIMAL(10, 2)
    SET @RATE = (
                 SELECT rate
                 FROM DEMO.Employee
                 WHERE COMPANY_ID = @company_id 
                   AND Emp_no = @emp_no
                   AND ACCOUNT_DATE = @account_date
                 )
    RETURN @RATE
END

CodePudding user response：

Perhaps you actually want to return a whole resultset rather than just a single value.

Then you should use an inline Table Valued Function (of the form RETURNS TABLE AS RETURN SELECT ...) which in any case performs much better than a scalar function.

Variables don't go in quotes so you just do COMPANY_ID = @company_id_.

Always declare varchar with a length.

CREATE OR ALTER FUNCTION DEMO.Get_Rate_For_Absence (
     @company_id_ VARCHAR(100),
     @emp_no_ VARCHAR(100),
     @wage_code_ VARCAHR(100),
     @account_date_ DATE
)
RETURNS TABLE AS RETURN
    SELECT e.rate
    FROM DEMO.Employee e
    WHERE e.COMPANY_ID = @company_id_
      AND e.Emp_no = @emp_no_
      AND e.ORG_CODE = @wage_code_
      AND e.ACCOUNT_DATE = @account_date_;

You use it slightly differently than scalar functions, as it goes in the FROM part

SELECT r.rate
FROM DEMO.Get_Rate_For_Absence('a', 'b', 'c', GETDATE()) r;

SELECT r.rate
FROM SomeTable t
CROSS APPLY DEMO.Get_Rate_For_Absence(t.a, t.b, t.c, t.date) r;