How to display dates used in stored procedure in crystal report ?

[swenri]

Hi all,

I have a stored procedure written in sql server 2008 @Fromdate and @ToDate which are variables in the stored preocedure. The SP works fine. How do i display the @Fromdate and @ToDate in the crystal report ? Below is the stored procedure that I wrote. This is extremely urgent...Thank you for the help.....


USE [DatamartDB2]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MMC_SP_BreastFeedingLDRReport]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MMC_SP_BreastFeedingLDRReport]
GO

USE [DatamartDB2]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[MMC_SP_Report]

AS

BEGIN

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @Location varchar(255)

--if (ISNULL(@Location,'')='')
begin


Declare @FromDate datetime
Declare @ToDate datetime
Declare @RptTimeFrameout VARCHAR(50)

SET @RptTimeFrameout='2' --Weekly
if @RptTimeFrameout='2' -- Weekly
Begin
select @FromDate =convert(datetime,convert(varchar(10),dateadd(dd,-7, getdate()), 101))
select @ToDate=dateadd(ss, -1, convert(datetime,convert(varchar(10), getdate(),101)))
End


SELECT
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientName,
("MO_Demographics".DateOfBirth) AS DOB,
"MO_Demographics"."MRN",
"MO_Demographics"."Race",

("SPDeliveryLog_Table"."GA") AS Gestational_Age,
("SPDeliveryLog_Table"."TimeOfDelivery") AS DeliveryTime,

("SPDeliveryLog_Table"."NeonateGender") AS NeonateSex,
("SPDeliveryLog_Table"."OB Formula TPAL (postpartum)") AS Obstetric_History,
("SPDeliveryLog_Table"."Delivering Providers") AS Delivering_MD_CNM_Attending_Surgeon,
("SPDeliveryLog_Table"."Assistants") AS Delivering_MD_CNM_Assitant_Surgeon,
("SPDeliveryLog_Table"."Nurses") AS Primary_Nurse_Circulating_Nurse,
("SPDeliveryLog_Table"."Type of Delivery") AS TypeofDelivery,
"SPDeliveryLog_Table"."FetalPresentation",

("MO_Neonate"."LiveBornStillBorn") AS LiveBorn,
"MO_Neonate"."BirthWeightGrams",
"SPDeliveryLog_Table"."BirthWeight(lb+oz)" AS BirthWeight,
"MO_Neonate"."Apgar1Min",
"MO_Neonate"."Apgar5Min",
"MO_Neonate"."Apgar10Min",

(select top 1 IPR_Neonate_Interventions_T_Delivery_Report_Neonate.Mom_And_Infant_Bonding
from IPR_Neonate_Interventions_T_Delivery_Report_Neonate
where MO_Demographics.SessionID = IPR_Neonate_Interventions_T_Delivery_Report_Neonate.SessionID) AS Moms_abdomen_directly,

(select top 1 IPR_General_Description_FU_Oth_Int_Del_Rep_Neo.[General_Description_f_Other_Intervention_Delivery__1_0]
from IPR_General_Description_FU_Oth_Int_Del_Rep_Neo
where MO_Demographics.SessionID = IPR_General_Description_FU_Oth_Int_Del_Rep_Neo.SessionID) AS Other,


"MO_Neonate"."InfantDisposition",

(select top 1 IPR_Feeding_Method_T.Infant_Feeding_Method
from IPR_Feeding_Method_T
where MO_Demographics.SessionID = IPR_Feeding_Method_T.SessionID) AS Feeding_Methodin_LDR

FROM
MO_Demographics
LEFT OUTER JOIN MO_Neonate
ON "MO_Demographics"."SessionID"="MO_Neonate"."sessionid"


LEFT OUTER JOIN BLSession_Extended
ON "MO_Demographics"."SessionID"= "BLSession_Extended"."sessionid"


LEFT OUTER JOIN "SPDeliveryLog_Table"
ON "MO_Demographics"."SessionID" = "SPDeliveryLog_Table"."sessionid"

LEFT OUTER JOIN "IPR_General_Description_FU_Oth_Int_Del_Rep_Neo"
ON "MO_Demographics"."SessionID" = "IPR_General_Description_FU_Oth_Int_Del_Rep_Neo"."sessionid"



WHERE SPDeliveryLog_Table.TimeOfDelivery
--BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-7,getdate()),101))+ '00:00:00 AM')
--AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'11:59:59 PM')
BETWEEN @FromDate AND @ToDate

ORDER BY MO_Demographics.MRN,
"SPDeliveryLog_Table"."TimeOfDelivery" ASC


END


END

SET NOCOUNT OFF

SET ANSI_NULLS OFF

GO

GO



GRANT EXECUTE ON [dbo].[MMC_SP_BreastFeedingLDRReport] TO [Public]

 

[SQLScholar]

This sounds to me that it is nothing to do with reporting services. I would repost this in the crystal forum.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------