Parameters in Stored Procedure are @classid INT = NULL, @streamid INT = NULL. Now in VB.NET I have declared two variable Dim classid As Int32?, streamid As Int32? as NULLABLE. If no value is passed to these parameters then it pass NOTHING.

Does NOTHING is considered as NULL in SQL Server or I have to pass DBNULL.Value explicity from code while passing parameters to SP?

Because when I am executing my SP directly from SQL Server then it showing me results but if I am calling my SP from application then it showing me no records.

Passing the parameters from Code:

cmd.SelectCommand.Parameters.AddWithValue("@class", classid.GetValueOrDefault())
cmd.SelectCommand.Parameters.AddWithValue("@stream", streamid.GetValueOrDefault())

I am passing both the parameters as above.

CodePudding user response：

The documentation says you're supposed to use DBNull.Value:

value Object

The value to be added. Use [DBNull.]Value instead of null, to indicate a null value.

It's quite possible that Nothing (null in C#) works as well, but according to the documentation, it's not officially supported.

Side note: you have a bug in your code:

cmd.SelectCommand.Parameters.AddWithValue("@class", classid.GetValueOrDefault())

Here, you don't pass Nothing, you pass 0, because GetValueOrDefault() converts Nothing to the underlying data type's default value (0 in the case of Int32?).

To pass either an Int32 or Nothing, use

cmd.SelectCommand.Parameters.AddWithValue("@class", classid)

To pass either an Int32 or DBNull.Value, use

cmd.SelectCommand.Parameters.AddWithValue("@class", If(CObj(classid), DBNull.Value))

^{(CObj is required with Option Strict On to allow If to infer a common return type.)}

CodePudding user response：

1: parameters you declared in stored procedure are

@classid INT = NULL, 
@streamid INT = NULL

but passing parameters from VB.Net are

@class
@stream

you must have the same parameters in boths sides to keep it working.

2: parameters should be declared as

@classid INT NULL, 
@streamid INT NULL