CODEWITHSUNDEEP
c#sql-serverstored-proceduresdapper
Maurice Klimek
Maurice Klimek

Reputation: 1092

How to pass a DateTime into a Stored Procedure using Dapper?

I have this Stored Procedure:

CREATE PROCEDURE [dbo].[IsDateInBetween]
    @someDateTime datetime
AS
BEGIN
    DECLARE @Exists INT

    IF EXISTS(SELECT Id FROM [dbo].[SomeTable] 
               WHERE StartDate < @someDateTime AND EndDate > @someDateTime)
    BEGIN
        SET @Exists = 1
    END
    ELSE
    BEGIN
        SET @Exists = 0
    END

    RETURN @Exists
END

When I call it:

DECLARE @return_value int

EXEC    @return_value = [dbo].[IsDateInBetween]
        @someDateTime = N'2020-07-14'

SELECT  'Return Value' = @return_value

GO

I receive 1, which is expected.

But then I try to call it in C#:

var result = DbConnection.ExecuteScalar<int>("dbo.IsDateInBetween", new { new DateTime(2020, 7, 14) }, commandType: CommandType.StoredProcedure);

And I get 0.

I tried to change the passed object to DynamicParameters, like that:

var parameters = new DynamicParameters();
parameters.Add("someDateTime", faultDateTime, DbType.DateTime);

But it didn't help.

I tried both DbType.DateTime and DbType.DateTime2, as well as parameter name starting with @ and without it. Both columns from the stored procedure are of type datetime.

What am I doing wrong?

Upvotes: 0

Views: 1838

Answers (2)

Preben Huybrechts
Preben Huybrechts

Reputation: 6141

You could use Query or QueryAsync Or any of the other query methods like QueryFirst or QueryFirstOrDefault. ExecuteScalar is an ADO.net method and also exists in dapper so it might not be clear witch one you are using.

For dapper try

DbConnection.QueryFirst<int>("EXEC dbo.IsDateInBetween @someDate", new { someDate = new DateTime(2020, 7, 14) });

Why use Query() instead of Execute()? You expect a return value.

Also you should specify the name of the parameter in your query and in your anonymous type.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82524

You just need to give your parameter the correct name:

var result = DbConnection.ExecuteScalar<int>(
    "IsDateInBetween", 
    new { someDateTime = new DateTime(2020, 7, 14) }, 
    commandType: CommandType.StoredProcedure);

Also, Unless you're executing a schema that is different than the login's default schema, you don't need to mention it, the procedure name is enough.

And another thing - the problem is probably in the stored procedure itself. You're not suppose to use Return - you're suppose to either use an output parameter or a simple select.

Try this procedure instead:

CREATE OR ALTER PROCEDURE [dbo].[IsDateInBetween]
    @someDateTime datetime
AS
BEGIN
    SELECT CASE WHEN EXISTS(
        SELECT Id 
        FROM [dbo].[SomeTable] 
        WHERE StartDate < @someDateTime 
        AND EndDate > @someDateTime
    )
    THEN 1
    ELSE 0
    END
END

Upvotes: 1

Related Questions