User Defined Functions | SQL Server Portal

Posts Tagged ‘User Defined Functions’

SQL Server – How to convert hexadecimal to binary

Posted in Solutions, SQL SERVER, tagged raresql, SQL, SQL Server, User Defined Functions on January 14, 2015| Leave a Comment »

How to convert hexadecimal to binary became very interesting when I was trying to read SQL Server log. Most part of SQL Server log data is in Hexadecimal so you need to convert it from hexadecimal to multiple formats to read it properly. Generally, programmers use the hexadecimal table to convert it into binary. But I developed this solution using remainder method and used it in almost all my solutions, wherever I used SQL Server log. Given below is the script.

Script :

--DROP FUNCTION dbo.[UDF_Convert_Hex_to_Binary]
--GO
CREATE FUNCTION dbo.[UDF_Convert_Hex_to_Binary]
(
@HEX VARBINARY(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN

DECLARE @BINARY 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 @BINARY=ISNULL(@BINARY,'')
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 128) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 64) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 32) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 16) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 8) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 4) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 2) % 2)
+ CONVERT(NVARCHAR(1), SUBSTRING(@HEX, Nums.n, 1) % 2)

FROM N4 Nums
WHERE Nums.n<=LEN(@HEX)

RETURN @BINARY
END
GO

Example :

Select dbo.[UDF_Convert_Hex_to_Binary](0x1cFEEE) AS [Hex to Binary]
GO
Select dbo.[UDF_Convert_Hex_to_Binary](0x2efd) AS [Hex to Binary]
GO
--OUTPUT

Read Full Post »

SQL SERVER – How to remove the duplicate words in the sentence

Posted in Solutions, SQL SERVER, tagged raresql, SQL, SQL Server, User Defined Functions on January 5, 2015| Leave a Comment »

I have been asked this question how to remove the duplicate words in a sentence, when my team and I were busy in messaging legacy application data and we had to migrate it to SQL Server 2012. Also, we had to check and clean the data if there was any duplicate word in a sentence. So I started scripting and checking multiple options to develop this solution including loop & XML, but I usually prefer XML. So I finally developed the solution using XML.

Before proceeding with the solution, I would like to create an example to demonstrate the solution.

--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
[ID] INT IDENTITY(1,1),
[Sentence] VARCHAR(MAX)
)
GO
INSERT INTO tbl_Sample
VALUES ('This is the the test test script from from raresql.com')
GO
INSERT INTO tbl_Sample
VALUES ('This should should remove duplicates')
GO

The script of this solution is given below. and can be downloadable from here.

--DROP FUNCTION dbo.[UDF_Remove_Duplicate_Entry]
--GO
CREATE FUNCTION dbo.[UDF_Remove_Duplicate_Entry]
(
@Duplicate_Word VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Xml XML
DECLARE @Removed_Duplicate_Word VARCHAR(MAX)
SET @Xml = CAST(('<A>'+REPLACE(@Duplicate_Word,' ','</A><A>')+'</A>') AS XML)

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

SELECT @Removed_Duplicate_Word =(SELECT Stuff((SELECT '' + ' ' + '' + A.[Column] FROM CTE A
LEFT JOIN CTE B ON A.[Sno]+1=B.[Sno]
WHERE (A.[Column]<>B.[Column] Or B.[Sno] is NULL)
FOR XML PATH('') ),1,1,''))

RETURN @Removed_Duplicate_Word
END
GO

SELECT
[ID]
,[Sentence] As [Before Duplicate removal]
,dbo.[UDF_Remove_Duplicate_Entry]([Sentence]) As [After Duplicate removal]
FROM tbl_Sample
GO
--OUTPUT

Let me know if you come across this scenario and its solution.

Read Full Post »

SQL SERVER – How to use a temp table in a table-valued function

Posted in Solutions, SQL SERVER, tagged raresql, SQL, SQL Server, temporary tables, User Defined Functions on December 11, 2013| 1 Comment »

Local and global temporary tables play a vital role in the SQL Server scripting. We generally use it to store temporary values for further manipulation. But unfortunately, you cannot use it inside the user defined function.

PROBLEM :
If you use temporary tables in the function, you will get the below error message.

Msg 2772, Level 16, State 1, Procedure ufnGetEmployeeInformation, Line 18
Cannot access temporary tables from within a function.

So, what is the solution?

SOLUTION :
To resolve this, you need to use table variables instead of temporary tables. You can easily accommodate it in the user defined function for further manipulation.
Given below is a sample.

--This script is compatible with SQL Server 2005 and above.
--DROP FUNCTION [dbo].[ufnGetEmployeeInformation]
--GO
USE AdventureWorks2012
GO
CREATE FUNCTION [dbo].[ufnGetEmployeeInformation]
(@BusinessEntityID int)
RETURNS @EmployeeInformation TABLE
(
[BusinessEntityID] int,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[JobTitle] [nvarchar](50) NULL,
[City] [nvarchar](30) NULL,
[Return Time] [datetime] NULL
)
AS
BEGIN
INSERT INTO @EmployeeInformation
SELECT
[BusinessEntityID],
[FirstName],
[LastName],
[JobTitle],
[City],
getdate()
FROM [HumanResources].[vEmployee]
WHERE
BusinessEntityID=@BusinessEntityID

RETURN;
END
GO

EXAMPLE :

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT * FROM [dbo].[ufnGetEmployeeInformation](124)
--OUTPUT

Read Full Post »

SQL Server – Compare two databases schema

Posted in Solutions, SQL SERVER, tagged Compare source and target database, Compare two SQL Server databases schema, Indexes, Mismatch data type in table or view, Missing column in table or view, Missing Parameter in the stored procedure, Primary Keys, raresql, SQL, SQL Server, Stored Procedures, Tables, Triggers, User Defined Functions, Views on October 15, 2012| 3 Comments »

Today, I received an email from one of my blog readers, who would like to match test and production databases. Here is a basic script to compare two databases.

Given below are the objects, script will compare in source and target databases.

Tables
Views
Stored Procedures
User Defined Functions
Triggers
Primary Keys
Indexes
Missing column in table or view
Mismatch data type in table or view
Missing Parameter in the stored procedure

Create Procedure Sp_Compare_two_database_Schema
@Source_DB_Name nvarchar(Max),
@Target_DB_Name varchar(Max)

As
BEGIN
DECLARE @SQL nvarchar(MAX)

SET @Source_DB_Name=QUOTENAME(@Source_DB_Name)
SET @Target_DB_Name=QUOTENAME(@Target_DB_Name)

SET @SQL=''

CREATE TABLE #Result
(
[Main Object Type] nvarchar(max),
[Main Object Name] nvarchar(max),
[Type] varchar(50),
[Sub Object Type] nvarchar(max),
[Sub Object Name] nvarchar(max)
)

--Match Main Objects Like Tables, view, Stored Procedure , triggers.
SET @SQL= 'Insert into #Result Select A.[type_desc] as [Main Object Type],A.[name],''Object Missing'' as [Type]
,A.[type_desc] as [Sub Object Type]
,A.[name] as [Sub Object Name]
from ' + @Source_DB_Name + '.sys.objects A
Where [Parent_object_id]=0 And A.[name] NOT IN(
Select [name] from ' +  @Target_DB_Name + '.sys.objects)
Order By A.[type_desc]'
Print @SQL
EXEC (@SQL)

--Match Sub Objects Like Foreign Keys.
SET @SQL= 'Insert into #Result Select A.[type_desc] as [Main Object Type],A.[name],''Object Missing'' as [Type]
,B.[type_desc] as [Sub Object Type]
,B.[name] as [Sub Object Name]
from ' + @Source_DB_Name + '.sys.objects A
Inner Join ' + @Source_DB_Name + '.sys.objects B
On A.[object_id]=B.[Parent_object_id]
Where B.[name] NOT IN(
Select [name] from ' +  @Target_DB_Name + '.sys.objects)
Order By A.[type_desc]'
Print @SQL
EXEC (@SQL)

--Find if any column is missing in target database.

SET @SQL= ';With CteA AS (Select A.[type_desc] as [Main Object Type],A.[Name] as [Main Object Name],''Column Missing'' as [Type],B.[Name] as [Column Name]
from  ' + @Source_DB_Name + '.sys.objects A
Inner Join  ' + @Source_DB_Name + '.sys.columns B
On A.[object_id] =B.[object_id]
Where A.[Type] In (''U'',''V'')
)
,CteB AS (Select A.[type_desc] as [Main Object Type],A.[Name] as [Main Object Name],''Column Missing'' as [Type],B.[Name] as [Column Name]
from ' +  @Target_DB_Name + '.sys.objects A
Inner Join ' +  @Target_DB_Name + '.sys.columns B
On A.[object_id] =B.[object_id]
Where A.[Type] In (''U'',''V'')
)
Insert into #Result
Select A.[Main Object Type],A.[Main Object Name],A.[Type], ''Column'' as [Sub Object Type],A.[Column Name] from CTEA A
Left Join CTEB B On
A.[Main Object Type]=B.[Main Object Type]
And A.[Main Object Name]=B.[Main Object Name]
And A.[Column Name]=B.[Column Name]
Where (B.[Main Object Name] is NULL OR B.[Column Name] is NULL)
And A.[Main Object Name]  Not In (Select [Main Object Name] from #Result A
Where A.[Type]=''Object Missing'')
Order By A.[Main Object Type],A.[Main Object Name], A.[Column Name]'
Print @SQL
EXEC (@SQL)

--Find if any column data type is not sync with target database.

SET @SQL= ';With CteA AS (Select A.[type_desc] as [Main Object Type],A.[Name] as [Main Object Name]
,''Data Type Difference'' as [Type],B.[Name]  as [Column Name]
,B.[system_type_id]
from ' + @Source_DB_Name + '.sys.objects A
Inner Join ' + @Source_DB_Name + '.sys.columns B
On A.[object_id] =B.[object_id]
Where A.[Type] In (''U'',''V'')
)
,CteB AS (Select A.[type_desc] as [Main Object Type],A.[Name] as [Main Object Name],''Data Type Difference'' as [Type],B.[Name] as [Column Name],B.[system_type_id]
from ' +  @Target_DB_Name + '.sys.objects A
Inner Join ' +  @Target_DB_Name + '.sys.columns B
On A.[object_id] =B.[object_id]
Where A.[Type] In (''U'',''V'')
)

Insert into #Result Select A.[Main Object Type],A.[Main Object Name],A.[Type], ''Column'' as [Sub Object Type], A.[Column Name] from CTEA A
Inner Join CTEB B On
A.[Main Object Type]=B.[Main Object Type]
And A.[Main Object Name]=B.[Main Object Name]
And A.[Column Name]=B.[Column Name]
Where A.[system_type_id]<>B.[system_type_id]
And A.[Main Object Name]  Not In (Select [Main Object Name] from #Result A
Where A.[Type]=''Object Missing'')
Order By A.[Main Object Type],A.[Main Object Name], A.[Column Name]'

Print @SQL
EXEC (@SQL)

--Find if any parameter of the procedure is missing in target database.

SET @SQL= ';With CteA AS (Select A.[type_desc] as [Main Object Type]
,A.[Name] as [Main Object Name],''Parameter Missing'' as [Type],B.[name] as [Parameter Name]
from' + @Source_DB_Name + '.sys.objects  A
Inner Join ' + @Source_DB_Name + '.sys.all_parameters B On A.[object_id] =B.[Object_id]
)
,CteB AS (
Select A.[type_desc] as [Main Object Type]
,A.[Name] as [Main Object Name],''Parameter Missing'' as [Type],B.[name] as [Parameter Name]
from ' +  @Target_DB_Name + '.sys.objects  A
Inner Join ' +  @Target_DB_Name + '.sys.all_parameters B On A.[object_id] =B.[Object_id]
)

Insert into #Result Select A.[Main Object Type],A.[Main Object Name],A.[Type]
, ''Parameter'' as [Sub Object Type]
, A.[Parameter Name] from CTEA A
Left Join CTEB B On
A.[Main Object Type]=B.[Main Object Type]
And A.[Main Object Name]=B.[Main Object Name]
And A.[Parameter Name]=B.[Parameter Name]
Where (B.[Main Object Name] IS NULL OR  B.[Parameter Name] IS NULL)
And A.[Main Object Name]  Not In (Select [Main Object Name] from #Result A
Where A.[Type]=''Object Missing'')
Order By A.[Main Object Type],A.[Main Object Name], A.[Parameter Name]'
Print @SQL
EXEC (@SQL
)
Select * from #Result A Order By A.[Main Object Type] DESC, A.[Main Object Name] ASC ,[Type] DESC
END
GO

--Syntax
--Sp_Compare_two_database_Schema 'Source DatabaseName','Target DatbaseName'

--Example
Sp_Compare_two_database_Schema 'Source_DB','Target_DB'

Read Full Post »

SQL Server Portal

Real world SQL scenarios & its unique & optimized solutions

Posts Tagged ‘User Defined Functions’

SQL Server – How to convert hexadecimal to binary

SQL SERVER – How to remove the duplicate words in the sentence

SQL SERVER – How to use a temp table in a table-valued function

SQL Server – Compare two databases schema

Recent Posts

Archives

Categories

Blog Stats

Follow Blog via Email

Find us on Facebook

Disclaimer