passing NULL as parameter to SP

[mahivr]

hi


i using exec SPName 'filterthis',1,NULL in query analyser. it works successfully.

i m accessing SQL stored proc in my page and i want to pass NULL as parameter from asp.net. but there is no enum for SqlDBtype.null. how can i pass NULL as parameter?

[mendhak]

How about System.DBNull

[mahivr]

How about System.DBNull

im trying to pass as "@vsUserID", SqlDbType.Char, 8, System.DBNull

but its not working and its giving error:

'DBNull' is a type in 'system' cant be used as expression.

[veryjonny]

I guess, its DBNull.value

[techgnome]

You could also try just not setting a value.... by its very nature that would make it NULL.

Tg

[Magiaus]

System.Data.SqlTypes.SqlString.Null the value is not important it is used to get/set the .net value type value.... you can SqlString = "sdfgsgdfg" or qlString.Value = "sdgdfgsfgsdfgf" the correct way to get a String is String = SqlString.Value

p.s. a stored procedure with invalid number of parameters passed in will fail and I'm not sure you can create a parameter object with out setting a value. However if you set the parameters default value to NULL in the sp you can omit that parameter and any other with a default value set.

[Magiaus]

the .Value I mean....

[techgnome]

System.Data.SqlTypes.SqlString.Null the value is not important it is used to get/set the .net value type value.... you can SqlString = "sdfgsgdfg" or qlString.Value = "sdgdfgsfgsdfgf" the correct way to get a String is String = SqlString.Value

p.s. a stored procedure with invalid number of parameters passed in will fail and I'm not sure you can create a parameter object with out setting a value. However if you set the parameters default value to NULL in the sp you can omit that parameter and any other with a default value set.

You most certanly can.... I do it every day....If you create a parameter and don't set the value, and don't have a default value set, the parameter will be NULL. As long as you create it and append it, it won't (shouldn't) give you errors about missing a parameter.

Tg

[Magiaus]

Can you post a snippet of how your building the parameter? I don't doubt your right; I just want to see how your doing it. I usually just use null in C#...

SqlParameter param = new SqlParamater("@SeadchName", null);

and for instance it would bring any names matching search name or everything if null....

[techgnome]

Same as you would if you were setting a value.... just leave off the value part of the CreateParameter.....
"@vsUserID", SqlDbType.Char, 8 <-- see? Just don't set the value.... create the parameter, just no value.
instead of
"@vsUserID", SqlDbType.Char, 8, System.DBNull

Tg