Ideally, I'm trying to get a stored procedure to return 1 if exists, or 0 if not.
This is the stored procedure:
CREATE PROCEDURE [dbo].[spCheckForExistingTimecard]
@userId int,
@paYPeriodId int,
@exists bit = 0 OUTPUT
AS
BEGIN
IF EXISTS (SELECT COUNT (t.TimeCardId)
FROM TimeCard AS t
WHERE t.PayPeriodId = @payPeriodId
AND t.UserId = @userId )
RETURN 1
ELSE
RETURN 0
Here's the code calling the stored procedure:
public static int CheckForExistingTimecard(int userId, int payPeriodId)
{
using (SqlConnection connection = new SqlConnection(dbMaintenanceConnectionString))
{
connection.Open();
using (SqlCommand sqlCommand = new SqlCommand("spCheckForExistingTimecard", connection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("@userId", userId);
sqlCommand.Parameters.AddWithValue("@payPeriodId", payPeriodId);
return (int)sqlCommand.ExecuteScalar();
}
}
}
Problem is that I am getting an error
Object reference not set to an instance of an object
on the return line of the calling code.
Any help would be greatly appreciated
As documeneted in officeil site
The first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty. Returns a maximum of 2033 characters.
ExecuteScalar returns null if no records were returned by the query
So this line:
return (int)sqlCommand.ExecuteScalar();
throws error
becaue it is trying to cast null to an int in that case. That'll raise a NullReferenceException.
you need to check for null:
object o = sqlCommand.ExecuteScalar();
item = o == null ? 0 : (int)o;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments