Calling stored procedures, passing NULL values, dealing with boolean values

Question

When calling a stored procedure I am concatenating values together, my question is how do you call a stored procedure but send a 'NULL' value in one of the params?

Lets say that AID = null, but if I pass that to my query I get an error?!

QueryConn.Execute("Search_Res " & Count & "," & AccessList("InvoiceLevel") & "," & AID)

Ok, so my next question is going to be how do I pass in a boolean variable?

Within my stored procedure the var @SearchChildren is either true or false, but how do I define this or should I go with an int and make things simple for myself and just use 0 or 1?

MS SQL Server 2005.

Answer 1

Change the parameter of the Search_Res stored procedure to be optional. @parameter = NULL. When NULL value is passed the parameter is ignored.

CREATE PROCEDURE dbo.foo 
    @param INT = NULL 
AS 
BEGIN ...

Answer 2

In this case since you are passing the procedure call and arguments as literals you want AID to contain the string value "null", not a null value.

Alternatively, you should consider using bind parameters. It looks like your calling language is VB.NET, so I don't exactly know how to do that in that language, but there are lots of references.

I haven't used SQL Server, but I know that in Oracle it's basically illegal to pass boolean variables to stored procedures. Using an INT with a 0 or 1 value is recommended.

Answer 3

It looks like you're trying to execute an SP using an ad-hoc query rather than an ADO.Net DBCommand object. Can't you just add "@SearchChildren = null" to your string?

You can also set parameter values explicitly using the command object, it's relatively straightforward.

SqlCommand cmd = new SqlCommand("Search_Res", QueryConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SearchChildren", DBNull.Value);
..
..
values = cmd.Execute();

Excuse the C#... my VB is rusty.