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.
Author |
Topic |
swenri
Yak Posting Veteran
72 Posts |
Posted - 2012-12-27 : 15:33:03
|
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]GOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MMC_SP_Report]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[MMC_SP_Report]GOUSE [DatamartDB2]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROC [dbo].[MMC_SP_Report] ASBEGINSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDDECLARE @Location varchar(255)--if (ISNULL(@Location,'')='') begin Declare @FromDate datetimeDeclare @ToDate datetimeDeclare @RptTimeFrameout VARCHAR(50) SET @RptTimeFrameout='2' --Weeklyif @RptTimeFrameout='2' -- WeeklyBeginselect @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_Bondingfrom IPR_Neonate_Interventions_T_Delivery_Report_Neonatewhere 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_Neowhere 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_Methodfrom IPR_Feeding_Method_Twhere MO_Demographics.SessionID = IPR_Feeding_Method_T.SessionID) AS Feeding_Methodin_LDR FROM MO_DemographicsLEFT OUTER JOIN MO_NeonateON "MO_Demographics"."SessionID"="MO_Neonate"."sessionid" LEFT OUTER JOIN BLSession_ExtendedON "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 @ToDateORDER BY MO_Demographics.MRN,"SPDeliveryLog_Table"."TimeOfDelivery" ASCENDENDSET NOCOUNT OFFSET ANSI_NULLS OFF GOGOGRANT EXECUTE ON [dbo].[MMC_SP_Report] TO [Public] |
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-12-28 : 08:23:18
|
You could add 2 output parameters called @FromDate and @ToDate, and read them from there.How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
mandm
Posting Yak Master
120 Posts |
Posted - 2012-12-28 : 11:26:54
|
You could also send it back as part of the data set.SELECT@FromDate AS FromDate,@ToDate AS Todate,("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_Bondingfrom IPR_Neonate_Interventions_T_Delivery_Report_Neonatewhere 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_Neowhere MO_Demographics.SessionID = IPR_General_Description_FU_Oth_Int_Del_Rep_Neo.SessionID) AS Other,"MO_Neonate"."InfantDisposition",etc... |
|
|
|
|
|
|
|