Calling stored procedures, passing NULL values, dealing with boolean values
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?
W开发者_开发知识库ithin 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.
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.
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 ...
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.
精彩评论