Sql-server – Dynamic SQL query – how to add an int to the code

Write your query this way. It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.

What SQL Server is compiling is really a query that looks like this

SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM 
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5]) twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table. This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1 never got a chance to filter the bad eggs.

It is by-design.

This type of formatting is generally best done in your application if possible.

The problem is that the case expression returns a result based on the highest datatype precedence of any branch.

So you would need to cast the final COUNT branch of your CASE to VARCHAR too as int has higher precedence than varchar.

Also you should probably add year into your order by except if you actually want to order Jan 2014 and Jan 2015 together followed by Feb and so on.

Though I might well be minded to take that concatenated string out of the GROUP BY too and rewrite it (Along the way fixing a couple of other issues with integer division and inconsistent boundary conditions) as.

WITH T(mmmyyyy, FORMATTED_COUNT, Yr, Mnth)
     AS (SELECT DATENAME(MONTH, MIN(CREATEDTS)) + ', ' + DATENAME(YEAR, MIN(CREATEDTS)) AS Month,
                CASE
                  WHEN COUNT(*) BETWEEN 1000 AND 999999
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000.0, 1) AS NUMERIC(4, 1)) )) ) + 'k'
                  WHEN COUNT(*) >= 1000000
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000000.0, 2) AS NUMERIC(6, 2)) )) ) + 'm'
                  ELSE CONVERT (VARCHAR(10), COUNT(*))
                END,
                YEAR(CREATEDTS),
                MONTH(CREATEDTS)
         FROM   USAGEDATA
         GROUP  BY YEAR(CREATEDTS),
                   MONTH(CREATEDTS))
SELECT mmmyyyy,
       FORMATTED_COUNT
FROM   T
ORDER  BY Yr,
          Mnth;