Working with UDFs in SQL JOINs:

CREATE
TABLE TableOne
(

Col1 INT,
Col2
CHAR(5)

)

GO

INSERT TableOne VALUES (1, 'One')
INSERT TableOne VALUES (2, 'Two')
INSERT TableOne VALUES (3, 'Three')

GO
SELECT

* FROM TableOne

CREATE
FUNCTION dbo.udfTableTwo (@Id INT)

RETURNS
@tbl TABLE (TID INT, TChar CHAR(1))

AS
 
BEGIN
 
DECLARE @test INT

SET @test = @Id

WHILE @test >= 0

BEGIN

INSERT @tbl VALUES (@Id, CHAR(65+@test))

set @test = @test - 1

END
RETURN

END

 SELECT * FROM dbo.udfTableTwo(2)

SELECT a.*, b.* FROM TableOne a join dbo.udfTableTwo(2) b

ON a.col1=b.TID

DROP TABLE TableOne

GO

DROP FUNCTION dbo.udfTableTwo

--- Example for Sample Functions

GO

CREATE
FUNCTION [dbo].[ufn_GetDaysInMonth](

@CurrentDate DATETIME )

RETURNS INT

AS

BEGIN

DECLARE @ReturnDays INT

SET @ReturnDays =

CASE

WHEN MONTH(@CurrentDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31

WHEN MONTH(@CurrentDate) IN (4, 6, 9, 11) THEN 30

ELSE

CASE WHEN (YEAR(@CurrentDate) % 4 = 0 AND YEAR(@CurrentDate) % 100 != 0) OR (YEAR(@CurrentDate) % 400 = 0) THEN 29

ELSE 28

END

END

RETURN @ReturnDays

END

 -- ExecutingSELECT

dbo.ufn_GetDaysInMonth('02/10/2012') No_Of_Days_In_Month

GO

Debugging Stored Procedures in SQL Server 2008 r2:

In SQL Server 2008 + versions, we have option to debug stored procedures so that we can track our code and variables values in output window.

Example: Create a sample stored procedure

ISNULL(expressions)

In SQL 2008 r2, we have ISNULL() T-SQL predefined function to validate null values. If the retruning value is null then we can replace the variable with our predefined value.

Syntax: ISNULL(check_expression , replacement_value )

Eg:

DECLARE@IsPhoneNumber INT

SELECT @IsPhoneNumber = ISNULL(PhoneNumber,0) FROM dbo.tblAddress WHERE CustomerID= 1001

SELECT@IsPhoneNumber

The Result will be:

• Prints '0' if the PhoneNumber value is NULL for given customer ID.
• Prints 'PhoneNumber' if the given customerID holds phone number in that row.

How to OPEN & READ xml document in SQL 2008 R2:

In this post we are going to look into open and read an XMLdocument in SQL. In sql, we have some predefined stored procedures which hepls to prepare and remove XML files those are:

sp_xml_preparedocument,
sp_xml_removedocument

And OPENXML command executes XML handler to read and display the content. Find the below script to read and insert the XML data into a table.

CREATE  PROCEDURE [dbo].[xmlProducts]   (  @XmlOverView  NVARCHAR(MAX)  = null  )  AS    BEGIN      /***********************************************************************************    ** Desc                                      : SP to save Products details with XML documents ** Author          : Ramakrishna K ** Date                                      : 06/08/2012 ***********************************************************************************/         /***************************************    * Standard SET statements.    ***************************************/    SET NOCOUNT ON    SET ANSI_PADDING ON    SET ANSI_NULLS ON    SET ANSI_WARNINGS ON    SET CONCAT_NULL_YIELDS_NULL ON    SET QUOTED_IDENTIFIER OFF           /***************************************    * Genric variables declaration ***************************************/    DECLARE     @Msg  VARCHAR(1000),   -- To display message text     @Error  INT           -- Captuer errors                SELECT     @Error = 0,      @Msg = ''      /***************************************  *    SP declared variables  ***************************************/  DECLARE  @TransactionExists BIT,  @PID   INT, @PName NVARCHAR(100), @iDoc    INT                         BEGIN TRY                                                                                   --The number of BEGIN TRANSACTION statements that have occurred on the current connection.                                                  IF @@TRANCOUNT > 0                                                                            SET @TransactionExists = 1                                                     ELSE                                                                            SET @TransactionExists = 0                                                                                  IF @TransactionExists = 0                                                     BEGIN                                                                              --Initialise transaction                                                                                 BEGIN TRANSACTION                                                     END                                                                               ---------------Section to save the product details into a table--------------------------                                                                             IF(@XmlOverView != NULL OR @XmlOverView != '')                                                  BEGIN                                                                                EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @XmlOverView                                                                                                                          INSERT INTO Production.xmlTest                                                                                                                                                 (PID                                                                                                                                                                         ,PName                                                                                                                         )                                                                                                                                                                                                                                                   SELECT * FROM OPENXML(@iDoc, '/NewDataSet/Table',2)                                                                                                                                                                          WITH (ProductID  INT                                                                                                                                                                                                                          ,Name  NVARCHAR(500)                                                                                                                                                                          )                                                                                                                             EXECUTE sp_xml_removedocument @iDoc                                                                             IF @TransactionExists = 0                                                                           BEGIN                                                                                                     COMMIT TRANSACTION                                                                                                     RETURN (0)                                                                           END                                                 END                         END TRY                           BEGIN CATCH                                                 IF @TransactionExists = 0                               ROLLBACK TRANSACTION                                                     SET @msg = Error_Message()                            RAISERROR (@msg,16,1) WITH NOWAIT                                                         IF @Error = 0                               SET @Error = 1                              RETURN (@Error)                         END CATCH     END

T-SQL Programming (SQL Server 2008 R2)

Programming with T-SQL

• Stored Procedure
• A stored procedure is a pre-compiled collection of transact-sql statements
• A stored procedure encapsulates repetitive tasks
• A stored procedure can accept input parameters and returns status value to indicate success or failure
• A stored procedure can return multiple output parameters.
• T-SQL statements that make up the body of a stored procedure can perform database operations like INSERT, DELETE, UPDATE, and SELECT as well as call another stored procedure(s).
• To view the definition of stored procedure sp_helptext
• Drop a single stored procedure DROP PROCEDURE procedure _name
• Dropping multiple stored procedures DROP PROCEDURE procedure1, procedure2
• Advantages:
• Share application logic
• Shield database schema details
• Provide security mechanisms
• Improve performance
• Stored procedures implement many tasks as a series of T-SQL statements. Conditional logic can be applied to the results of the first T-SQL statements to determine which subsequent Transcat-SQL statements are executed.
• Reduce network traffic
• Rather than sending hundreds of Transact-SQL statements over the network, users can perform a complex operation by sending a single statement, which reduces the number of requests that pass between client and server.
• The maximum size of a stored procedure is 128MB depending on available memory.
• User Defined Functions
• Working with Cursors
• Cursors can be consider as named results sets which allow a user to move through each record one by one
• Triggers
• A trigger is a special type of stored procedure
• A trigger is:
• Associated with a table
• Invoked automatically
• Not called directly
• Treated as part of the transaction that fired it