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

GO

 

IF EXISTS (SELECT *

           FROM sysobjects

           WHERE name = N'EmployeeName')

           DROP FUNCTION EmployeeName

GO

 

CREATE FUNCTION EmployeeName()

RETURNS varchar(100)

AS

BEGIN

   DECLARE @FirstName varchar(50),

           @LastName varchar(50)

   SET @FirstName = 'Prem'

   SET @LastName = 'Nath R.K.'

   RETURN @FirstName + ' ' + @LastName

END

GO

 

-- 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.

* * * * *


Email Your Comment To AUTHOR