Question of the month June 2015 – When will you use Temporary Stored Procedure?

Many of SQL developers do not know that SQL Server supports temporary stored procedure like temporary tables. A simple example is

create procedure #temp
as
select 1 as number

Have you ever used this? What is the real time use case to have a temporary stored procedure?

Advertisements

Beware of implicit conversion

You need to know that SQL Server does implicit conversion based on the precedence of the data types involved in the arithmetic operations.

Let us consider the following example

declare @num1 int, @num2 int
select @num1=3,@num2=2
select @num1/@num2

As you see the result is 1 and not 1.5 as expected because the data type of both the operands is INTEGER which results to 1 by truncation. The proper way to avoid this is to convert one of the operand to float or decimal

declare @num1 int, @num2 int
select @num1=3,@num2=2
select cast(@num1 as decimal(16,2))/@num2

The result is 1.5000000000000

Another method is just to multiply one of the operands by 1.0

declare @num1 int, @num2 int
select @num1=3,@num2=2
select 1.0*@num1/@num2


So be aware of this and whenever you use division make sure to convert one of the operand to decimal

Formatted dates , alias name and order by clause

One of my friends told me that when he was trying to order by datetime values, the result is not in proper order.

Consider the following data

create table #orders (order_id int, order_date datetime, order_value decimal(12,2))
insert into #orders (order_id,order_date,order_value)
select 1,'20150107',200 union all
select 2,'20151110',1300 union all
select 3,'20140404',500 union all
select 4,'20150110',2000 union all
select 5,'20150318',450

Suppose you want to show formatted dates and order by the order_date column, you can use the following code

select order_id,convert(varchar(10),order_date,103) as order_date,order_value
from #orders
order by order_date

The following is the result

order_id    order_date order_value
----------- ---------- ------------
3           04/04/2014 500.00
1           07/01/2015 200.00
4           10/01/2015 2000.00
2           10/11/2015 1300.00
5           18/03/2015 450.00

If you look at the result you can notice that the formatted dates are not in proper order although you order it by order_date column which is of datetime datatype. So what is the problem? It is the problem with using same column alias name for formatted dates. If you use different alias name for formatted dates and order by order_date you will get result properly

select order_id,convert(varchar(10),order_date,103) as order_date_formatted,order_value
from #orders
order by order_date

The result is

order_id    order_date_formatted order_value
----------- -------------------- --------------
3           04/04/2014           500.00
1           07/01/2015           200.00
4           10/01/2015           2000.00
5           18/03/2015           450.00
2           10/11/2015           1300.00

Please note that formatting datetime values is not recommended as they become characters and not datetime values so that any datetime related calculations are not possible until you convert them back to datetime values.

If you still want to do format for display purpose, and order it by original datetime value, use different alias name

If do not want to use different alias name, you need to again convert it back to DATETIME data type

select order_id,convert(varchar(10),order_date,103) as order_date,order_value
from #orders
order by convert(datetime,order_date,103)