1. 인라인 테이블 함수

CREATE FUNCTION ufn_getEmployee(@sal INT)
	RETURNS TABLE
AS
  RETURN(
	SELECT empNo AS 사번, empName AS 이름, salary AS 급여
	  FROM employee
	WHERE salary > @sal
  )

  SELECT * 
    FROM dbo.ufn_getEmployee(500);

2. 멀티 문 테이블 함수

 CREATE FUNCTION ufn_emp_salGrade(@salary INT)

 RETURNS @table TABLE (
	empNo INT,
	empName NCHAR(10),
	grade INT
 )

 AS
	BEGIN
		DECLARE @cnt INT;
		SELECT @cnt = count(*) 
		  FROM employee
		WHERE salary >= @salary;

		IF @cnt =0
		BEGIN 
			INSERT @table VALUES(0, '없음', 0);
		END

		INSERT @table 
		  SELECT empNo, empName, 
			CASE
				WHEN ( salary >=201 AND salary <301) THEN 5
				WHEN ( salary >=301 AND salary <401) THEN 4
				WHEN ( salary >=401 AND salary <501) THEN 3
				WHEN ( salary >=501 AND salary <901) THEN 2
				WHEN ( salary >=901 AND salary <1001) THEN 1
			ELSE '해당사항 없음'
			
			END   

			FROM employee
			WHERE salary >= @salary;

			RETURN;
	END

SELECT *
	  FROM salGrade;

	SELECT *
	  FROM dbo.ufn_emp_salGrade(500);

2