How to create and call SQL Server Function


To create a SQL server function, first code a sql file such as the following one;

USE [test] –name of the Database
GO
/****** Object:  UserDefinedFunction [dbo].[ROLEDESCRIPTIONFINDER]    Script Date: 05/22/2011 01:23:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [ROLEDESCRIPTIONFINDER]
(
 @ROLE_NUMBER INT 
 — USED AS AN INPUT FOR THE FUNCTION
)
RETURNS VARCHAR(50) –RETURN VALUE
AS
BEGIN
 
 — DECLARE THE RETURN VARIABLE
 — INCLUDING THE DESCRIPTION OF ROLES
 
 DECLARE @ROLE_DESCRIPTION VARCHAR(50)

 SELECT @ROLE_DESCRIPTION = –SETTING DESCRIPTION
  CASE @ROLE_NUMBER
   WHEN 1 THEN ‘IT_PROGRAMMER’
   WHEN 2 THEN ‘MANAGER’
   WHEN 3 THEN ‘DBADMIN’
   WHEN 4 THEN ‘WEBSERVICE_DEVELOPER’
   WHEN 5 THEN ‘SYSTEM_ADMIN’
   WHEN 6 THEN ‘SOFTWARE_DEVELOPER’
   WHEN 7 THEN ‘CONTACT_CENTER_AGENT’
   WHEN 8 THEN ‘TEAM_LEADER’
   ELSE ‘DEFAULT_ROLE’
  END

 RETURN @ROLE_DESCRIPTION —RETURN VALUE
END

And call it;

SELECT [test].[dbo].[ROLEDESCRIPTIONFINDER] (4) AS “DESCRIPTION_RETURNED” 

Result:

DESCRIPTION_RETURNED
————————————————–
WEBSERVICE_DEVELOPER

(1 row(s) affected)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s