Getting to Grips with Dynamic SQL: Validating inputs

In the last article I touched on how you would build a dynamic query which is likely, in the end, accepting an input from a user to produce a dynamic statement. These work well but often, for added security, it’s best to also validate the input value prior to executing the statement. This either means that the dynamic SQL isn’t executed at all, or you can choose to return a custom error message. Validating Objects When using dynamic objects, then use you use the sys or INFORMATION_SCHEMA objects to check that the object does intend exist. Personally I prefer the…

Continue reading

Getting to Grips with Dynamic SQL: The beginning

Dynamic SQL is a tool that many of us may end up using in the future, or have in the past. Often, however, it’s use can frowned upon because it often can be a sign that what you have really is a design problem, or what is known as an “XY Problem”. Most of the questions I do see are an indication of this, such as “I need a Procedure that accepts a table name as a parameter for an INSERT.” These sorts of requirements are often clear examples, as this likely means you are using objects names to infer…

Continue reading

Be very quiet, I’m hunting monsters

Sorry I’ve not posted anything in a couple weeks, I’ll back to it soon. I recently picked up Monster Hunter World on PC (already owned it on PS4) and been distracted by it (using it as an excuse to procrastinate) to get to a similar point in the game. I’ll get back to the SQL Server world (and finishing the T-SQL language extension for Prism) soon. I’ll aim to get part 2 on Pivots done by end of this week.

Continue reading

3+ part naming on Columns will be Deprecated

This is something that I’ve found a few people have missed and were surprised when I told them. This appears to have been snuck in with the Deprecated Database Engine Features in SQL Server 2016 under Features deprecated in a future version of SQL Server (note that in 2017 it is still under that heading and 2019 introduced no new deprecations). Specifically, from the documentation it states: The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be deprecated in a later version. The specific version of SQL Server has not been…

Continue reading

Stop using ISNUMERIC, it’s (probably) wrong

Even before SQL Server 2012, ISNUMERIC was a function that you best avoided. Put quite simply, it’s just bad. It often provides results that are wrong, and it isn’t data type specific, something that’s actually really important when you have several numerical data types to use: int/bigint and the smaller ones, numeric/decimal, float/real and money. All of these data types behave very differently when converting from a varchar. Let’s start by looking at the results for ISNUMERIC: CREATE TABLE dbo.BadNumbers(Number varchar(20)); INSERT INTO dbo.BadNumbers VALUES(‘1’), (‘7.8’), (”), (‘1,020’), (‘1,079,190.7’), (‘17,1,68,11.0’), (‘1/3’), (‘1.236E7’), (‘2d6’); SELECT Number, ISNUMERIC(Number) AS IsNumber FROM dbo.BadNumbers;…

Continue reading