User Defined Functions

Overview:

Functions are the database objects, it contains the set of T-SQL transactions and has set of the rule that differs from stored procedure, Will discuss the difference between functions and stored procedures in next post. There are three types of functions namely Scalar Functions, Inline Table-Valued Functions and Multi-Statement Table-Valued Functions.

Scalar Functions: This function type returns a single value of data type specified in the RETURNS clause. The returned values are of any type except text, ntext, image, cursor, or timestamp.

Example:

CREATE FUNCTION fn_ScalarExample(@Val INT)

RETURNS INT

AS

BEGIN

      SET @Val = @Val*2;

      RETURN @Val;

END

GO

Usage   :

                SELECT dbo.fn_ScalarExample(12)

Result   :

                24

Inline Table-Valued Functions:  In this type there is no transactions & returns a variable of data type table whose value is derived from a single SELECT statement. SELECT scripts are specified immediate to RETURN

Example:

CREATE FUNCTION fn_InlineExample(@Val INT)

RETURNS TABLE

AS

      RETURN (SELECT (@Val*2) AS Val);

GO

Usage   :

                SELECT * FROM dbo.fn_InlineExample(12)

Result   :

                24

Multi-Statement Table-Valued Functions: In this we have to define the structure of table in RETURNS section and through the set of transaction data has to be loaded into the table.

Example:

CREATE FUNCTION fn_MultiStatementExample(@Val INT)

RETURNS @Test TABLE( Val INT)

AS

BEGIN

      INSERT @Test

      SELECT  @Val*2

      RETURN

END

GO

Usage   :

                SELECT * FROM dbo.fn_MultiStatementExample(12)

Result   :

                24

Dynamic File Up loader

Overview:

                Easy tool to upload flat or .CSV files to the database table.  Very useful for small scale projects and does data dump quickly.

Query:

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id=OBJECT_ID(N'usp_Uploader'))

DROP PROCEDURE usp_Uploader

GO

SET   NOCOUNT     OFF;

GO

CREATE PROCEDURE usp_Uploader(

                                                @tableName nVARCHAR(20),

                                                @filePath nVARCHAR(50),

                                                @rowDelimiter VARCHAR(5),

                                                @fieldDelimiter VARCHAR(5),

                                                @startFrom VARCHAR(4),

                                                @errorMessage nVARCHAR(200) OUTPUT

                                          )

AS

BEGIN

      BEGIN TRANSACTION

      BEGIN TRY

            DECLARE     @query VARCHAR(max);

            SET         @query            =     'BULK INSERT '+@tableName+'

                                                FROM '+@filePath+'

                                                WITH

                                                (

                                                FIRSTROW ='+@startFrom+',

                                                FIELDTERMINATOR ='+@fieldDelimiter+',

                                                ROWTERMINATOR = '+@rowDelimiter+'

                                                )'                                 

            EXECUTE(@Query);

            COMMIT TRANSACTION

      END TRY

      BEGIN CATCH

            ROLLBACK TRANSACTION

            SET   @errorMessage = ERROR_MESSAGE()

      END CATCH

END;

GO

Usage:

DECLARE     @Error nVARCHAR(200);

EXEC usp_Uploader 'test','''d:\Source.txt''','''\n''','''\t''',100,@Error OUTPUT;

PRINT @Error;     

Dynamic PIOVTing

Overview:

                Pivot gives you the options to make rows data in to columns and this example gives you the way to create dynamic pivoting. You can see in the query that locations are assigned to a variable and the same has been used in the PIVOT query. So, rows data that i.e AUS, UAE, UK and USA are taken into the variable.

 

Base table:

Query:

            DECLARE     @Query      VARCHAR(200);

DECLARE     @Columns    VARCHAR(200);

SELECT      @Columns=COALESCE(@Columns+',','')+'['+a.Location+']'

FROM        (     SELECT DISTINCT Location FROM DynamicPivot)a

SET         @Query =    'SELECT     Name,'+@Columns+'

                        FROM  (     SELECT      Name, Location, Quantity

                                    FROM  DynamicPivot

                              ) a

                        PIVOT(SUM(Quantity) FOR Location IN ('+@Columns+')) pvt'

EXEC(@Query);

Result:

 

Difference between IsNull, Coalesce and NullIf

Overview:

                This article explains the detailed difference between IsNull, Coalesce and NullIf.

ISNULL: This function works like “if” condition which we use in other program languages. It takes only two arguments, if first argument is Null then it returns second and one more thing is that the second argument should be of same size that means if first argument has varchar(2) and second has varchar(4) then it truncates last characters.

Below gives you the structure and the example

ISNULL(argument1, argument2)

Pseudocode

IF argument1 IS Null

                   RETURNS argument2

            ELSE

                   RETURNS argument1

            END      

             Example:             SELECT ISNULL(NULL,'Test')

             Result:                  Test      

 Example :

DECLARE @a VARCHAR(2);

DECLARE @b VARCHAR(4);

SET                          @b = 'abcd';

SELECT ISNULL(@a,@b)

 Result : ab

COALESCE: This function works same as “if else” statement and it takes multiple arguments and it checks Null values for each if first argument is null then it considers the second value, if the both first and second arguments are nulls then it considers third argument. It contradicts the IsNull as it truncates the extra characters if the size of second argument is bigger than first but COALESCE doesn’t. Below are its structure and example

COALESCE(argument1, argument2, argument3[, argument4, argument6, argument7…])

Pseudocode

IF argument1 IS NOT Null 

                RETURNS argument1

            ELSE argument1 IS NOT Null AND argument2 IS Null

                                RETURNS argument1

            ELSE argument1 IS NOT Null AND argument2 IS NOT Null AND argument3 IS Null

                                RETURNS argument1

ELSE argument1 IS Null AND argument2 IS Null AND argument3 IS Null

                                RETURNS “Default Set Value”

            END

                Example:

                Query:

SELECT    field1,

                                                                field2,

                                                                field3,

                                                                COALESCE(field1,field2,field3,0)  AS Result

FROM     TableName

    Result:

    Example :

DECLARE @a VARCHAR(2);

DECLARE @b VARCHAR(4);

SET          @b = 'abcd';

SELECT COALESCE (@a,@b)

    Result : abcd

 NULLIF: This is a special function where it works as “if” statement and it takes two arguments and compares the 2 argument, if both are same then returns “Null” else returns the first argument. Below are its structure and example

NULLIF(argument1, argument2)

Pseudocode

IF argument1 equals argument1

                   RETURNS Null

            ELSE

                   RETURNS argument1

END

Example:             SELECT NullIF(2,1)

Result:                 2

Example:             SELECT NullIF(1,1)

Result:                  NULL

SQL DBCC USEROPTIONS

Overview:

                Returns the SET options active for the current connection or database. By executing the DBCC USEROPTIONS command you get to know, what are all values set for Set Option for the current connection or database.

Code:

                DBCC USEROPTIONS

Result:

textsize	2147483647
language	us_english
dateformat	mdy
datefirst	7
lock_timeout	-1
quoted_identifier	SET
arithabort	SET
ansi_null_dflt_on	SET
ansi_warnings	SET
ansi_padding	SET
ansi_nulls	SET
concat_null_yields_null	SET
isolation level	read committed

SQL PIVOT table

Overview:

                It gives you the transition from rows to columns. Below example explains how we can use PIVOT on table data.

Code:

Prerequisites

/*Create a tempeorary table*/

CREATE TABLE #PivotExample(Col1 VARCHAR(20), Col2 VARCHAR(20), Col3 FLOAT)

GO

/*Inserting data into the tempeorary table*/

INSERT INTO #PivotExample(Col1, Col2, Col3) VALUES('A','x',5.4)

,('A','y',3.4)

,('A','z',5.2)

,('A','x',4.4)

,('B','y',3.4)

,('B','z',1.4)

,('B','x',3.2)

,('B','z',7.1)

GO

/*Displays the data from the table*/

SELECT * FROM #PivotExample

GO

PIVOT usage

/*Pivot table logic*/

SELECT Col2, A, B

FROM (

SELECT Col1, Col2, Col3

FROM #PivotExample) actualTable

PIVOT (SUM(Col3) FOR Col1 IN (A, B)) AS pivotTable

ORDER BY Col2

GO

/*Removing tempeorary table*/

DROP TABLE #PivotExample

GO

Result:

Data in #PivotExample table

PIVOT Result (Applied on “Col1”)