Feeds:
Posts
Comments

Archive for December, 2013

I was working on an SQL server tool and got stuck in a place where I had to split the the numbers into two parts, first should be before the decimal part and second should be after the decimal part. In my earlier articles, I already wrote how to get the numbers before the decimal. In this article, I will share, how to get the numbers after the decimals.

Let me create a sample, before proceeding with the solution.
Sample :

USE Tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
 [Col_ID] INT,
 [Col_Decimal] decimal(18,4)
)
GO
INSERT INTO tbl_sample VALUES (1,12345.9876)
INSERT INTO tbl_sample VALUES (2,-12345.9876)
INSERT INTO tbl_sample VALUES (3,123.45)
INSERT INTO tbl_sample VALUES (4,12.90)
GO

Given below are the solutions.

Solution 1 : (Without any function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, ([Col_Decimal]%1) As [Col_After_decimal]
FROM tbl_sample
GO
--OUTPUT

number after decimal.1.1

Solution 2 : (Using CAST & ABS function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, ABS([Col_Decimal]) - CAST(ABS([Col_Decimal]) AS INT)
As [Col_After_decimal]
FROM tbl_sample
GO
--OUPUT

number after decimal.1.1

Advertisements

Read Full Post »

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

use temporary table in function.1.1

Read Full Post »

Sometimes, you need to define window frame within the table on the basis of certain criteria, to pick up some specific data. Today, I was developing one report for my customer and the requirement was very simple, to find the people with minimum wage in each department. We do have the solution using RANK(ROW_NUMBER) function. However, we can achieve it using FIRST_VALUE function (Shipped with SQL Server 2012) as well.

Let me create a sample to demonstrate the script.

--This script is compatible with SQL Server 2005 and above.
--Create table
 Create Table [Employee]
(
  [Employee ID] int,
  [Employee Name] varchar(50),
  [Department] varchar(50),
  [Salary] int
)

GO
--Insert records into table
Insert Into [Employee] Values (1,'Ali','IT',10000)
Insert Into [Employee] Values (2,'Derek','IT',6000)
Insert Into [Employee] Values (3,'Jack','IT',9000)
Insert Into [Employee] Values (4,'Simon','Marketing',5000)
Insert Into [Employee] Values (5,'Monica','Marketing',7500)
Insert Into [Employee] Values (6,'Sandra','Marketing',8000)
GO

Given below are the solutions :

SOLUTION 1 :
This is an old method and we have been using it since SQL Server 2005. We use a Ranking Function namely ROW_NUMBER in this solution to find the minimum value in the group. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
;With CTE As
(
Select
 Row_Number() Over (Partition By Department Order By Salary) As [S.No]
,[Employee ID]
,[Employee Name]
,[Department]
,[Salary]
From dbo.[Employee]
)
Select
 [Employee ID]
,[Employee Name]
,[Department]
,[Salary]
from CTE
Where [S.No]=1
--OUTPUT

minimum value in the group.1.1

SOLUTION 2 :
We will use a new analytic function namely FIRST_VALUE in this solution to find the minimum value in the group. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
;WITH CTE As
(
Select
 [Employee ID]
,[Employee Name]
,[Department]
,[Salary]
,FIRST_Value([Salary]) Over
(Partition By [Department] Order By [Salary]) As [minimum Value]
From dbo.[Employee]
)
Select
 [Employee ID]
,[Employee Name]
,[Department]
,[Salary]
From CTE
Where [Salary]=[minimum Value]
GO
--OUTPUT

minimum value in the group.1.1

Read Full Post »

Today, I was working on customer’s test database and I wanted to delete all the tables having “_temp” in the names. Even though I could manually write the script by using a table at a time, I was looking for a shortcut. I have been using sp_MSforeachtable on test databases for multiple purposes. But as far as I know, it performs the specified operation in all tables in the particular database. So I started doing my research. First of all, I searched if there was any parameter available in the sp_MSforeachtable that we can use to filter tables objects and I found one parameter namely @whereand. Given below is the script to view the available parameters in sp_MSforeachtable.

EXEC sp_help sp_msforeachtable
--OUTPUT

sp_msforeachtable_conditional.1.1

SOLUTION :
You need to use the @whereand parameter to filter any table object in sp_MSforeachtable (Undocumented system stored procedure).
Given below is a script, where I will select table contains “_temp” in the name.

 USE tempdb
 GO
 EXEC sp_msforeachtable
 @command1 ='SELECT * FROM ?'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
Where name like ''%_temp%'')'

Note : It is not recommended to use sp_MSforeachtable on production databases.

Read Full Post »

In SQL Server, alias are very helpful, because you do not need to write the long table names again and again. I usually use ALIAS in my development to speed up the work. Yesterday, I was working on a complex query and I used a lot of alias to minimize the line of code and finally I came across an error.

Let’s discuss this in detail:
Message Number: 1011

Severity : 16

Error Message: The correlation name ‘%.*ls’ is specified multiple times in a FROM clause.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error.

Use AdventureWorks2012
GO

SELECT
p.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,p.[JobTitle]
FROM [HumanResources].[Employee] p
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]

GO

Msg 1011, Level 16, State 1, Line 2
The correlation name ‘p’ is specified multiple times in FROM clause.

Ooopps…… I am unable to execute the above qquery.

Resolution:
The resolution is very simple. Basically, you aliased more than one table with the same name, so SQL Server is unable to relate alias with table name. In the above table, I ALIASED both tables with p while I can assign one name (p) as an alias to one table only and I must change the alias of other table name (p) to something else. (That make sense also). Lets rewrite the script with the different alias and it works…….

USE [AdventureWorks2012]
GO
SELECT
e.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,e.[JobTitle]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
GO
--OUTPUT

error_message_1011.1.1

Read Full Post »

I deployed some Sequence objects on production database and after couple of days, I was facing some problem with Sequence objects. It was little difficult to debug all sequence objects at once, so I thought of checking the sequence objects modified dates to find if I modified any Sequence object after deployment.

Given below are the two methods to find the modified dates of Sequence Object.

METHOD 1 : Using sys.sequence
Given below is the script to find the modified date of all sequence object using sys.sequences

USE AdventureWorks2012
GO
SELECT
name As [Sequence Name]
,modify_date
FROM sys.sequences
GO
--OUTPUT

Sequence modified dates

METHOD 2 : Using sys.objects
Given below is the script to find the modified date of all sequence object using sys.objects

USE AdventureWorks2012
GO
SELECT
name As [Sequence Name]
,modify_date
FROM sys.objects
WHERE [type] ='SO'
GO
--OUTPUT

Sequence modified dates

Read Full Post »

« Newer Posts