Configuring SqlParameter as Default-able

Question

I'm leaving my SqlParameter's SqlValue unset to indicate that the parameter should be set to the default value. This is per MSDN documentation:

Use null or do not set SqlValue to use the default value for the parameter.

When I try to execute the SqlCommand associated with the SqlParameter, I receive a SQL error complaining that

the parameterized query '...' expects the parameter '@p0', which was not supplied.

The problem is that the generated query calls sp_executesql without indicating that the parameter can have a default value.

Generated SQL (problematic):

exec sp_executesql N'INSERT [dbo].[WithDefault] ([Field]) VALUES (@p0);',N'@p0 int',@p0=default

Correct SQL:

exec sp_executesql N'INSERT [dbo].[WithDefault] ([Field]) VALUES (@p0);',N'@p0 int=default',@p0=default

(Notice =default after @p0 int)

Question:

How do I configure SqlParameter so that it includes =default along with the data type of the parameter?

I'd imagine something like:

param.DbType = System.Data.DbType.Int32;
param.IsDefaultable = true; // but this property doesn't exist

You posted the correct way to do this in your question. ""Use null or do not set SqlValue to use the default value for the parameter.". You are not doing either of those two things. You need to have a default defined in your procedure for this to work correctly. — Sean Lange, Jul 20 '15 at 18:13
@SeanLange, the generated SQL is what I get if I leave SqlValue unset. The destination table has a default defined (`CREATE TABLE WithDefault (Field INT NOT NULL DEFAULT(1));`). — Ben Gribaudo, Jul 20 '15 at 18:26
Why not use a stored procedure for this? It will make this issue resolve itself and you get an application with a separation of layers. I see the issue you are having but that isn't something I deal with because I use stored procs for everything. — Sean Lange, Jul 20 '15 at 18:57

score 0 · Answer 1 · answered Jul 20 '15 at 17:37

0

What I can think of, would be using COALESCE() and passing NULLs. For instance:

EXECUTE sp_executesql N'SELECT COALESCE(@p0, 15);', N'@p0 INT', @p0 = NULL;

But that doesn't seem to meet your A/C.

Other than that, I do not know if it's possible.

answered Jul 20 '15 at 17:37

1 Answers1