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.

womp · Accepted Answer · 2010-01-21 21:50:41Z

8

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.

answered Jan 21, 2010 at 21:50

womp

117k27 gold badges240 silver badges271 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yada · Accepted Answer · 2010-01-22 03:14:21Z

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 ...

edited Jan 22, 2010 at 3:14

answered Jan 21, 2010 at 21:40

Yada

31.5k25 gold badges105 silver badges145 bronze badges

1 Comment

flavour404 Over a year ago

Actually this was the basis for the answer that I used so I you get the 'tick.' Thanks to everybody who answered.

Dan · Accepted Answer · 2010-01-21 21:46:21Z

0

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.

answered Jan 21, 2010 at 21:46

Dan

11.1k7 gold badges55 silver badges82 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related