SQL

Monday, April 29, 2013

SQL Code for multiple delimiters in given string

CREATE TABLE #tableAll (InputString NVARCHAR(20))

CREATE TABLE #tableAllSep(ContactName NVARCHAR(50),RoleName NVARCHAR(50))

DECLARE @InputIds NVARCHAR(2000)

SELECT @InputIds='Ram@NewYaork,Raj@India,Yuvi@Aus'

DECLARE @SQL NVARCHAR(2000)

SELECT @SQL=''

SELECT @SQL ='INSERT INTO #tableAll SELECT '+''''+REPLACE(@InputIds,',',''' UNION ALL SELECT ''' )

SELECT @SQL = @SQL+''''

EXEC (@SQL)

--SELECT * FROM #tableAll

INSERT INTO #tableAllSep

SELECT LTRIM(RTRIM(LEFT(InputString,CHARINDEX('@', InputString)-1))),

SUBSTRING(InputString,CHARINDEX('@', InputString)+1,

LEN(InputString)-LEN(LEFT(InputString,CHARINDEX('@', InputString)-1))) FROM #tableAll

SELECT * FROM #tableAllSep

DROP TABLE #tableAll

DROP TABLE #tableAllSep

Sunday, March 24, 2013

ORDER BY with REPLACE()

We can you Replace() with ORDER BY in SQL Server 2008 R2. Below is the sample example.

SELECT
ID,Name FROM

(
SELECT ID,Name FROM tblPerson

)
AS OrderTemp ORDER BY REPLACE (Name,'-',' ')

Wednesday, February 27, 2013

Dates Overlapping logic:

DECLARE
@d1 DATETIME = '02-10-2012'

DECLARE
@d2 DATETIME = '02-14-2013'

IF
NOT EXISTS (SELECT 1 FROM table1

WHERE (@d1 BETWEEN start_datetime AND end_datetime) OR

(@d2 BETWEEN start_datetime AND end_datetime) OR

(start_datetime BETWEEN @d1 AND @d2) OR

(end_datetime BETWEEN @d1 AND @d2))

BEGIN

INSERT INTO Table1 (start_datetime,end_datetime,Comments)

VALUES (@D1, @d2 , 'Success')

END

SELECT
* FROM Table1

Wednesday, December 19, 2012

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

Saturday, September 22, 2012

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

Monday, August 13, 2012

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.

Monday, August 6, 2012

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

)

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