Introduction

This article demonstrates how to create and use a scalar function in the SQL Server Database. In this blog, we won't go very far but will only work on some basic functions.

Creating a function

In figure 1, there are two columns - farmer address and identity type. Identity type is predefined in digits and if we want the identity, we need to type the identity name in figure 2.

Scalar Function In SQL Server
Figure 1

Create Function

  1. CREATE FUNCTION [dbo].[Identitytype] (@IDENITYTYPE VARCHAR(50))
  2. returns VARCHAR(50)
  3. AS
  4. BEGIN
  5. DECLARE @IDENITYTYPE1 VARCHAR(50);
  7. SELECT @IDENITYTYPE1 = CASE
  8. WHEN @IDENITYTYPE = 1 THEN 'Aadhar No '
  9. WHEN @IDENITYTYPE = 2 THEN 'Pan Card '
  10. WHEN @IDENITYTYPE = 3 THEN 'Voter Id '
  11. ELSE 'Not Specified '
  12. END
  14. RETURN @IDENITYTYPE1
  15. END

Now, call the function in a query

You can write a function in the query and pass column name in the parameter.

  1. SELECT FARMERADDRESS,[DBO].[IDENTITYTYPE](IDENITYTYPE) AS IDENTITYTYPE FROM M_FARMERMASTER
Scalar Function In SQL Server
Figure 2

Note

  1. You can check if the function is working or not.

    example: SELECT [DBO].[IDENTITYTYPE](1)

  2. You have to always use the full name of the function and always use [dbo].FunctionName. Otherwise, it generates an error like - 'IDENTITYTYPE' is not a recognized built-in function name.

  3. You can also use it in a stored procedure in the same way.

Summary

In this article, I discussed how we can create a scalar function in SQL Server. We also saw how we can use a case in a function and how to use it. After that, we saw how we use it in a query and in a stored procedure.