Using old-timey classic ADO, not ADO.NET, how do you pass a date value to a stored procedure from JavaScript? The JS is running in a classic ASP page on IIS7. SQL Server is 2012 (1).
SOLVED: See answer below. Long story short, the results were finding their way back to me via a JSON stringifier that ignored properties with variant date values.
I have a stored procedure in SQL Server:
create procedure test(@n int, @d datetime)
as
begin
select @n n, @d d;
end;
And I have some JavaScript code in a classic ASP page:
var conn = new ActiveXObject("ADODB.Connection");
var cmd = new ActiveXObject("ADODB.Command");
conn.Open(connectionString);
cmd.ActiveConnection = conn;
cmd.CommandType = adCmdStoredProc;
cmd.CommandText = 'dbo.test';
cmd.Parameters.Append(cmd.CreateParameter('@n', adInteger, adParamInput, 4, 123));
var param = cmd.CreateParameter('@d', adDate, adParamInput);
param.Value = (new Date('01/01/2000')).getVarDate();
cmd.Parameters.Append(param);
var rs = cmd.Execute();
What I get back from the SP always has the expected value for @n
(123, above), and always has null
for @d
. connectionString
must be OK because it does call the SP, and it's definitely the SP I think I'm calling; if I make changes to it, they are reflected in what comes back.
I get the getVarDate()
from Eric Lippert's answer here. I have also tried adDBDate
with various data types.
I wrote the above test SP for testing purposes; you may have noticed that it doesn't do very much useful work. In production, I have to pass a date to an existing SP. The details of that SP wouldn't lend much clarity to this question. I can, if I absolutely must, write a wrapper SP that receives a date as a string and converts it. But I want to understand what is wrong here, and we have more than enough semi-redundant SPs cluttering up the database already. And that's just a horrible way to do things.
(1) @@version = 'Microsoft SQL Server 2012 (SP1) - 11.0.3381.0 (X64) Aug 23 2013 20:08:13 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2) (Hypervisor)'