locked
What is purpose of SqlParameter IsNullable property?

    Question

  • I can't figure out the purpose of the SqlParameter.IsNullable property. The VS Help page for this property says it is "true if null values are accepted; otherwise false. The default is false."  From this I concluded that if it were set to false, the SqlParameter would not accept null values.  I found, however, that I could set a null value to an SqlParameter and use it in an update command to place the null value in an SQL Server table field (that accepts null values) regardless of the setting of IsNullable.  In the example given in the Help page, IsNullable is used in connection with an output parameter, so I thought it might only be relevant there.  However, I tested it with output parameters, and found that they returned nulls just fine even when isNullable was set to false.  Help. Thanks.
    Sunday, July 08, 2007 9:09 PM

Answers

  • You can write your stored procedure slightly differently to make this work..

     

    Code Snippet

    CREATE PROCEDURE usp_AddBook

    (

    @Title nvarchar(255),

    @Description nvarchar(500) = NULL

    )

    AS

     

    INSERT INTO Books

    (Title, Description)

    VALUES

    (@Title, @Description)

     

     

    In this particular example, the @Description variable can be NULL (ie...omitted from the t-sql)..

    Monday, July 09, 2007 4:28 AM
  • I'd have to look into it to say for sure, but my immediate guess would be that this property is mostly meant for reading.  Now, I'm not sure why it's not readonly, perhaps there is an instance where setting it can have an effect, but its seems logical that reading this property could be beneficial before trying to set a null value.  Especially if the database schema is essentially unknown.

     

    But to address the specific question, I'll see if I can find an instance where setting this property has an effect...

    Tuesday, July 10, 2007 5:19 AM

All replies

  • You can write your stored procedure slightly differently to make this work..

     

    Code Snippet

    CREATE PROCEDURE usp_AddBook

    (

    @Title nvarchar(255),

    @Description nvarchar(500) = NULL

    )

    AS

     

    INSERT INTO Books

    (Title, Description)

    VALUES

    (@Title, @Description)

     

     

    In this particular example, the @Description variable can be NULL (ie...omitted from the t-sql)..

    Monday, July 09, 2007 4:28 AM
  • I appreciate your reply, and I learned something new about stored procedures from it.  I understand that with the stored procedure you gave, it is not necessary to send the @Description parameter using an SqlCommand object if you want the Description field to be null.  However, I am still trying to figure out when you would need to send an SqlParameter to or from the database with the IsNullable property set to true.  Is that property used by some other object rather than being set directly by code?
    Tuesday, July 10, 2007 2:05 AM
  • It's a really good question, and to be quite honest,  I'm not sure what kind of behavior change that property might introduce..  The docs are a bit unclear..lol

    http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.isnullable.aspx

     

     

    Tuesday, July 10, 2007 3:31 AM
  • I'd have to look into it to say for sure, but my immediate guess would be that this property is mostly meant for reading.  Now, I'm not sure why it's not readonly, perhaps there is an instance where setting it can have an effect, but its seems logical that reading this property could be beneficial before trying to set a null value.  Especially if the database schema is essentially unknown.

     

    But to address the specific question, I'll see if I can find an instance where setting this property has an effect...

    Tuesday, July 10, 2007 5:19 AM