2

I'm getting an intermittent null value for an output parameter for a stored procedure I have. I'm wondering if it has to do with the NOLOCK inside the stored procedure. It works most of the time, it's intermittently failing. Especially under high load. Most of the time it returns the "y" or "n" you would expect.

 SqlConnection con = getCon();
 SqlCommand cmd = new SqlCommand("loginRecord", con);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add(new SqlParameter("@username", username));
 cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@exists", System.Data.SqlDbType.VarChar, 3, System.Data.ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));

 try
 {
     con.Open();
     cmd.ExecuteNonQuery();
 }
 catch (Exception ex)
 {
     Util.sendErrorEmail(ex.ToString());
}
finally
{
   con.Close();
}

 //The following line is the one that throws an "Object reference not set to an instance of an bject." exception
 string userExists = cmd.Parameters["@exists"].Value.ToString();

Here is the stored procedure:

ALTER PROCEDURE [dbo].[loginRecord]
(
    @username nvarchar(100),
    @exists char(1) OUTPUT
)

AS
IF EXISTS(select username from Users WITH (NOLOCK) where username = @username)
    BEGIN
        set @exists='y'
    END
ELSE
    BEGIN
        set @exists='n'

        --insert user account--
        insert into Users (username, datejoined)
        values (@username, getdate())
    END
insert into Logins (username, logged)
values (@username, getdate())

GO
p.campbell
  • 98,673
  • 67
  • 256
  • 322
Robert
  • 568
  • 1
  • 6
  • 21
  • When it fails (returns null), what should it have returned? Does it always fail for Y or N situations, or for both? – Philip Kelley Aug 12 '11 at 15:59
  • If you look at the stored procedure there is an if/else and it seems like the value has to be set no matter what. It's supposed to insert into Users table if the person doesn't exist yet. – Robert Aug 12 '11 at 16:45
  • Yes, but I'm trying to determine if the problem only occurs under one or another circumstance. If it only happens for one or the other situation, it's a simpler problem to resolve. – Philip Kelley Aug 12 '11 at 16:52
  • 2
    This is not thread safe. Two concurrent transactions can both read that the same user name does not exist and both proceed to attempting to insert it. Is there a unique index on `Users.username`? – Martin Smith Aug 12 '11 at 22:59
  • Martin yes there is a primary key for Users.username. I can see what's going on here now. My question to you is what can I do about it? 1. Is there a way to make it "thread safe"? Could you help me understand that? 2. Is there a way to have the stored proc detect that the username already exists when it does the user insert and then return "y" and also still insert into the logins table? Thank You! – Robert Aug 14 '11 at 22:58
  • @Robert - You can use `MERGE` if you are on SQL Server 2008 or just do the insert and catch the duplicate key error similar to the solution in [this answer](http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there/3408196#3408196) – Martin Smith Aug 15 '11 at 22:07
  • Martin thank you very much. How can I mark your comment as the answer for this thread? – Robert Aug 18 '11 at 02:36

2 Answers2

1

My guess is that an exception is occurring before @exists is assigned a value. I'd change my catch to be:

Catch(Exception ex) { Util.sendErrorEmail(ex.ToString()); return; }

Jeff Siver
  • 7,434
  • 30
  • 32
  • The exception is not caught in there, it doesn't fail until the line I specify there I have ELMAH logging and it shows me that the line after the whole try/catch/finally is the line that fails. – Robert Aug 12 '11 at 16:45
  • ELMAH, unless you have changed it's default behavior, logs unhandled exceptions. Because of your Catch loop, any exception that occurs is handled and will not be logged. Could you also post what exception you are getting? – Jeff Siver Aug 12 '11 at 18:26
  • Jeff I see what you're saying yes the stored proc must be throwing an exception. From looking at the comment above "Martin Smith" says that the stored proc is not thread safe. I'm willing to bet that it's trying to insert a duplicate username in there. I'm going to ask him how to fix that. – Robert Aug 14 '11 at 22:57
0

I think it has nothing to do with stored procedure implementation. Event if you deliberately return null for @exists variable from stored procedure, "cmd.Parameters["@exists"].Value" will not be null in C#. Instead it will be 'System.DBNull' which is a valid object, you can call methods on.

This is not an direct answer to the question but it will help you narrow it down.

A. Lat
  • 11
  • 1