Posts From Sql Server Blogs

1. EXECUTE, not required, but advisable.

The other day one of the developers I work with gave me a script similar to this:

BEGIN TRAN

sp_rename 'tablename.columnname', 'newcolumnname'

COMMIT

He was getting the error:

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'tablename.columnname'.

Now most of you probably realize that had he just ran the sp_rename it would have worked just fine.

So why was the error generated? Was it because sp_rename can’t be put in a transaction? Nope, it works just fine in the transaction with a minor change to the code.

What you have to remember is that if a stored procedure is the first thing in a batch then it is executed without the need for the explicit EXECUTE command.

In this case the stored procedure name was not the first thing in the batch. The BEGIN TRANSACTION statement was. All of that means that with a simple change to the code it works correctly.

BEGIN TRAN

EXEC sp_rename 'tablename.columnname', 'newcolumnname'

COMMIT

I had a fellow DBA tell me one time “If you always do it right, you will always do it right.” It sounds a little bit odd at first and at the time he was talking about using batch processing vs using loops and cursors. Very basically what he meant was that if you keep up good coding habits you will run into fewer issues. In this particular case getting into the habit of putting EXEC or EXECUTE in front of your stored procedure calls. This way the stored procedure calls will work regardless of their placement in the batch.

Pinal Dave Puzzle:

June 2014:

Run following code:




SELECT SUM(data)FROM (SELECT NULL AS DATA) t

It will throw following error.

Msg 8117, Level 16, State 1, Line 1
Operand data type void type is invalid for sum operator.

I can easily fix if I use ISNULL Function as displayed following.




SELECT SUM(data)FROM (SELECT ISNULL(NULL,0) AS DATA) t

Above script will not throw an error. However, there is one more method how this can be fixed.

SQL SERVER – T-SQL Scripts to Find Maximum between Two Numbers:

There are plenty of the things life one can make it simple. I really believe in the same. I was yesterday traveling for community related activity. On airport while returning I met a SQL Enthusiast. He asked me if there is any simple way to find maximum between two numbers in the SQL Server. I asked him back that what he really mean by Simple Way and requested him to demonstrate his code for finding maximum between two numbers.

Here is his code:

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
SELECT (0.5 * ((@Value1 + @Value2) + ABS(@Value1 - @Value2))) AS MaxColumn
GO

I thought his logic was accurate but the same script can be written another way. I quickly wrote following code for him and which worked just fine for him.

Here is my code:

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
SELECT CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE @Value2 END AS MaxColumn
GO

He agreed that my code is much simpler but as per him there is some problem with my code which apparently he does not remember at this time. There are cases when his code will give accurate values and my code will not. I think his comment has value but both of us for the moment could not come up with any valid reason.

Do you think any scenario where his code will work and my suggested code will not work?

1. Haresh – that’s only a solution if a) you’re dealing with positive numbers and b) you consider the comparison against null to be valid. I’d argue that any comparison against NULL should return NULL.
   DECLARE @Value1 DECIMAL(5,2) = 2
   DECLARE @Value2 DECIMAL(5,2) = NULL
   SELECT CASE
   WHEN @Value1 > @Value2 THEN @Value1
   WHEN @Value2 > @Value1 THEN @Value2
   ELSE NULL
   END AS MaxColumn
   Will return the larger value if both are NOT NULL and unequal, and will return NULL if either of the values are NULL or are equal. I’d argue that that is the more correct behaviour.

SQL SERVER – Solution – Generating Zero Without using Any Numbers in T-SQL

try this

select count(cast(null as int))
or
any false condition
select count(*) where ‘a’=’b’

It seems every currency symbol that SQL Server supports. Return the same value as zero

i tried some as

select €
 select ¥
 select £

Another way for generate zero.

select count(*)-count(*)

Another way for generate zero.

select Ascii(‘Y’)-Ascii(‘Y’)
 OR
 select LEN(”)

select
$,
$+,
$+.,
-$,
$*$,
+$-,
($),
$.,
($.),
-+$-,
+-$+.,
-$+.+$.,
'$,'-$+,
'$,.'-$-.

Run the code and see the output. Each column produces zero as a result.