sp_executesql with ISNUMERIC(@ColumnName) not working with column name passed as parameter

Question

Question: Why example 1 does not work while example 2 works:

SQL Table to check for non-numeric values in varchar column:

CREATE TABLE #t(Mycol varchar(15))
INSERT #t VALUES ('123.86'),('4a5.67'),('45.68'),('45r8.78')

Query to check the NON-NUMERIC values in the table:

SELECT Mycol from #t WHERE ISNUMERIC(Mycol) = 0

Output:

Mycol
4a5.67
45r8.78

GOAL: Achieve the same by using dynamic SQL

Example 1: Did not work, why? How can we improve this code without declaring variables outside EXECUTE sp_executesql statement; or is it even possible?

EXECUTE sp_executesql N'SELECT @colName as Mycol from #t WHERE ISNUMERIC(@colName) = 0', N'@colName varchar(15)', @colName = N'Mycol';

Output:

Mycol
Mycol
Mycol
Mycol
Mycol

Example 2: Works - but required more variable declarations.

DECLARE @Qry nvarchar(150), @colName varchar(15) = 'Mycol'
SET @Qry = N'SELECT ' + @colName + ' FROM #t WHERE ISNUMERIC(' + @colName + ') = 0'
EXECUTE sp_executesql @Qry, N'@colName varchar(15)', @colName = N'Mycol';

Output:

Mycol
4a5.67
45r8.78

• Charlieface: Side note: ISNUMERIC is a bad idea, for example it returns true for the value . Better to use TRY_CONVERT to some numeric type and check for null

Answer 1

In your first example, the @colName value becomes a string literal within the query. You're expecting that the executed query is going to be:

SELECT MyCol as MyCol from #t WHERE ISNUMERIC(MyCol) = 0

However, the actual query that ends up being executed is equivalent to this:

SELECT 'MyCol' as MyCol from #t WHERE ISNUMERIC('MyCol') = 0

And, obviously, in the case of every record that is in your #t table, the string value 'MyCol' is not going to be numeric, and therefore you get a row returned for every record, but every row just has the value 'MyCol' (because that's what the query statement asks for).

In your second example, you're fully building the query statement first, and then executing it - in the case of the second example, your @Qry variable will hold this string:

SELECT MyCol from #t WHERE ISNUMERIC(MyCol) = 0

So that's the query that gets executed by sp_executesql. (It's worth noting that in the case of the second example, there doesn't actually end up being any @colName variable in the actual query string being executed by sp_executesql, so the inclusion of that as a parameter declaration is redundant.

It's not possible to use the parameter variables of sp_executesql to denote column names - as you see from my description of your Example 1 case, the variable values are injected into the query as string literals (as if you'd typed the values with enclosing quotes), not just an inline text value like you were expecting.

Therefore, Dynamic SQL is your only real friend in this case. As I pointed out, though, you don't actually need to do the "multiple" variable declaration that you have in your Example 2 - this much will achieve the desired result:

DECLARE @Qry nvarchar(150), @colName varchar(15) = 'Mycol'
SET @Qry = N'SELECT ' + @colName + ' FROM #t WHERE ISNUMERIC(' + @colName + ') = 0'
EXECUTE sp_executesql @Qry

And, as per @Charlieface's comment, QUOTENAME is a good inclusion to minimise the risk of SQL injection

DECLARE @Qry nvarchar(150), @colName varchar(15) = 'Mycol'
SET @Qry = N'SELECT QUOTENAME(' + @colName + ') FROM #t WHERE ISNUMERIC(QUOTENAME(' + @colName + ')) = 0'
EXECUTE sp_executesql @Qry