Stored Procedure as method of Connection and NULLs

[Dave Anderson]

[This question is directed at Bob Barrows, but perhaps someone else knows an
answer. In any case it is good information for the group.]

I really like Bob's "stored procedure as method of Connection Object"
technique. It is convenient, compact, and concise, and simplifies protection
from SQL injection.

HOWEVER, I cannot figure out a way to pass a null value to an INT parameter
when I use this. Null string parameters are fine. The error reads:

Microsoft OLE DB Provider for SQL Server error '80040e07'
Operand type clash: text is incompatible with int

As you can imagine, "text is incompatible with int" gets my attention. I am
explicitly passing a null (not the string "null"). I am using JScript on the
web server and connecting to SQL Server 2000. An example:

var CN = Server.CreateObject("ADODB.Connection"),
PCTagNumber = +Request.Form("PCTagNumber").Item || null,
DeskLocation = Request.Form("DeskLocation").Item || null

CN.Open(CNString)
CN.Inventory_Update(HR.UserID,PCTagNumber,DeskLocation)

Assume the stored procedure looks something like:

CREATE PROCEDURE dbo.Inventory_Update(
@UserID VARCHAR(50),
@TagNumber INT,
@DeskLocation VARCHAR(255)
) AS ...

In the above case, a null tag number produces the error, while a null desk
location results in a SQL NULL passed to the parameter @DeskLocation.

Is there a way for me to pass a null integer here, or is this a limitation
of Bob's technique?



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Dave Anderson]

I cannot figure out a way to pass a null value to an INT
parameter when I use this.

Not looking good for me:

"To execute a stored procedure, issue a statement where
the stored procedure name is used as if it were a method
on the Connection object, followed by any parameters.
ADO will make a "best guess" of parameter types."

http://msdn.microsoft.com/library/en-us/ado270/htm/mdobjconnection.asp



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Bob Barrows [MVP]]

Not looking good for me:

"To execute a stored procedure, issue a statement where
the stored procedure name is used as if it were a method
on the Connection object, followed by any parameters.
ADO will make a "best guess" of parameter types."

http://msdn.microsoft.com/library/en-us/ado270/htm/mdobjconnection.asp

Yes, I am having the same issue. I can only come up with two workarounds:
1.
Declare the parameter with a default so it is optional, making it the last
parameter to be declared, then
if (PCTagNumber==null)
CN.Inventory_Update(HR.UserID,DeskLocation)
else
CN.Inventory_Update(HR.UserID,DeskLocation,PCTagNumber)

Drawbacks
- I only succeeded in omitting the last argument
- It doesn't help with more than one nullable parameter

2.
Use a "magic" number instead of null, testing for that value in the
procedure
Drawbacks
- do I need to enumerate them?

I even tried using the parameter marker technique, which suffered the same
fate (I somewhat expected that, suspecting that the same stuff was happening
behind the scenes to make both techniques work).

Oh well, I guess it's back to the explicit Parameters collection for these
situations (where int parameters are nullable)

Bob