Passing dates as varchar to a Stored Procedure in SQL Server

140 Views Asked by At

I have a Stored Procedure that accepts parameters like so :

ALTER PROCEDURE [DataExtract].[USP_GET_ACTIVATIONS]
(
  @UNITID INT = NULL,
  @STARTDATE VARCHAR(30) = NULL,
  @ENDDATE VARCHAR(30) = NULL,
  @TEST BIT = 0
)
AS
BEGIN
......

When I execute this from the SQL Server console, it returns the data I expect.

EXEC    [DataExtract].[USP_GET_ACTIVATIONS]
        @UNITID = 1,
        @STARTDATE = N'2020-06-29',
        @ENDDATE = N'2020-07-08',
        @TEST = 1
GO

I am trying to invoke this procedure using this callable statement

final SQLServerConnection connection = (SQLServerConnection) ds.getConnection();
SQLServerCallableStatement callableStatement = (SQLServerCallableStatement) connection.prepareCall("{call [DataExtract].[USP_GET_ACTIVATIONS](?, ?, ?, ?)}");
callableStatement.setInt(1, 1);
callableStatement.setString(2, "2020-06-29");
callableStatement.setString(3, "2020-07-08");
callableStatement.setBoolean(4, true);
final boolean execute = callableStatement.execute();
//execute is always false

The statement always returns the result as false (and the resultSet null). I'm guessing there's something wrong with the way I'm passing dates, but I can't figure out what.

How do I go about fixing this issue? (Changing the SP is not an option since there are many other components that depend on this)

0

There are 0 best solutions below