Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Passing Dates to a Stored Procedure

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-07-31 : 05:30:26
Pari writes "I take a date from the html form and wish to pass on to the stored procedure where the value is inserted . . .

Article Link.

jhermiz

3564 Posts

Posted - 2004-10-09 : 14:03:14
quote:
Originally posted by AskSQLTeam

Pari writes "I take a date from the html form and wish to pass on to the stored procedure where the value is inserted . . .<BR><P>Article <a href="/item.asp?ItemID=226">Link</a>.



Isn't it @@Error ?

I think @Error will throw an error in the sproc..

try this:


CREATE PROC update_issue_by_issue_id (
@IssueID bigint,
@CustomerID bigint,
@SiteID bigint=NULL,
@CommissionID bigint=NULL,
@IssueTypeID bigint=NULL,
@Originator varchar(50),
@TargetDate datetime,
@ClosedDate varchar(40)=NULL,
@Issue varchar(8000),
@ModifiedBy varchar(50),
@ModifiedOn datetime
)
--sproc: insert_issue
--Created By: Jon Hermiz
--Created On: October 8, 2004
--Modified By:
--Modified On:
--Reason For Modification:
--Function: To insert an issue
AS
DECLARE @CD DATETIME
SELECT @CD = CONVERT(datetime, @ClosedDate)

IF @@ERROR <> 0
/* no closed date */
UPDATE Issue SET
CustomerID=@CustomerID,
SiteID=@SiteID,
CommissionID=@CommissionID,
IssueTypeID=@IssueTypeID,
Originator=@Originator,
TargetDate=@TargetDate,
Issue=@Issue,
ModifiedBy=@ModifiedBy,
ModifiedOn=@ModifiedOn
WHERE IssueID=@IssueID
ELSE
/*with closed date*/
UPDATE Issue SET
CustomerID=@CustomerID,
SiteID=@SiteID,
CommissionID=@CommissionID,
IssueTypeID=@IssueTypeID,
Originator=@Originator,
TargetDate=@TargetDate,
ClosedDate=@CD,
Issue=@Issue,
ModifiedBy=@ModifiedBy,
ModifiedOn=@ModifiedOn
WHERE IssueID=@IssueID
GO


Go to Top of Page

Jonu2
Starting Member

2 Posts

Posted - 2009-11-23 : 20:11:18
Hi, I'm very new to Sql and Crystal Reporting but am trying to enhance a report so that if no date range is input then it will default to a fromdate of six months previous and a todate of six months in the future.
The sp used starts with the below code and I only changed the last bit highlighted in red. When I run it, it gives me an error indicating that it doesn't know what DATETIME is - it doesn't seem to recognise DATETIME. I know I'm probably doing something very basic worng but I'd really appreciate being pointed in the right direction please, many thanks:

ALTER procedure [dbo].[NCS_AuditReport_Sp]
@ServiceCustomerID nvarchar(30),
@ResourceID nvarchar(30),-- Auditor
@Sector varchar(40), -- Customer Group
@State nvarchar(16), -- ServiceStateCode
@TaskName varchar(100),
@WorkTypeCode varchar(1000),
@FromDate datetime,
@ToDate datetime,
@Standard nvarchar(20) --MOD005

as

/*
** Confidential Information
** Limited Distribution of Authorized Persons Only
** Created 2005 and Protected as Unpublished Work
** Under the U.S. Copyright Act of 1976
** Copyright (c) 2001-2005 Epicor Software Corporation
** All Rights Reserved
**
** Rev. Name Date Reference Description
** ****** ********** ********** ************** ********************
** MOD001 TJK 2007-08-28 N/A Re-write the whole thing
** MOD002 TJK 2007-11-30 N/A Correct the join to the Client Infopath tables
** MOD003 Marketta R 2009-01-19 Change Division codes to names
**
** EXAMPLE Procedure Call

EXEC "NCS_AuditReport_Sp"
NULL,
'DPOWER',
NULL,
NULL,
NULL,
NULL,
'2007-07-01 00:00:000',
'2007-08-31 00:00:000',
NULL

EXEC "NCS_AuditReport_Sp"
'12023000',
'DPOWER',
'522',
'SA',
'EMS',
'40QAS',
'2007-07-01 00:00:000',
'2007-08-31 00:00:000',
'EMS'

*/


-- Creating a temp table to hold records for the audit report
create table #AuditReport (
ProjectCode varchar(40),
ServiceCustomerID nvarchar(30),
ServiceCustomerName nvarchar(240),
BillToID nvarchar(30),
BillToName nvarchar(240),
Status char,
ServiceStandard nvarchar(40),
ServiceStateCode nvarchar(16),
SectorCode nvarchar(12),
SectorName nvarchar(60),
ClientManagerID nvarchar(30),
ClientManagerFirstName varchar(120),
ClientManagerLastName varchar(120),
TaskUID int,
TaskName nvarchar(120),
PlannedHours decimal(8,2),
BookedHours decimal(8,2),
WorkTypeCode varchar(24),
WorkTypeDesc varchar(60),
StartDate datetime,
EndDate datetime,
CompleteFlag int,
City nvarchar(50),
ProjectPrimaryResource varchar(120), --MOD001
)

-- MOD001 Update client manager name
select distinct client_code, client_manager
into #ClientManager
FROM ncs_client_certification (NOLOCK)
WHERE client_manager IS NOT NULL
--MOD007 WHERE Client_code like @ServiceCustomer

-- Get WHERE clause parameter
declare @ServiceCustomer nvarchar(100),
@Auditor nvarchar(100),
@CustomerGroup varchar(100),
@ServiceState nvarchar(100),
@TaskDesc varchar(2000),
@WorkType nvarchar(200),
@Stand nvarchar(100)

-- If dates are blank then set dates as 6 months ago to 6 months forward

if (@FromDate = 0 or @FromDate ='' or @FromDate is null or @FromDate = '1900-01-01 00:00:00.000')
and (@ToDate = 0 or @ToDate ='' or @ToDate is null or @ToDate = '1900-01-01 00:00:00.000')
begin
select dateadd(month,-6,datetime) as StartDate
select dateadd(month,6,datetime) as EndDate
end
Go to Top of Page
   

- Advertisement -