Feeds:
Posts
Comments

Archive for June, 2013

Recently, I was developing a report in a legacy application. In that application we had used SQL Server 2005 and later on migrated to SQL Server 2012. The problem I came across while developing the report was I needed the ‘year’ in two digit format. Normally the year is in four digits like 2013 but it should be in two digits only like 13 to match with the saved data in the tables.

Given below are the two different methods.
Method 1 :
In this method, we will use TWO functions (RIGHT & YEAR) to achieve it.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()
SELECT RIGHT(YEAR(@datetime),2) AS [Two digit year]
--OUTPUT

Two digit year
————–
13

(1 row(s) affected)

Method 2 :
In the second method, we will use only ONE function (FORMAT) to achieve it.

--This script is compatible with SQL Server 2012 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()
SELECT FORMAT(@datetime,'yy') AS [Two digit year]
--OUTPUT

Two digit year
————–
13

(1 row(s) affected)

Read Full Post »

We usually see this kind of problem in a legacy system wherein the datatype of a table’s column is something but the data stored in it belongs to some different datatypes. And most of the time these datatypes are compatible with each other so it does not create any issues at the time of insertion. But, when it comes to data manipulation you feel handicapped. However, you can resolve these issues using some scripting. But you must remember that before doing any manipulation you must know the actual data is in which datatype (small integer, integer, bigint, numeric, decimal, money, float) and according to that, you need to convert and then manipulate it.

Lets resolve this issue step by step :

Step 1 :
Let me create a table to demonstrate the solution.

USE tempdb
GO
CREATE TABLE tbl_sample
(
 [ID] INT,
 [Column_varchar] VARCHAR(10)
)
GO

Step 2 :
Insert some dummy data to perform aggregate SUM on column ([Column_varchar]). Given below is the script.

USE tempdb
GO
INSERT INTO tbl_sample VALUES (1,12345)
INSERT INTO tbl_sample VALUES (2,1.234)
INSERT INTO tbl_sample VALUES (3,'a64')
INSERT INTO tbl_sample VALUES (4,'1,200')

Step 3 :
Browse the data from the table and check the datatypes. Given below is the script.

USE tempdb
GO
SELECT * FROM tbl_sample

sumvarcharcolumn1.1

Step 4 :
As you can see there is a ‘,’ (Comma) in ID no 4 in the table. If you find cases like this, you need to use money datatype to convert.
Given below is the script to convert it.

USE tempdb
GO
SELECT
(CASE WHEN ISNUMERIC([Column_varchar])=1
THEN CONVERT(MONEY
,[Column_varchar]) ELSE 0 END)
AS [Converted to Numeric]
FROM tbl_sample
GO

sumvarcharcolumn1.3

Step 5 :
Once you convert it into any number datatype after that just perform any aggregate function on it. Lets SUM the column ([column varchar]) in the table (tbl_sample).
Given below is the script.

SELECT
SUM((CASE WHEN ISNUMERIC([Column_varchar])=1
THEN CONVERT(MONEY,[Column_varchar]) ELSE 0 END)
)
AS [Converted to Numeric]
FROM tbl_sample
GO

sumvarcharcolumn1.2

Read Full Post »

I received an inquiry from one of my blog readers “Mr. Arun” and he wanted to store more than 8,000 characters in a column. Generally the length of  a varchar(Max) data type consider it as a 8000 characters and above. So I suggested him to use VARCHAR(MAX). But even if you use VARCHAR(MAX), you should be careful while working on more than 8000 characters.
Let me explain the solution step by step.

Step 1 :
Let me create a table to demonstrate the solution.

USE tempdb
GO
CREATE TABLE tbl_sample
(
 [ID] INT,
 [Column_varchar] VARCHAR(MAX)
)
GO

Step 2 :
Insert 10,000 characters in the column ([Column_varchar]). Given below is the script.

USE tempdb
GO
INSERT INTO tbl_sample VALUES (1, REPLICATE('A',10000))

Step 3 :
Check the length of column ([Column_varchar]) to see if 10,000 characters are inserted or not. Given below is the script.

USE tempdb
GO
SELECT DATALENGTH([Column_varchar]) AS [Column Length]
FROM tbl_sample
--OUTPUT

morethan80001.1

Step 4 :
Ooopps…… It only inserted 8000 characters even though I passed 10,000. Basically the solution is that you need to cast the characters as VARCHAR(MAX) before insertion and insert it again.

USE tempdb
GO
INSERT INTO tbl_sample VALUES
(2, REPLICATE(CAST('A' AS VARCHAR(MAX)),10000))

Step 5 :
Check the length of column ([Column_varchar]) AGAIN and see whether 10,000 characters are inserted or not. Given below is the script.

USE tempdb
GO
SELECT DATALENGTH([Column_varchar]) AS [Column Length]
FROM tbl_sample
--OUTPUT

morethan80001.2

As you can see, this time it has inserted more than 8000 characters.

Conclusion :
Remember, whenever you are planning to insert more than 8000 characters to any varchar column, you must cast it as varchar(max) before insertion.

Read Full Post »

This error message is shipped with SQL Server 2005 and it occurs due to one of the nice functions namely COALESCE . Lets discus the cause and resolution for this error message.

Let me explain this error in detail :

Message Number: 4127

Severity : 16

Error Message: At least one of the arguments to COALESCE must be a typed NULL

Error Generation:

Let me create a sample to demonstrate this error.

Select COALESCE(NULL,NULL,NULL)
--OUTPUT

Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be a typed NULL.

Ooopps…… I am unable to execute it.

Resolution:

The reason behind this error is that COALESCE returns the first nonnull expression among its arguments and if you look at above expression, there is no nonnull expression. Given below are the two solutions to resolve it.

Solution 1 :
You need to convert any one of the arguments as typed datatype.

SELECT COALESCE(NULL,NULL,CONVERT(INT,NULL))
--OUTPUT

NULL
Solution 2 :
You need to add an additional argument as ‘N/A’ (meaning not applicable).

SELECT COALESCE(NULL,NULL,NULL,'N/A')
--OUTPUT

N/A

Conclusion :
Remember, whenever you use COALESCE make sure that at least one argument is nonnull or data typed or add one more variable N/A (‘Not Applicable’) as an argument.

Read Full Post »

Developers mostly prefer the shortest possible code and they frequently use it in their applications. I have written a shortest possible code for RAISERROR in this article but unfortunately this shortest code has been discontinued from SQL Server 2012. The most important concern is not that the shortest possible code is discontinued but what is the replacement of that code.

Given below is the shortest possible code, we use in the earlier versions of SQL Server 2005/2008.

--This script is compatible with SQL Server 2005/ 2008.
USE tempdb
GO
RAISERROR 14243 'This is a test message'
GO
--OUTPUT

Msg 14243, Level 16, State 1, Line 1
This is a test message

As you can see that above script is executed successfully. Now lets execute the above script in SQL Server 2012.

--This script is NOT compatible with SQL Server 2012.
USE tempdb
GO
RAISERROR 14243 'This is a test message'
GO
--OUTPUT

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘14243’.

Ooopps…… I am unable to execute it in SQL Server 2012. As per MSDN, we need to rewrite the statement using the current RAISERROR syntax.

Lets rewrite it step by step.

Step 1 :
This step is not mandatory, in case if your error message is already available in sys.messages.
Given below is a script to add error messages in sys.messages.

EXEC sys.sp_addmessage
     @msgnum   = 80000
    ,@severity = 10
    ,@msgtext  = N'This is a test message.'
    ,@lang = 'us_english';

Step 2 :
In this step we need to execute the RAISERROR using new syntax.
Given below is the script.

USE tempdb
GO
RAISERROR (80000, -- Message id,
           10, -- Severity,
           1) -- State
GO
--OUTPUT

This is a test message.

Cleaning :

sp_dropmessage @msgnum = 80000;
GO

Read Full Post »

« Newer Posts