Call stored procedures that pass null values ​​related to boolean values

Question

When I call a stored procedure, I combine the values ​​together, my question is how do you call the stored procedure, but send the value "NULL" in one of the parameters?

Let's say that AID = null, but if I pass this to my request, I get an error message ?!

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

Okay, so my next question will be how do I pass a boolean variable?

In my stored procedure, var @SearchChildren is either true or false, but how can I determine this or should I go with int and make things simple for myself and just use 0 or 1?

MS SQL Server 2005.

Answer 1

Change the Search_Res stored procedure Search_Res as optional. @parameter = NULL. When a NULL value is passed, the parameter is ignored.

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

Answer 2

It looks like you are trying to run SP using an ad-hoc request, not an ADO.Net DBCommand object. Can you add "@SearchChildren = null" to your line?

You can also set parameter values ​​explicitly using the command object, this is relatively simple.

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

Sorry C # ... my VB is rusty.

Answer 3

In this case, since you pass the procedure call and arguments as literals, you want the AID contain the string value "null" rather than a null value.

As an alternative, you should consider using snapping options. It sounds like your calling language is VB.NET, so I don’t know exactly how to do this in this language, but there are many links.

I have not used SQL Server, but I know that in Oracle it is basically illegal to pass logical variables to stored procedures. It is recommended to use INT with 0 or 1.