CODEWITHSUNDEEP
c#asp.netstored-proceduressql-server-2008-r2
Hitin
Hitin

Reputation: 442

SQL Server stored procedure returns -1 in codebehind

I am not able to figure out what could be the reason for getting -1 for my stored procedure.

Here code behind code (C#):

SqlCommand _command = _connection.CreateCommand();
_command.CommandText = "usp_ZX_GetValidToken";
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@tokenstring", tokenString);
_command.Parameters.AddWithValue("@difference", lifeInSeconds);    
Object _response = _command.ExecuteNonQuery();

Here is my stored procedure code:

CREATE procedure [dbo].[usp_ZX_GetValidToken]
    @tokenstring nvarchar(50), @difference int
AS
    IF EXISTS(SELECT * FROM AppAccessTokens 
              WHERE Consumed = 0 AND GUIDToken = RTRIM(@tokenstring) 
                AND DATEDIFF(SECOND, CreateDateTime, GETDATE()) <= @difference)
       RETURN 1;
    ELSE
       RETURN -1;

Oh yes! I also make sure that my table have correct data, and I always execute below code before calling procedure:

update AppAccessTokens
set CreateDateTime = GETDATE()

The _response from the above C# code is always -1, I am really not able to figure out this on my own. I need a fresh eye to this one.

I have tried restarting machine, server, and IIS. (I know its doesn't make sense) But nothing changes, it keeps returning -1 at all times.

Can anyone suggest what should I do?

Upvotes: 1

Views: 666

Answers (1)

Rahul Singh
Rahul Singh

Reputation: 21795

That's because when your SP is executed it will not affect any rows so ExecuteNonQuery will return 0 always. Change your SP like this:-

IF EXISTS(SELECT * FROM AppAccessTokens WHERE Consumed = 0 
                  AND GUIDToken = RTRIM(@tokenstring) AND
 DATEDIFF(SECOND, CreateDateTime, GETDATE()) <= @difference)
 SELECT 1;
 ELSE
 SELECT -1;

Then execute it with ExecuteScalar which will read the first row of first column and we are only selecting 1 value in our SP so _response will get correct value:-

int _response = Convert.ToInt32(_command.ExecuteScalar());

Alternatively, you can also use Output parameters.

Upvotes: 2

Related Questions