[dba-SQLServer] Nulls in sprocs

Dear Group,

I may have figured something out with respect to this NULL business. It might have been a red herring.

What I was trying to do was use a procedure in one of the O'Reilly .NET books that showed how to use a Transaction to commit and rollback a parameterized sproc. I followed the example using my own data and sproc as closely as I could, but the Update method that executed the sproc always returned 0 as the number of rows updated. I figured that it was because some of the values for the sproc parameters were NULL. Then I got a curveball thrown at me when I used QA to EXEC my sproc with literal values for the parameters. I found that I could simply type in a date as a string enclosed in quotes in the EXEC and the sproc would do it's thing just fine.

Now I see in another book (The Murach ADO.NET book) that a Transaction can be wrapped around the ExecuteNonQuery method besides the Update method. So, instead of having to involve a SqlDataAdapter object and executing its Update method, I can simplify matters a bit and construct the UPDATE query in code, use the ExecuteNonQuery method of the SqlCommand object, and THEN attempt the Commit. I haven't actually modified my code to do this, but that's what I'm about to do now.

Thanks for your help, David and Doris and Francis. I understand more about NULLs in sprocs now...though I'm baffled by this CAST/CONVERT business in QA.

Steve Erbach
Neenah, WI

> ------------Original Message------------
> From: "Steven W. Erbach" <serbach at new.rr.com>
> To: dba-sqlserver at databaseadvisors.com
> Date: Wed, Jan-12-2005 7:50 AM
> Subject: [dba-SQLServer] Nulls in sprocs
> 
> Dear Group,
> 
> I've run into a problem with UPDATE stored procedures that puzzles me.