Default NULL for INT data types

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Default NULL for INT data types

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-01-31 : 00:22:16

Is there a way to establish a default NULL value for an INT data type in a SPROC. You can obviously do this directly in a T-SQL statement such as UPDATE tblExample SET intField = NULL WHERE ID = 5. When I try to do this in a SPROC, the INT data type defaults to 0 rather than becoming NULL. I'm using the following syntax which works with string fields, but for some reason not with INT data types:

CREATE PROCEDURE spExample
@ID int,
@intValue int = NULL,
@strValue varchar = NULL

AS

UPDATE tblExample
SET
intField = @intValue,
strField = @strValue
WHERE ID = @ID

In the method that calls this SPROC I then use an IF statement to exclude parameters without a corresponding value in the web form. If there is no @strValue, then it defaults to NULL in the SPROC and is updated accordingly. If there is no value entered for the @intValue, then it defaults to 0 (despite the default setting in the SPROC) and the INT field is updated with a 0 rather than NULL. What am I missing here? Thanks in advance for any assistance on this.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-31 : 01:32:50

Just a WAG: on the webserver side you check strValue as if strValue="",
but intValue you compare against Null: if intValue=Null. I think both of
these parameters should be compared against "" (i.e., empty string).

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-31 : 09:16:50

I'd bet the problem is in your ADO call. No ASP/ADO code posted so it's hard to help.

ADO requires you to specify all the parameters. If a parameter isn't specified by the User in the INPUT field of a FORM, then you've got to be VERY careful to explicitly pass NULL in the parameter list. Passing an empty string is not the same as passing NULL, and ASP doesn't return NULL when a user doesn't specify a value for an INPUT field.

You will probably need something like this in ASP:

If strINPUT1 = "" Then strINPUT1 = NULL

' Your ADO call goes here...

Or, like Stoad suggested, skip the test in ASP and handle empty strings in the SP

IF @strValue = '' BEGIN SET @strValue = NULL END

The problem with this is (I'm pretty sure of this but...) @intValue will DEFAULT to Zero (0) when the ADO parameter is "" (empty string). So if Zero is a legitimate value for User INPUT, then you don't have a test for omitted INPUT of @intValue. You will have to explicitly pass NULL for @intValue on the ASP/ADO side.

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-01-31 : 14:42:37

As Stoad suggests my server-side code (ASP.NET with C#) for both the string and integer parameters already checks to see if the corresponding web form text box is blank, as follows:

<code>
if (txtStrExample.Text != "")
{
SqlParameter parameterstrField = objCommand.Parameters.Add("@strField", SqlDbType.VarChar, 50);
parameterstrField.Value = txtStrExample.Text;
}
if (txtIntExample.Text != "")
{
SqlParameter parameterintField = objCommand.Parameters.Add("@intField", SqlDbType.Int);
parameterintField.Value = Convert.ToInt32(txtintExample.Text);
}
</code>

If the text box is blank then these parameters aren't set and I expected them to default to the NULL value specified in the SPROC. As I indicated before, this is happening for the @strValue parameter but not the @intValue.

I've never been able to pass a NULL value directly into a SPROC as SamC suggests. When I attempted to do so in this case, I got the following error:

"System.Data.SqlClient.SqlException: Procedure 'spExample' expects parameter '@intValue', which was not supplied."

I've gotten around this by relying on the default NULL value declaration in the SPROC itself; at least in the case of string data types. Since the integer data types are defaulting to 0, I'm now following Stoad's advise and have just added an IF statement to evaluate each integer parameter before the UPDATE in the SPROC. If @intValue is 0 this will SET it to NULL. You'll also notice in my revised SPROC below that I no longer have a default NULL value for the @intValue parameter; since it defaults to 0 anyway.

<code>
CREATE PROCEDURE spExample
@ID int,
@intValue int,
@strValue varchar = NULL

AS

IF @intValue = 0 SET @intValue = NULL

UPDATE tblExample
SET
intField = @intValue,
strField = @strValue
WHERE ID = @ID
</code>

I still wonder whether there is a better way to do this, but it works so we'll start with that. Thank you for your assistance on this!

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-31 : 15:12:55

quote:
I've never been able to pass a NULL value directly into a SPROC as SamC suggests. When I attempted to do so in this case, I got the following error:

There's a .NET / ADO DB constant for NULL "DBNull" in fact. I've used it before, but I'd have to dig around to find an example.

An error will be generated if you try to use a .NET NULL instead of DBNull. I have no idea why they've differentiated these, but I suppose it has to do with class typing. This may (or may not) be the problem you encountered.

I'm not sure of .NET, but in ASP, ALL the parameters must be coded up to the last parameter that is passed. Skipping a parameter causes an ADO error. If the same holds for .NET, then you would *not* be able to omit @intValue, yet pass @strValue.

I've made a habit of coding all parameters, passing DBNULL (or default value) when the parameter isn't needed. Not saying this is a great solution, it's just what I do.

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-01-31 : 16:29:05

Thanks for the clarification on assigning NULL values in my ASP.NET code SamC. I'd always been perplexed that I couldn't get it to work. I've discovered that the actual syntax is DBNull.Value in C#; and not simply the Null that I've been trying to use. Following your suggestion, the alternative ASP.NET code and SPROC would be as follows:

<code>
//Add strField parameter
SqlParameter parameterstrField = objCommand.Parameters.Add("@strField", SqlDbType.VarChar, 50);
if (txtStrExample.Text != "")
{
parameterstrField.Value = txtStrExample.Text;
}
else
{
parameterstrField.Value = DBNull.Value;
}

//Add intField parameter
SqlParameter parameterintField = objCommand.Parameters.Add("@intField", SqlDbType.Int);
if (txtIntExample.Text != "")
{
parameterintField.Value = Convert.ToInt32(txtintExample.Text);
}
else
{
parameterintField.Value = DBNull.Value;
}
</code>

<code>
CREATE PROCEDURE spExample
@ID int,
@intValue int,
@strValue varchar

AS

UPDATE tblExample
SET
intField = @intValue,
strField = @strValue
WHERE ID = @ID
</code>

I've simply added control structures in my ASP.NET code to assign the DBNull.Value to the parameter if the corresponding textbox is blank. I've then removed from the SPROC both the default NULL assignment and the control structure that reset @intValue to NULL whenever it was 0. My understanding from your comments is that you consider this explicit NULL assignment to be a preferable approach. Thanks again for your guidance on this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources