Please help !! - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Please help !!

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]
GO

IF 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]
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_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_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",

etc...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources