An Introduction to SQL Server Scalar UDFs

Unlike stored procedures, scalar functions can also be created using the option SCHEMA BINDING and ENCRYPTION, as shown below:

CREATE FUNCTION fnSchemaTest

(

             @EmailId varchar(255)

)

RETURNS varchar(100)

WITH SCHEMABINDING

AS

BEGIN

       DECLARE @FullName varchar(100)

       SELECT @FullName  = [FT_FName] + ‘ ‘ + [FT_LName]

            FROM dbo.FunctionTest

       WHERE [FT_EMail] = @EmailID

       RETURN @FullName

END

The addition of columns is allowed, as shown below:

ALTER TABLE dbo.FunctionTest

ADD [Active] bit

The deletion of a newly added column is allowed, as shown below:

ALTER TABLE dbo.FunctionTest

DROP COLUMN [Active]

The deletion of column being referenced in the function with SCHEMA BINDING is not allowed, as shown below:

ALTER TABLE dbo.FunctionTest

DROP COLUMN [FT_FName]

Output:

Server: Msg 5074, Level 16, State 3, Line 1

The object ‘fnSchemaTest’ is dependent on column ‘FT_FName’.

Server: Msg 4922, Level 16, State 1, Line 1

ALTER TABLE DROP COLUMN FT_FName failed because one or more objects access this column.

Since the columns FT_FName is being used in Function with SCEHEMA binding we cannot DROP it.

CREATE FUNCTION fnEncrypTest

(

             @EmailId varchar(255)

)

RETURNS varchar(100)

WITH ENCRYPTION

AS

BEGIN

        DECLARE @FullName varchar(100)

        SELECT @FullName  = [FT_FName] + ‘ ‘ + [FT_LName]

               FROM dbo.FunctionTest

               WHERE [FT_EMail] = @EmailID

       RETURN @FullName

END

Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |