nullable stored procedure parametre

[Badgers]

Hi,

How can I pass an integer as null into a stored procedure.

The calling sp code:

Public Function GetAccountManagers(ByVal AgencyID As Integer, _
ByVal UserID As Nullable(Of Integer)) As SqlDataReader


Dim db As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("My.MySettings.Test_AdminConnectionString"), SqlDatabase)
Dim dbCommand As SqlCommand = db.GetStoredProcCommand("common_GetAccountManagers")
Dim dbDataReader As SqlDataReader

Try
db.AddInParameter(dbCommand, "AgencyID", SqlDbType.Int, AgencyID)

'#001 TSO 25/6/7 Add UserID Paramatre.
If UserID.HasValue = True Then
db.AddInParameter(dbCommand, "UserID", SqlDbType.Int, UserID)

End If

dbDataReader = db.ExecuteReader(dbCommand)

Return dbDataReader

Catch exsql As SqlException
Throw New ApplicationException(exsql.Message, exsql)

Catch ex As Exception
Throw New ApplicationException(ex.Message, ex)

Finally
db = Nothing
dbCommand = Nothing
dbDataReader = Nothing
End Try

End Function


The stored procedure code :

ALTER PROCEDURE common_GetAccountManagers
(
@AgencyID AS INT,
@UserID AS INT = NULL
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT TOP 100 PERCENT UserID AS AdminUserId, ISNULL(UserName, '') + ISNULL(' ' + UserSecondName, '') AS Name, AgencyID, UserEmail
FROM [Security].[dbo].view_AgencyUsers
WHERE 1 = 1
AND (UserLabel = 'agency user')
AND (AgencyID = @AgencyID)
AND (@UserID IS NULL OR UserID = @UserID)
ORDER BY Name

END

As you can see the @UserID is a optional value. Notice the statement where I only set it if not NULL.

The problem, if I pass 0 as Nullable(of integer) through it is 0 which gets passed to sp. Is it possible to send a null value through Nullable(Of Integer)??

I can get around this by added an IF in the calling code to check if UserID = 0 and then set NULL, but this seems bodgy?

Thanks

Tim.

 

[Rhys666]

Yep, that should be perfectly valid, well it is in c# anyway.

Just pass 'null' in rather than '0' and you should get a nullable integer whose 'HasValue' property is 'false' rather than 'true'.

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra

Church of the Flying Spaghetti Monster

 

[jmeckley]

if that doesn't work use DBNull.Value as the value for your null parameter.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jshurst]

Another way of adding parameters would be like this (so you don't have to tell it the type)

myCommand.Parameters.Add(new SQLParameter("@whatever",DBNull.Value))

I don't know if there are any performance implications to this, but I don't really like having to specify the type of parameter that I'm attempting to add.

Regards,

J