Programming in MS SQL Server 2012

MODIFY Function

In SQL Server Enterprise Manager Application, locate the (EBusiness) database and expand the node, click the user-defined functions node in the left pane and in the righ pane, right-click (EmployeeName) name of the function and then click Modify Function option. SQL Server displays the existing function source code to update program.

The following example describes a User-defined function with RETURN value.

-- Example 89 --

USE EBusiness

IF EXISTS (SELECT *

FROM sysobjects

WHERE name = N'EmployeeName')

DROP FUNCTION EmployeeName

CREATE FUNCTION EmployeeName()

RETURNS varchar(100)

BEGIN

DECLARE @FirstName varchar(50),

@LastName varchar(50)

SET @FirstName = 'Prem'

SET @LastName = 'Nath R.K.'

RETURN @FirstName + ' ' + @LastName

END

-- Execute Scalar User-defined Function --

SELECT EBusiness.dbo.EmployeeName()

SELECT EBusiness.dbo.EmployeeName() AS 'Employee Name'

SELECT 'Employee Name' = EBusiness.dbo.EmployeeName()

Query Output Screen

Note:

sp_help is used to displays user-defined function information.

sp_helptext is used to displays user-defined function source code.

* * * * *

Your Comment To AUTHOR