sql server

The type of column “xxx” conflicts with the type of other columns specified in the UNPIVOT list.

When pivoting data in SQL Server, all columns must be of same time, otherwise you will get this error. You might get this error if you have imperfect case statement or one column is of integer type while the other is of type varchar.

Interesting I was getting this error even after casting my columns into varchar(10) but still the error. Turned out just casting wouldn’t fix the problem either. My problem code was

 

The above code was giving The type of column “PercentValue” conflicts with the type of other columns specified in the UNPIVOT list becaust i was unpivoting like this

 

In the above query  + ‘%’ was the one that gave me the problem. By appending the string ‘%’ with varchar(10), it  return a true varchar type but my first string was only casted value. So adding + ‘ ‘ to the first string fixed the problem, which is basically a lank string. So the fixed code looked like this

which is merely adding +’ ‘ to string value.

cannot find stored procedure SQL Server

I was creating a stored procedure on my live server and was constantly getting the error message “Cannot find stored procedure” no matter how many times i created the same stored procedure on the server. Worse if I would create the same procedure again, I would get error, the object already exists.

Found out that I was not using dbo.StoredProcedure with the stored procedure name, that is schema was missing. So remember on live server, using dbo (schema) can make a big difference esp if your site is being hosted on a shared host like mine.

Lesson learned, dbo is important with the stored procedure name!

How to create voucher numbers in SQL?

This is a short tutorial on creating voucher numbers in SQL Server. I took most of the help from this so article but made my own changes. This can be very handy script if you are working in a financial software where you need to generate voucher numbers or sequence numbers per day.

Voucher numbers are typical numbers sequences based on department from start to end of financial year.Typical examples can be SAL/001, CST/001 etc.

The following code creates a function (needs to be created first) which is then used by the table. Every time a new entry is made in the table, the function is called which generates a voucher number. In this case, the voucher number is simply a sequence number which is generated on daily basis. For example, on a new day, first voucher is always starts with 1 and then goes.

Be careful with CASE and NULL statement

Checking NULL values in database can be a little tricky. It is very easy to trickle those if you do not provide correct syntax. The recommend way to check null is ISNULL(myfieldname,”). When using NULL in case statement, it might run without any error but will give you in correct result. Consider this example

Script to create #temp table. Note that # in front of tablename means it is temporary table. it will be automatically destroyed once you close you window.

Now lets say you want to pull column A value if it is not empty and column B value if column A is empty. You might want to write something like this which is incorrect

This basically pulls column A value and does not bother about column B value at all. Here is the result

The correct syntax to populate column C with either A or B depending which one has value is

Here is the result