Dapper reporting invalid syntax near parameter name when attempting to pass null in a DynamicParameter

Question

I'm attempting to get all records from a table where a certain field is not a blank string or null. Due to the way I'm building the queries, ISNULL and COALESCE are not options here. Changing the schema of the database is not an option either.

This is the code I'm running to try to retrieve the records.

using System;
using System.Data.SqlClient;
using System.Linq;
using Dapper;

namespace DapperMCVE
{
    internal class UserFinder
    {
        public static void Main(string[] args)
        {
            using (var connection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;Integrated Security=True"))
            {
                connection.Execute(@"IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = 'Users')
                                        CREATE TABLE Users(NullableUserId varchar(50) NULL) ON [PRIMARY]");
                connection.Execute(@"DELETE Users");
                connection.Execute(@"INSERT INTO Users(NullableUserId) 
                                     VALUES(''), (NULL), ('SteveSmith@fake.com'), ('Morgan@totallyreal.org')");
                var parameters = new DynamicParameters();
                parameters.Add("UserIdNull", (string)null);
                parameters.Add("UserIdBlank", "");
                try
                {
                    var users = connection.Query(@"SELECT * 
                                                   FROM Users 
                                                   WHERE NullableUserId IS NOT @UserIdNull 
                                                     AND NullableUserId != @UserIdBlank", parameters);
                    Console.WriteLine(users.ToList());
                }
                catch (SqlException e)
                {
                    Console.WriteLine(e);
                }
                Console.ReadKey();
            }
        }
    }
}

The error thrown is System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '@UserIdNull'.

My assumption is that the above code should replicate this query:

SELECT *
  FROM Users
  WHERE NullableUserId IS NOT NULL
    AND NullableUserId != ''

but it seems to be doing something closer to

SELECT *
FROM Users
WHERE NullableUserId IS NOT 'NULL'
  AND NullableUserId != ''

If I change the query to

SELECT *
FROM Users
WHERE ISNULL(NullableUserId, '') != ISNULL(@UserIdNull, '')

it works fine. Unfortunately, I cannot use ISNULL in this case.

I've replicated this in SQL Server 2014 and 2016. We're using 2016 in our production environment.

The profiler reports that the following command is being run:

exec sp_executesql N'SELECT * 
                     FROM Users 
                     WHERE NullableUserId IS NOT @UserIdNull 
                       AND NullableUserId != @UserIdBlank',N'@UserIdNull nvarchar(4000),@UserIdBlank nvarchar(4000)',@UserIdNull=NULL,@UserIdBlank=N''

Which makes me suspect this may be an issue with SQL Server itself?

Things I've tried (to no avail):

so why dont you capture the actual sql command with the profiler ? — GuidoG, Dec 07 '17 at 14:53
I dont know dapper, but maybe in `parameters.Add("UserIdNull", (string)null);` you should use DBNull.Value somehow ? — GuidoG, Dec 07 '17 at 14:57
@GuidoG I added the command from the profiler. The second link I provided addresses `DBNull.Value`. The tl;dr is that dapper doesn't support it, because they expect you to use `null` instead. — Morgan Thrapp, Dec 07 '17 at 14:59
@GuidoG I'm building the entire query dynamically based on a JSON object that's being passed to me. I don't have a way ahead of time of knowing if the field is nullable, and if it is, what the appropriate default value is. Having said that, I can probably alter my API to allow that, but I was hoping to find a solution that would allow Dapper to filter on null values. — Morgan Thrapp, Dec 07 '17 at 15:44

score 3 · Answer 1 · answered Dec 07 '17 at 15:35

3

You can not use IS NOT and variable. You can hardcode NullableUserId IS NOT NULL if you always expect null. Or create query text using some string concatenation

answered Dec 07 '17 at 15:35

Alex Lyalka

1,484
8
13

GuidoG · Answer 2 · 2017-12-08T09:08:44.023

2

You cannot use IS NOT and parameter in sp_executesql or anywhere else.
Look at this example.

This query returns 4 records from my table :

exec sp_executesql 
N'SELECT * FROM tblUser WHERE gsm IS NOT null AND gsm != '''''

So now I try it with parameters for both values, and now I get an error

exec sp_executesql 
N'SELECT * FROM tblUser WHERE gsm IS NOT @UserIdNull AND gsm <> @UserIdBlank',
N'@UserIdNull nvarchar(100),@UserIdBlank nvarchar(100)',
@UserIdNull=NULL,@UserIdBlank=N''

but now I get the error

Incorrect syntax near '@UserIdNull'

It will give the same error when you do it without sp_executesql

declare @UserIdNull nvarchar(100) = null
declare @UserIdBlank nvarchar(100) = ''
SELECT * FROM tblUser WHERE gsm IS NOT @UserIdNull AND gsm <> @UserIdBlank

This produces the same error

So the only option you have is to build your query without a parameter for the IS NOT value or use ISNULL()

In the query you build in your question, you known when to write IS NOT and then followed by a parameter.
I suggest to simply write IS NOT NULL in stead of using a parameter there

edited Dec 08 '17 at 09:08

answered Dec 07 '17 at 15:38

GuidoG

11,359
6
44
79

The problem is that I don't know ahead of time if the field is nullable. The user is passing the operator, in this case `IS NOT`. Even if I did know that the field is nullable, I wouldn't know what the default value for `ISNULL` should be. – Morgan Thrapp Dec 07 '17 at 15:57
I see, but when you get the operator `IS NOT` then could you not always write `NULL` behind it, in stead of a parameter ? – GuidoG Dec 07 '17 at 16:01
No, because `IS NOT` might be part of `IS NOT IN`, for example. – Morgan Thrapp Dec 07 '17 at 16:16
yes you are right. I am afraid I run out of options than – GuidoG Dec 07 '17 at 16:16
We should never expect the code shown here (in this answer) to work. The **entire point** of `sp_executesql` - the reason it exists - is to allow dynamic SQL to work *with parameters*; parameters are never injected - they are retained as parameters. This is not a problem in `sp_executesql` - it is the *correct behaviour*. I strongly recommend a glance at http://blog.marcgravell.com/2017/12/dapper-prepared-statements-and-car-tyres.html – Marc Gravell Dec 07 '17 at 21:47
@MarcGravell Why do you expect the code in this answer to work ? It clearly states that is does not works. If using parameters should work in sp_executesql then why does it not works in the OP's query ? – GuidoG Dec 08 '17 at 07:58
You misunderstand me; from your first line: "There seems to be a problem with sp_executesql and NULL value for parameters." - you seem to be suggesting that the sp_executesql shown *should* work - but IMO that misunderstands what parameters *are* are how they should behave. – Marc Gravell Dec 08 '17 at 08:27
@MarcGravell What I tried to show here (by using an example) is that using parameters with sp_executesql works, but not for `IS NOT` and variable. – GuidoG Dec 08 '17 at 09:05
@GuidoG but again: that's not "a problem with sp_executesql" - that's literally just how `IS NOT ...` *works* - it doesn't work with variables or parameters at all, ever, in any context – Marc Gravell Dec 08 '17 at 09:07
@MarcGravell Yes you are right, I changed the first line in my answer – GuidoG Dec 08 '17 at 09:09
@MarcGravell And I added an example without sp_executesql to demonstate that it also will not work – GuidoG Dec 08 '17 at 09:14

score 2 · Accepted Answer · answered Dec 07 '17 at 21:41

2

IS NOT @UserIdNull genuinely isn't valid SQL syntax.... IS NOT NULL is fine. This isn't a dapper issue - this is simply an SQL feature: you need to write legal SQL.

My assumption is that the above code should replicate this query:

Nope. It uses parameters. The syntax is retained. Dapper does not inject literals. This is very deliberate and correct. I wrote about this yesterday, as it happens.

answered Dec 07 '17 at 21:41

Marc Gravell

1,026,079
266
2,566
2,900

Interesting. I had no idea that this wasn't supported in SQL. – Morgan Thrapp Dec 08 '17 at 13:40

Dapper reporting invalid syntax near parameter name when attempting to pass null in a DynamicParameter

3 Answers3