This was a fun error to encounter when I tried to alter one of my SQL Server views. I searched Google with some very limited results for this one.

Msg 8115, Level 16, State 2, Procedure sp_MStran_ddlrepl, Line 95

Arithmetic overflow error converting expression to data type tinyint.

It happened when I tried to alter an existing view. I was quite confused, because it didn’t happen on our dev box. It didn’t happen on our production box, either. It only happened on our server that has replication enabled. We don’t enable replication for views, so any alters I do have to also be executed on the replicated database.

We didn’t have a stored procedure named sp_MStran_ddlrepl, nor could I find it in the system stored procs. I wasn’t doing any conversions of any kind in the view. So, what didn’t it like?

An innocuous tab character. It was hiding.

ALTER VIEW [FOO]

It was hiding between the word VIEW and the bracket [. When I removed it and put a space there instead, all was magically well again with the world.

I hope this helps someone else, but I still don’t truly understand why it happened. If you can help explain why, I’d really appreciate the comment. Thanks!

If you’ve been developing with SQL for long, you’ve wanted to have a stored procedure that can take a comma-delimited string of text as a parameter for use in an IN clause.

For example, say we want to query a Products table for a list of products, ‘Chai,Mishi Kobe Niku,Carnarvon Tigers’. We pass that to the stored procedure as @prods and have:

SELECT * FROM Products WHERE ProductName IN (@prods)

We get 0 results! That’s because the query is executed exactly as we sent it - the commas are seen as part of the input, and is the equivalent of running

SELECT * FROM Products WHERE ProductName IN ('Chai,Mishi Kobe Niku,Carnarvon Tigers')

instead of what we intended:

SELECT * FROM Products WHERE ProductName IN ('Chai','Mishi Kobe Niku','Carnarvon Tigers')

The usual remedy for this is to create a temp table. I hate temp tables.

So I decided to cheat. An IN only returns exact matches anyway, and we have control of what is passed into the stored procedure, so we can guarantee that no spaces exist between the commas in the text strings we’re trying to match.

Key words: text strings we’re matching.

You can cheat by matching against the strings delimited by the commas, as long as your table isn’t huge. You put a comma at the beginning and the end of the string, and check for your column value to match any of the values delimited by commas.

Here is how it would work.

CREATE PROCEDURE GetProductsByName
@prods VARCHAR(4000)
AS
BEGIN
SET @prods = ',' + @prods + ','

SELECT * FROM PRODUCTS
WHERE
CHARINDEX(',' + ProductName + ',', @prods) > 0
END

Now, execute it:

exec dbo.GetProductsByName @prods='Chai,Mishi Kobe Niku,Carnarvon Tigers'

Happy coding!

I found myself wanting to pull some data out of the database by month and year using a stored procedure to group things and get counts. Sure, I could use month numbers and make the consuming application display “November”, but that’s just silly, since it was MY application consuming it and all.

To get the month part or year part of a date in SQL Server, you use the DATENAME built-in function like so:

DATENAME(MM, CASE_FILE_RECEIVED) AS MNTH,

DATENAME(YY, CASE_FILE_RECEIVED) AS YR,

Short and sweet.

Got any other short and sweet “tricks”? Share them in the comments!

I know I’m not the only one who would like to pass a comma separated list of values to an SQL stored procedure and loop through them. Here’s how you loop a CSV list in TSQL. Obviously, you’d be using a passed argument and actually doing something useful, but you get the idea.

DECLARE @S VARCHAR (50)

DECLARE @I INT

DECLARE @NUM INT

SET @S = ‘1,2,3,4,50,44,323′

IF LEN(@S) > 0

BEGIN

SET @I = CHARINDEX(‘,’, @S, 0)

IF @I > 0

BEGIN

WHILE @I > 0

BEGIN

SET @NUM = SUBSTRING(@S, 0, @I)

PRINT @NUM

SET @S = SUBSTRING(@S, @I+1, LEN(@S))

PRINT @S

SET @I = CHARINDEX(‘,’, @S, 0)

END

END

ELSE

BEGIN

SET @NUM = @S

PRINT @NUM

END

END

This article is a must-read for web developers. It includes ways hackers might try to exploit your web applications using tricks that typical input escaping won’t catch. Full example code (PHP/MySQL) is provided.

The Unexpected SQL Injection - When Escaping Is Not Enough

“Having in mind the previous examples, there is no surprise that the “breaking quote” in the 5th test really breaks the query, and that in the 6th test a UNION-based injection can give the attacker a list of usernames and their associated passwords.”

While parameterized queries (for example, C#.NET) are far safer than dynamic SQL, I don’t doubt that at least one of these tricks could be used against a typical ASP.NET application that needs to do a “LIKE” or IN somewhere.

Happy reading.