Post
Ask Question

Scalar Function In SQL Server

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);   
  6.   
  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   
  13.   
  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.