Untitled

Passing date as input parameter to mssql stored procedure through PHP

//initiate function

    $proc = mssql_init('usp_Update_Certificate_Customer_Details', $msdb);

 

    //define parameters

    $telId = $_POST['telId'];

    $certNumber = $_POST['certNumber'];

    $custTel = $_POST['main_tel'];

    $instRef = $_POST['install_ref'];

    //$commDate = $_POST['comm_date'];

    $standards = $_POST['standards'];

    $commDate = date('Y-m-d h:i:s');

    echo 'telid: '.$telId.' certNumber: '.$certNumber.' custTel: '.$custTel.' instRef: '.$instRef.' commDate: '.$commDate.' standards: '.$standards;

    mssql_bind($proc, '@Telephone_ID', $telId, SQLINT4, false, false, 10);

    mssql_bind($proc, '@Certificate_Number', $certNumber, SQLINT4, false, false, 10);

    mssql_bind($proc, '@Customer_Telephone', $custTel, SQLVARCHAR, false, false, 10);

    mssql_bind($proc, '@Installers_Reference', $instRef, SQLVARCHAR, false, false, 10);

    mssql_bind($proc, '@Commisioned_Date', $commDate, SQLDATETIME, false, false, 10);

    mssql_bind($proc, '@Installed_to_Standards', $standards, SQLVARCHAR, false, false, 10);

 

    //Execute Procedure

    $result = mssql_execute($proc);

 

    //Free Memory

    mssql_free_statement($proc);

       

Warning: mssql_bind() expects parameter 4 to be long, string given

       

SET ANSI_NULLS ON

 

GO

 

SET QUOTED_IDENTIFIER ON

 

GO

 

-- =============================================

 

-- Author:        Phil Yeomn

 

-- Create date: 19-04-2008

 

-- Description:   Update Customer Details

 

-- =============================================

 

ALTER PROCEDURE [dbo].[usp_Update_Certificate_Customer_Details](

 

@Telephone_ID INT,

 

@Certificate_Number Int,

 

@Customer_Telephone Varchar(50),

 

@Installers_Reference Varchar(50),

 

@Commisioned_Date Datetime,

 

@Installed_To_Standards Varchar(50)

 

)

 

 

 

AS

 

BEGIN

 

SET NOCOUNT ON;

 

 

 

IF @Telephone_ID <> 0 AND @Customer_Telephone IS NULL

 

BEGIN

 

      DELETE FROM ssaib.dbo.Telephone_T

 

      WHERE Telephone_ID = @Telephone_ID

 

 

 

      SET @Telephone_ID = NULL

 

END

 

 

 

IF @Telephone_ID = 0 AND @Customer_Telephone IS NOT NULL

 

BEGIN

 

INSERT INTO       ssaib.dbo.Telephone_T (Telephone_Number_VC, Last_Update_DT)

 

VALUES               (@Customer_Telephone, GETDATE())

 

 

 

SET @Telephone_ID = SCOPE_IDENTITY()

 

END

 

 

 

IF @Telephone_ID = 0

 

BEGIN

 

SET @Telephone_ID = NULL

 

END

 

 

 

UPDATE      ssaib.dbo.Certificate_Returns_T

 

SET         Customer_Telephone_ID = @Telephone_ID,

 

            Installers_Reference_VC = @Installers_Reference,

 

            Commisioned_Date_DT = @Commisioned_Date,

 

            Installed_To_Standards_VC = @Installed_To_Standards

 

WHERE  (Certificate_Return_ID = @Certificate_Number)

 

 

 

 

 

END

       

telid: 61529 certNumber: 1262789 custTel: 01423 734002 instRef: /3548 commDate: 2012-05-05 00:00:00 standards: PD 6662 and DD 243

Warning: mssql_bind() expects parameter 4 to be long, string given in /home/ssaibuk/public_html/common/modules/supplier/certificates-direct.php on line 111

 

Warning: mssql_execute() [function.mssql-execute]: message: Procedure or function 'usp_Update_Certificate_Customer_Details' expects parameter '@Commisioned_Date', which was not supplied. (severity 16) in /home/ssaibuk/public_html/common/modules/supplier/certificates-direct.php on line 116

 

Warning: mssql_execute() [function.mssql-execute]: General SQL Server error: Check messages from the SQL Server (severity 16) in /home/ssaibuk/public_html/common/modules/supplier/certificates-direct.php on line 116

 

Warning: mssql_execute() [function.mssql-execute]: stored procedure execution failed in /home/ssaibuk/public_html/common/modules/supplier/certificates-direct.php on line 116

       

CREATE PROCEDURE [dbo].[WorkingWithDateObjects]

-- Add the parameters for the stored procedure here

 

@dateString [varchar] (19) = null, -- ex: '2009-07-01 00:00:00.000'

@dateObject [datetime] = null -- computed in sp from @dateString

 

AS

 

BEGIN

SET NOCOUNT ON;

SET @dateObject = CONVERT(VARCHAR(19),@dateString,120)

 

    SELECT *

 

    FROM SomeTable

 

    WHERE DateField >= @dateObject

 

  END

       

$commDate = date('Y-m-d H:i:s');