Programming in MS SQL Server 2012

Decode Argument

Argument is an external data value that provided to a user-defined function to produces new scalar value or table value based on input data value. The argument variables must declares nested to CREATE FUNCTION FunctionName statement with data type and data size. If a user-defined function has more than one argument variables that need to separate by using comma symbol in the parentheses of the user-defined function.
The following example describes a scalar user-defined function with Varchar variables.

-- Example 95 --

USE EBusiness

IF EXISTS (SELECT *

FROM sysobjects

WHERE name = N'DisplayData')

DROP FUNCTION DisplayData

CREATE FUNCTION DisplayData(@Dept_Code char(2))

RETURNS varchar(100)

BEGIN

DECLARE @NewData varchar(100)

SELECT @NewData =

CASE @Dept_Code

WHEN '10' THEN 'System Department'

WHEN '20' THEN 'Commercial Department'

WHEN '30' THEN 'Accounts Department'

WHEN '40' THEN 'Marketting Department'

WHEN '50' THEN 'Administration Department'

ELSE 'Unknown'

END

RETURN @NewData

END

-- Execute Function with Argument Data Values --

SELECT EBusiness.dbo. DisplayData('10')

SELECT EBusiness.dbo. DisplayData('20')

SELECT EBusiness.dbo. DisplayData('30')

SELECT EBusiness.dbo. DisplayData('40')

SELECT EBusiness.dbo. DisplayData('50')

Query Output Screen

* * * * *

Your Comment To AUTHOR