0

In one of my .NET apps, I'm trying to call a stored procedure in a sybase db, and some of the values that I'm sending, according to our business logic, can be null.

However, if I do send a null value, I get an "Unsupported parameter type" exception.

Any suggestions?

IWriteApps
  • 973
  • 1
  • 13
  • 30

2 Answers2

1

Do not add the parameter to the command at all, that's considered null as far as .NET is concerned.

C Bauer
  • 5,003
  • 4
  • 33
  • 62
  • Unfortunately, this suggestion is not correct for all cases. You can create stored procedures with default value for a parameter; in this case, if you omit the value, SQL will assume the default value, not NULL. Passing `DBNull.Value` is a safer choice because its behavior is always the same. – Gerardo Lima May 25 '12 at 09:03
  • I disagree. The purpose of default value is to transform null. If the value is not null, it goes in normally. Can you illustrate an example where this is relevant? – C Bauer May 29 '12 at 14:03
  • I edited my original post to present an example and its explanation. If you want to pass NULL to a parameter, assign `DBNull.Value` to it, if you don't you are just using its DEFAULT value, that can be any valid constant. By the way, this behavior is the same for SQL Server. – Gerardo Lima May 29 '12 at 15:37
  • I'm not disagreeing on the functionality, I'm disagreeing with the philosophy. Why would you assign a default value of '1' if you want to be able to send in null as an argument? – C Bauer May 29 '12 at 16:18
  • It was a sample, but there are plenty valid real world examples, @CBauer: think about a stored procedure with a *timestamp* parameter, whose default value is GETDATE(), but you want to allow the users of the SP to pass an undefined date; then, again, passing `DBNull.Value` is not the same as not passing a value at all. – Gerardo Lima May 29 '12 at 16:30
  • although I didn't vote down on your solution, I guess I've already made it clear why I criticized it: because not passing a parameter value might lead to different result that passing `DBNull.Value`. – Gerardo Lima May 29 '12 at 16:31
0

Use DBNull.Value, instead of null when passing null values to database.

From Sybase: "You can assign a default value for the parameter in the create procedure statement. This value, which can be any constant, is used as the argument to the procedure if the user does not supply one.".

The following script declares a SP with a default value, and then executes it passing: 2, NULL, DEFAULT, and without any value. You can see that the behavior of not specifying the parameter is the same as passing DEFAULT and it can be different from passing NULL.

That is the distinction I pointed, because when you don't declare the parameter for the Command object that's the type of behavior you get:

CREATE PROCEDURE MySp(@p1 INT = 1)
AS BEGIN
  SELECT @p1 AS [parameter_value];
END
GO

EXEC MySp 2;       -- parameter value = 2
EXEC MySp NULL;    -- parameter value = NULL
EXEC MySp DEFAULT; -- parameter value = 1
EXEC MySp;         -- parameter value = 1
GO
Gerardo Lima
  • 6,467
  • 3
  • 31
  • 47