Feeds:
Posts
Comments

Posts Tagged ‘raresql’

SQL SERVER 2012 – sys.dm_exec_describe_first_result_set_for_object – dynamic management function

Posted in Functions, SQL SERVER, tagged , , , , on January 15, 2013| 1 Comment »

sys.dm_exec_describe_first_result_set_for_object is one of the dynamic management functions introduced in SQL server 2012. This function also displays the first result set metadata information like “dm_exec_describe_first_result_set”  dynamic management function, but the major difference is“dm_exec_describe_first_result_set” use for SQL queries and “sys.dm_exec_describe_first_result_set_for_object” use for objects like stored procedure, triggers.

Syntax :

sys.dm_exec_describe_first_result_set_for_object
( @object_id , @include_browse_information )

Parameters :
@object_id : Object ID of stored procedure and trigger only. Data type is integer.
@include_browse_information : It can be from 0 to 2. using this parameter, function returns the additional information. Data type is bit.

Purpose :

The purpose of sys.dm_exec_describe_first_result_set_for_object  is to view the metadata information of first result set of any stored procedure or trigger. Lets say, if any procedure or trigger having more than one result set then the meta data information will be displayed for the first result set only.

Note : sys.dm_exec_describe_first_result_set_for_object can not display the meta data of other than “stored procedure or trigger”.

Let me explain it with simple examples :

Example 1 : (View metadata information of first result set of the Stored Procedure) when @include_browse_information=0 

Use AdventureWorks2012
GO
Select * from
sys.dm_exec_describe_first_result_set_for_object
(object_id('[dbo].[UDP_Employee]'),0)
--OUTPUT
--Given below are the few columns from the result set. Other than that, there are other important information also available to give you complete details of metadata.
--But if the @include_browse_information=0 then this function will not give you the source data (Source Database, Source Schema, Source Table, Source Column) information.

Example 2 : (View metadata information of first result set of the Stored Procedure) when @include_browse_information=1 

Use AdventureWorks2012
GO
Select * from
sys.dm_exec_describe_first_result_set_for_object
(object_id('[dbo].[UDP_Employee]'),1)
--OUTPUT
--If the @include_browse_information=1 then this function will give you the source data (Source database, Source Schema, Source table, Source column) information including the information available in @include_browse_information=0

Example 3 : (View metadata information of first result set of the Stored Procedure having two result set) when @include_browse_information=0
Lets create a stored procedure having two result set.

Create Procedure SP_test
As
Select top 2 [DepartmentID],[Name] from [HumanResources].[Department] order by [DepartmentID]

Select top 2 [BusinessEntityID],[JobTitle] from [HumanResources].[Employee] order by [BusinessEntityID]
GO
EXEC SP_test

Lets view the meta data information of the first result set of “SP_test” stored procedure using “sys.dm_exec_describe_first_result_set_for_object”

Use AdventureWorks2012
GO
Select * from
sys.dm_exec_describe_first_result_set_for_object
(object_id('[dbo].[SP_test]'),0)
--OUTPUT
--Given below are the few columns from the first result set Only.

Example 4 : View metadata information of View
In this example, “sys.dm_exec_describe_first_result_set_for_object” will generate an error because nothing except stored procedure or triggers is allowed.

Use AdventureWorks2012
GO
Select * from
sys.dm_exec_describe_first_result_set_for_object
(object_id('[HumanResources].[vEmployee]'),1)
--OUTPUT
--In this case all values will be NULL expect error information columns in the end of the result set.
--Given below is the screen images of the last columns.

Summary :
sys.dm_exec_describe_first_result_set_for_object gives the meta data information of the first result set of any stored procedure and trigger, but it differs from different @include_browse_information parameter.

  1. This function can filter the meta data column as per the requirement.
  2. This function returns the data in a tabular form, so you can utilize it in any other function or procedure.

Reference : MSDN

Read Full Post »

SQL Server – Split String into Rows based on multiple delimiters

Posted in Solutions, SQL SERVER, tagged , , , on January 10, 2013| 8 Comments »

In my previous article I discussed about “How to split string based on single delimiter”. In this article, we will discuss how to split the string based on multiple delimiters and also remove the spaces around the string.

Given below is the user defined function to achieve this result.

--Create this user deinfed function.
CREATE FUNCTION dbo.[UDF_Split_Based_On_Multiple_Delimiters]
(
@String VARCHAR(MAX),  -- Variable for string
@delimiter VARCHAR(50) -- Delimiter in the string
)
RETURNS @Table TABLE(        --Return type of the function
Splitcolumn VARCHAR(MAX)
)
BEGIN

DECLARE @Xml AS XML
DECLARE @REVISED_STRING VARCHAR(MAX)

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @REVISED_STRING=STUFF((SELECT '' + (Case When
PATINDEX('%[' + @delimiter + ']%',SUBSTRING(@String,Nums.n,1)) >0
Then ',' else LTRIM(RTRIM(SUBSTRING(@String,Nums.n,1))) end)
FROM N4 Nums WHERE Nums.n<=LEN(@String)  FOR XML PATH('')),1,0,'')

SET @Xml = cast(('<a>'+replace(@REVISED_STRING,
',','</a><a>')+'</a>') AS XML)

INSERT INTO @Table SELECT A.value('.', 'varchar(max)')
as [Column] FROM @Xml.nodes('a') AS FN(a)

RETURN
END
GO
--Syntax SELECT * FROM dbo.[UDF_Split_Based_On_Multiple_Delimiters] (String , multiple delimiter)
SELECT * FROM dbo.[UDF_Split_Based_On_Multiple_Delimiters]
('abc,def ; ghij ; kl',',;:')
GO
--OUTPUT

Splitcolumn
———–
abc
def
ghij
kl

(4 row(s) affected)

This may not be the best solution. Let me know if you have better solution than this to split it.

Read Full Post »

SQL SERVER 2012 – Surrounded With

Posted in SQL SERVER, Tips, tagged , , , on January 9, 2013| Leave a Comment »

SQL server 2012 introduced a new feature namely “Surrounded With” that can reduce the development time and increase the accuracy level in SQL SERVER.

In the previous version of SQL server (2005 & 2008), if we had to do a customization/development in any procedure/function and add few conditions in it, we had to first insert the condition with BEGIN in a certain place and then had to insert an END at an appropriate place.
If both were placed wrongly, then script would not work properly.

The solution is now available in SQL Server 2012 and it is very simple.
Let me explain it Step by Step.

Step 1 :

Write the code where you need to place “If condition , While condition or Begin End” .

Step 2 :

Select the code and right click and select “Surround With”

Step 3 :

Select the appropriate condition either it is “If condition”,“while condition” or “Begin End”.

Step 4 :

SQL server automatically generate the appropriate code in appropriate place, Now just write the condition and run the code.

Let me know if this technique is helpful for you.

Read Full Post »

SQL Server 2012 – Analytic Functions – PERCENT_RANK

Posted in Functions, SQL SERVER, tagged , , , , on January 8, 2013| Leave a Comment »

In my previous article I discussed about CUME_DIST. In this artcile we will discuss another important analytical function introduced in SQL SERVER 2012 and that is similar to CUME_DIST namely PERCENT_RANK. Lets discuss PERCENT_RANK syntax, purpose, return type, simple examples.

Syntax 

PERCENT_RANK( )
OVER ( [ partition_by_clause ] order_by_clause )

Purpose
The purpose of this function is to calculate the relative rank of a row within a group of rows. PERCENT_RANK of any set of First row value will be 0. PERCENT_RANK includes NULL values but they are treated as the lowest possible values.

Return Type
The return type is float(53) and the values are always between 0 and 1.

Example 1 :  Simple PERCENT_RANK()

Lets take gold rates as an example to check their relative rank in one week.

Create table [Daily_Gold_Rate]
(
(
[S.No] int,
[Date] datetime,
[Carat] int,
[Gold Rate] numeric(18,2)
)
Insert into [Daily_Gold_Rate] values(1,'2013-01-03',18,155.00)
Insert into [Daily_Gold_Rate] values(4,'2013-01-04',18,153.00)
Insert into [Daily_Gold_Rate] values(7,'2013-01-05',18,152.00)
Insert into [Daily_Gold_Rate] values(10,'2013-01-06',18,154.50)
Insert into [Daily_Gold_Rate] values(13,'2013-01-07',18,154.50)

GO

Select
Row_Number() OVER (ORDER BY [Carat],[Gold Rate]) as [Row Number]
,[Date]
,[Carat]
,[Gold Rate]
,PERCENT_RANK () OVER (ORDER BY [Carat],[Gold Rate]) AS [PERCENT_RANK]
from [Daily_Gold_Rate]

Explanation :


If you look at Column E and Column H, Column E is calculated by SQL Server and Column H is calculated manually to understand how it works. To calculate PERCENT_RANK() manually, you need two values.

1. Row Number based on the values (meaning less than or equal to value) (Column F). But first value will always be 0.
2. Total Number of Records – 1 (Column G)

Column G: It is simple; you need to calculate the total number of records and reduce 1 from it.
Column F: You simply need to get the row number based on the values. But first row number will be 0 as well, so you need start counting it from row number 2. You can observe that it is simple row number till row number 2 but in row number 3 & 4 we found the same Gold rate, so it picked up 2 (due to less than or equal to value criteria) as row number.

Finally, you need to divide Column F by Column G to get the PERCENT_RANK() manually. The same functionality PERCENT_RANK() does automatically in SQL.

Example 2 : PERCENT_RANK() with Partition By Clause

Lets insert other Gold rates to proceed with this example.

Insert into [Daily_Gold_Rate] values(2,'2013-01-03',22,190.50)
Insert into [Daily_Gold_Rate] values(3,'2013-01-03',24,202.23)

Insert into [Daily_Gold_Rate] values(5,'2013-01-04',22,191.00)
Insert into [Daily_Gold_Rate] values(6,'2013-01-04',24,202.25)

Insert into [Daily_Gold_Rate] values(8,'2013-01-05',22,190.00)
Insert into [Daily_Gold_Rate] values(9,'2013-01-05',24,203.25)

Insert into [Daily_Gold_Rate] values(11,'2013-01-06',22,189.50)
Insert into [Daily_Gold_Rate] values(12,'2013-01-06',24,201.50)

Insert into [Daily_Gold_Rate] values(14,'2013-01-07',22,189.00)
Insert into [Daily_Gold_Rate] values(15,'2013-01-07',24,201.00)

Select
Row_Number() OVER (Partition by [Carat] ORDER BY [Carat],[Gold Rate])
as [Row Number]
,[Date]
,[Carat]
,[Gold Rate]
,PERCENT_RANK () OVER (Partition by [Carat] ORDER BY [Carat],[Gold Rate])
AS [PERCENT_RANK]
from [Daily_Gold_Rate]

Reference :MSDN

Read Full Post »

SQL SERVER – Remove Duplicate Entry from Comma Delimited String

Posted in Solutions, SQL SERVER, tagged , , , on January 7, 2013| 8 Comments »

I had to migrate data from old system to new system and I found that we have one field called ‘notes’ having duplicated comma separated IDs. The next stage was to remove the duplicated (IDs) from string (notes column).
We can develop this solution via while Loop but I developed it without Loop.

Given below is the solution that can remove duplicate entry from comma, semi colon or any other delimited string .

Create Function dbo.[UDF_Remove_Duplicate_Entry]
(
@Duplicate_String VARCHAR(MAX),
@delimiter VARCHAR(2)
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Xml XML
DECLARE @Removed_Duplicate_String VARCHAR(Max)
SET @Duplicate_String=REPLACE(@Duplicate_String,'&','And')
SET @delimiter=REPLACE(@delimiter,'&','And')

SET @Xml = CAST(('<A>'+REPLACE(@Duplicate_String,@delimiter,'</A><A>')+'</A>') AS XML)

;WITH CTE AS (SELECT A.value('.', 'varchar(max)') AS [Column]
FROM @Xml.nodes('A') AS FN(A))

SELECT @Removed_Duplicate_String =Stuff((SELECT '' + @delimiter + '' + [Column]  FROM CTE GROUP BY [column]
FOR XML PATH('') ),1,1,'')

SET @Removed_Duplicate_String=REPLACE(@Removed_Duplicate_String,'And','&')
RETURN (@Removed_Duplicate_String)
END
GO

--For Example :
SELECT dbo.[UDF_Remove_Duplicate_Entry] ('1,2,2,3,4,1,1,2,3,5',',') AS [Comma Delimited]
GO
SELECT dbo.[UDF_Remove_Duplicate_Entry] ('1;2;2;3;4;1;1;2;3;5',';') AS [Semi Colon Delimited]
GO
--OUTPUT

Comma Delimited
——————
1,2,3,4,5

(1 row(s) affected)

Semi Colon Delimited
——————
1;2;3;4;5

(1 row(s) affected)

Read Full Post »

SQL SERVER – Get Number of Days in a Month

Posted in Solutions, SQL SERVER, tagged , , , on January 6, 2013| 1 Comment »

In my previous article, I discussed about the easiest way to calculate the last date of any month in SQL SERVER 2012.
Today, we will discuss the easiest way to calculate the total number of days in a month in SQL SERVER 2012.
But before that, I would like to discuss how we were calculating the total number of days in a month in the previous versions of SQL SERVER (2005 & 2008)

In SQL Server 2005/2008

For Example :

DECLARE @DATE DATETIME
SET @DATE='2012-12-10'

SELECT DAY(DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+1,0)))
AS [Current Month]

--RESULT
Current Month
-------------
29

(1 row(s) affected)

In SQL Server 2012

For Example :

DECLARE @DATE DATETIME
SET @DATE='2012-02-10'

Select Day(EOMONTH(@DATE)) AS [Current Month]
--RESULT
Current Month
-------------
29

(1 row(s) affected)

Read Full Post »

SQL SERVER – User Defined Function – Parse HTML

Posted in Functions, SQL SERVER, tagged , , , , on January 3, 2013| Leave a Comment »

Today, I came across a situation where I needed to parse HTML tags and get plain text from it, and we do not have a built-in function in SQL SERVER to do it. So, I searched the solution over the internet but most of the solutions are designed from the loop.
So, I thought of doing it without loop and with the help of XQuery.
Given below is the user defined function to remove all HTML tags (“< >”) from any HTML string and return plain text.

CREATE FUNCTION dbo.[UDF_Parse_HTML_From_String]
(
@HTML_STRING VARCHAR(MAX) -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN

DECLARE @STRING VARCHAR(MAX)
Declare @Xml AS XML
SET @Xml = CAST(('<A>'+ REPLACE(REPLACE(REPLACE(REPLACE(@HTML_STRING
,'<','@*'),'>','!'),'@','</A><A>'),'!','</A><A>') +'</A>') AS XML)

;WITH CTE AS (SELECT A.value('.', 'VARCHAR(MAX)') [A]
FROM @Xml.nodes('A') AS FN(A) WHERE CHARINDEX('*',
A.value('.', 'VARCHAR(MAX)'))=0
AND ISNULL(A.value('.', 'varchar(max)'),'')<>'')

SELECT @STRING=STUFF((SELECT ' ' + [A] FROM CTE FOR XML PATH('')),1,1,'')
RETURN @STRING
END
GO
SELECT dbo.[UDF_Parse_HTML_From_String] ('<b>This is raresql.com</b><h2>HTML Parse User Defined Function</h2><a href="http://raresql.com"></a>') as [Text]
--OUTPUT

Read Full Post »

SQL SERVER – Function to Extract Numbers From String

Posted in Functions, SQL SERVER, tagged , , , on December 19, 2012| 1 Comment »

I had to migrate data from old system to new system and I found that we have one field called ‘comments’ having contact details of the customers. The next stage was to extract the numbers (Contact details) from string (Comments column).

We can develop this solution via cursor as well but I developed it without cursor.

Given below is the solution.

CREATE FUNCTION dbo.[UDF_Extract_Numbers_From_String]
(
@String VARCHAR(MAX)  -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_STRING VARCHAR(MAX)

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @RETURN_STRING=ISNULL(@RETURN_STRING,'')+ SUBSTRING(@String,Nums.n,1)
FROM N4 Nums
WHERE Nums.n<=LEN(@String) AND  PATINDEX('%[0-9.+-]%',SUBSTRING(@String,Nums.n,1))>0

RETURN @RETURN_STRING
END
GO

SELECT dbo.UDF_Extract_Numbers_From_String
('Mobile No +49 4879 17835 is activated') as [Numbers]
GO
SELECT dbo.UDF_Extract_Numbers_From_String
('New Fax No is +2-213-8764243') as [Numbers]

--RESULT

Read Full Post »

SQL SERVER – Create Comma Separated List From Table

Posted in Solutions, SQL SERVER, tagged , , , , , , on December 18, 2012| 24 Comments »

I was preparing a statistical report and was stuck in one place where I needed to convert certain rows to comma separated values and put into a single row.

Lets say I have multiple currencies in a table and I would like to display in a single comma separated rows.

I found few options to resolve this.

  1. USING CURSOR
  2. USING COALESCE
  3. USING STUFF

Let me create a simple example to explain these methods in detail.

Create Table tbl_Currency
(
[Currency] varchar(50)
)
GO
Insert into tbl_Currency Values ('US dollar')
Insert into tbl_Currency Values ('European euro')
Insert into tbl_Currency Values ('Indian rupee')
Insert into tbl_Currency Values ('Pakistani rupee')
Insert into tbl_Currency Values ('Philippine peso')
Insert into tbl_Currency Values ('UAE dirham')
Insert into tbl_Currency Values ('British pound')

Let me explain these methods in detail :

    • USING CURSOR:

This is a simple way for development but performance wise it will cost a lot for your application.

Given below is a CURSOR example :

CREATE PROCEDURE Cursor_Example1_Proc
AS
DECLARE @Currency varchar(Max)
DECLARE @Consolidated_Currency varchar(Max)
DECLARE Cur_Cursor CURSOR FOR
SELECT [Currency] FROM tbl_Currency

OPEN Cur_Cursor

FETCH NEXT FROM Cur_Cursor INTO @Currency

WHILE @@FETCH_STATUS = 0
BEGIN
Set @Consolidated_Currency =ISNULL(@Consolidated_Currency,'')
+ ISNULL(@Currency + ', ','')

FETCH NEXT FROM Cur_Cursor INTO  @Currency
END
Select Left(@Consolidated_Currency,LEN(@Consolidated_Currency)-1) as [Currency]

CLOSE Cur_Cursor
DEALLOCATE Cur_Cursor
GO
EXEC Cursor_Example1_Proc
GO
    • USING COALESCE:

We can also achieve the same using COALESCE function. Given below is an example.

Declare @Currency varchar(Max)
Set @Currency=''
Select @Currency=@Currency + Coalesce([Currency]+ ', ','') from tbl_Currency
Select Left(@Currency,LEN(@Currency)-1) as [Currency]
GO
    • USING STUFF:

This is the recommended / best way to do this because you can achieve the same result without any variable and less lines of code. Given below is an example.

SELECT STUFF((SELECT ', ' + [Currency] FROM tbl_Currency FOR XML PATH('')),1,1,'') as [Currency]
GO

RESULT :
The result of all of the above methods is the same. Given below is the result.

  • REAL WORLD EXAMPLE :

As mentioned above, I was working on a report where multiple Bank A/C # should be displayed next to the customer name like a comma separated field.

Let me create an example to explain this :

Create Table tbl_Customer_Bank_Details
(
[Customer ID] int,
[Customer Name] varchar(50),
[Bank Account No] varchar(50)
)
GO
Insert into tbl_Customer_Bank_Details Values (1,'Imran','A001')
Insert into tbl_Customer_Bank_Details Values (1,'Imran','A002')
Insert into tbl_Customer_Bank_Details Values (1,'Imran','A003')
Insert into tbl_Customer_Bank_Details Values (2,'Bob','B001')
Insert into tbl_Customer_Bank_Details Values (2,'Bob','B002')
GO
Select * from tbl_Customer_Bank_Details
--RESULT

Lets combine the account number (comma separated values) with respect to the customer.

SELECT [Customer ID], [Customer Name]
, STUFF((SELECT ', ' + A.[Bank Account No] FROM tbl_Customer_Bank_Details A
Where A.[Customer ID]=B.[Customer ID] FOR XML PATH('')),1,1,'') As [Bank Accounts]
From tbl_Customer_Bank_Details B
Group By [Customer ID], [Customer Name]

Read Full Post »

SQL SERVER – Proper Case User-Defined Function

Posted in Functions, SQL SERVER, tagged , , , , , , on December 13, 2012| 3 Comments »

Today, I was cleaning data for upload in the live database. I found few discrepancies in customer names. The names did not have a standard pattern, some of them in proper case, while few others in small / upper case and also some in sentence case.

However, the client wanted these names to be converted to Proper Case. As usual, I started the easy way… the web search. But almost all solutions use loop to convert it into PROPER CASE.

I normally avoid loop to perform any operation until unless it is the last option due to performance issue.

So, I designed the solution via Xquery and converted the string to Proper Case without loop.

Let me create sample to explain it.

Create Table Student
(
[Student ID] int Identity(1,1),
[Student Name] varchar(50)
)

Go
Insert into Student Values ('Steve Masters')
Insert into Student Values ('david ortiz')
Insert into Student Values ('Michael sean ray')
Insert into Student Values ('Steven SElikoff')
Insert into Student Values ('Carole POLAND')
Insert into Student Values ('bjorn rettig')
Insert into Student Values ('Michiko OSAda')
Insert into Student Values ('carOL Philips')
Insert into Student Values ('Merav Netz')

GO
Create Function dbo.[UDF_PROPER_CASE]
(
@String VARCHAR(MAX)  -- Variable for string
)
RETURNS varchar(MAX)
BEGIN
Declare @Xml XML
Declare @ProperCase Varchar(Max)
Declare @delimiter Varchar(5)
Set @delimiter=' '
SET @Xml = cast(('<A>'+replace(@String,@delimiter,'</A><A>')+'</A>') AS XML)

;With CTE AS (SELECT A.value('.', 'varchar(max)') as [Column]
FROM @Xml.nodes('A') AS FN(A) )
Select @ProperCase =Stuff((Select ' ' + UPPER(LEFT([Column],1))
+ LOWER(SUBSTRING([Column], 2 ,LEN([Column]))) from CTE
for xml path('') ),1,1,'')

RETURN (@ProperCase)
END
GO
-- For Example :
SELECT dbo.[UDF_PROPER_CASE]([Student Name]) as [Student Name]
from Student
GO

Read Full Post »

« Newer Posts - Older Posts »