Create user-defined function (T-SQL)

MS SQL user-defined functions are of 2 types: Scalar and Tabular-Valued based on the type of result set each return.

A Scalar function accepts one or more parameters and returns a single value.

A Table-Valued function takes one input parameter and returns the columns of the table.

Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the functions are being created. [ as referred in the Microsoft Docs ]

Syntax

  1. T-SQL Scalar function syntax
CREATE FUNCTION [ schema_name. ] <function_name> [ (@parameterName datatype)]
RETURNS return_data_type
BEGIN
function_body --T-SQL statements
RETURN scalar_expression
END;
  1. T-SQL Inline Table-Valued function syntax
CREATE FUNCTION [ schema_name. ] <function_name> [ (@parameterName datatype)]
RETURNS TABLE
AS
RETURN
(
T-SQL statements
);
GO
  1. Scalar Functions
USE HASURA;
GO
CREATE FUNCTION dbo.getDeptId (@EmpId int)
RETURNS INT
AS
BEGIN
DECLARE @DeptId int;
SELECT @DeptId = e.DeptId
FROM dbo.EMPLOYEE e
WHERE e.EmpID=@EmpId;
RETURN @DeptId;
END;
GO

Invoke the function as:

SELECT dbo.getDeptId(3);
SELECT e.empName, dbo.getDeptId(e.EmpId) AS DepartmentID FROM EMPLOYEE e;
  1. Table-Valued Functions
CREATE FUNCTION [dbo].[getEmpDetails] (@EmpId int)
RETURNS TABLE
AS
RETURN
(
SELECT e.EmpName, e.EmpID, d.DeptName
FROM EMPLOYEE e
LEFT JOIN DEPARTMENT d
ON e.DeptId=d.DepartmentId
WHERE e.EmpId=@EmpId
);
GO

Invoke the function as:

SELECT * FROM dbo.getEmpDetails(4);

result set:

EmpNameEmpIdDeptName
Musk4R&D
Did you find this page helpful?
Start with GraphQL on Hasura for Free
  • ArrowBuild apps and APIs 10x faster
  • ArrowBuilt-in authorization and caching
  • Arrow8x more performant than hand-rolled APIs
Promo
footer illustration
Brand logo
© 2024 Hasura Inc. All rights reserved
Github
Titter
Discord
Facebook
Instagram
Youtube
Linkedin