Hi All: I am trying to publish a SQL store procedure with multiple arguments in it in Reporting Services but getting an error message. This Store Procedure runs perfectly fine in management studio but I am not able to deploy this to reporting services. I would appreciate if anyone can guide me how to correct this issue. I can provide error image later on. Thanks in advance. Haseef.

I am trying to publish a SQL store procedure with multiple arguments in it in Reporting Services Hello Haseef, I don't understand; you want to deploy a Stored Procedure to SSRS? You can only deploy reports to SSRS, but no SQL Server objects like a Stored Procedure. Or do you want to deploy a report, which uses a stored procedure? If so, which exact error message do ou get?Olaf Helper Blog Xing

Hey, I am trying to publish a report which uses store procedure which takes parameter such as from date and thru date. Thanks.

And could you please provide the exact error message?Olaf Helper Blog Xing

Hey Olaf, I am not the best when describing a problem so I will try my best. when I run this in management studio it works and I have to use this whole script for the report. My thinking is that if I want this to be run from Reporting services am I posting the whole script or partial? I am not sure. When I posted the whole script I get an Error for @FromDate and @ThruDate which I am not sure how to correct. I wanted to upload the jpeg but microsoft prevents it. Please see the error msg below: Thanks again. Error: "An error occured while executing the query. Incorrect syntax near 'GO'. The variable name '@ FromDate' has already been declared. Variable names must be unique within a query batch or stored procedure. Incorrect syntax near the keyword 'with'. If this statement is common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. (Microsoft SQL Server, Error 102) ========Actual Script =============== IF OBJECT_ID (N'dbo.mtzcus_ED_Throughput_1',N'P') IS NOT NULL DROP PROCEDURE mtzcus_ED_Throughput_1 GO CREATE PROCEDURE mtzcus_ED_Throughput_1 @FromDate DATETIME ,@ThruDate DATETIME ,@Detail CHAR(1) = 'Y' WITH RECOMPILE AS /* Set statements */ SET NOCOUNT ON -- Stops the message that shows the count of rows for faster processing SET ANSI_NULLS ON -- Ensures ISO-compliant behavior of = and <> when used with null values SET QUOTED_IDENTIFIER ON -- Ensures ISO-compliant behavior regarding quotation mark delimiting identifiers and literal strings /******************************************************************/ /***** These variables are to be changed by the report writer *****/ /******************************************************************/ DECLARE @SourceID VARCHAR(5) /* Change to your site-specific values */ SET @SourceID = 'hospital' /* These variables are required for certification and are calculated in the report */ DECLARE @Denominator NUMERIC ,@Exclusions NUMERIC ,@NumeratorExcludingPsychObs NUMERIC ,@NumeratorObs NUMERIC ,@NumeratorPsych NUMERIC /* These variables are hardcoded in the report */ ,@MeasureNumberIdentifier VARCHAR(15) ,@ReportName VARCHAR(50) SET @MeasureNumberIdentifier = 'NQF 0495' SET @ReportName = 'ED-1' /* PATIENT POPULATION */ SELECT AV.VisitID ,AV.AccountNumber ,AV.UnitNumber ,ABSD.Diagnosis ,COALESCE('Flag ' + AV.ObservationPatient, 'DateTime ' + CONVERT(VARCHAR(50), AV.ObservationDateTime, 121)) AS Observation ,AV.ObservationPatient ,AV.ObservationDateTime ,AVS.ArrivalDateTime ,COALESCE(ADC.ErDateTime, ADA.AdmitDateTime) AS DepartOrAdmitDateTime ,DATEDIFF(MINUTE, AVS.ArrivalDateTime, COALESCE(ADC.ErDateTime, ADA.AdmitDateTime)) AS TotalMinutes INTO #TotalPopulation FROM AdmVisits AS AV INNER JOIN AdmittingData AS ADA ON AV.SourceID = ADA.SourceID AND AV.VisitID = ADA.VisitID LEFT OUTER JOIN AdmDischarge AS ADC ON AV.SourceID = ADC.SourceID AND AV.VisitID = ADC.VisitID INNER JOIN AbstractData AS AD ON AV.SourceID = AD.SourceID AND AV.VisitID = AD.VisitID INNER JOIN AdmVitalSigns AS AVS ON AV.SourceID = AVS.SourceID AND AV.VisitID = AVS.VisitID LEFT OUTER JOIN AbsDrgDiagnoses AS ABSD ON AD.SourceID = ABSD.SourceID AND AD.AbstractID = ABSD.AbstractID AND ABSD.DiagnosisSeqID = 1 AND ISNUMERIC(ABSD.Diagnosis) = 1 WHERE AV.SourceID = @SourceID AND ADC.DischargeDateTime BETWEEN @FromDate AND @ThruDate AND AD.AdmitFromEr = 'Y' -- Will have a non-null TotalMinutes calculation AND AVS.ArrivalDateTime IS NOT NULL AND ( ADC.ErDateTime IS NOT NULL OR ADA.AdmitDateTime IS NOT NULL ) -- Must have a diagnosis or be an observation patient AND ( ABSD.Diagnosis IS NOT NULL OR AV.ObservationPatient IS NOT NULL OR AV.ObservationDateTime IS NOT NULL ) /* Create index on TotalPopulation table for faster sorting when calculating medians */ CREATE CLUSTERED INDEX mtzcus_ED1_IdxTotalPopulation ON #TotalPopulation(TotalMinutes) /* FINAL CALCULATION */ SET @Denominator = (SELECT COUNT(VisitID) FROM #TotalPopulation) SET @Exclusions = ( SELECT COUNT(VisitID) FROM #TotalPopulation WHERE Diagnosis NOT BETWEEN '290' AND '319' AND ObservationDateTime IS NULL AND ObservationPatient IS NULL ) /* Calculate median for Non-Psych/Observation patients (top 50% bottom row + bottom 50% top row divided by 2) */ SET @NumeratorExcludingPsychObs = ( SELECT ( ( SELECT MAX(TotalMinutes) FROM ( SELECT TOP 50 PERCENT TotalMinutes FROM #TotalPopulation WHERE Diagnosis NOT BETWEEN '290' AND '319' AND ObservationDateTime IS NULL AND ObservationPatient IS NULL ORDER BY TotalMinutes ) AS BottomHalf ) + ( SELECT MIN(TotalMinutes) FROM ( SELECT TOP 50 PERCENT TotalMinutes FROM #TotalPopulation WHERE Diagnosis NOT BETWEEN '290' AND '319' AND ObservationDateTime IS NULL AND ObservationPatient IS NULL ORDER BY TotalMinutes DESC ) AS TopHalf ) ) / 2 ) /* Calculate median for Observation patients (top 50% bottom row + bottom 50% top row divided by 2) */ SET @NumeratorObs = ( SELECT ( ( SELECT MAX(TotalMinutes) FROM ( SELECT TOP 50 PERCENT TotalMinutes FROM #TotalPopulation WHERE ObservationDateTime IS NOT NULL OR ObservationPatient IS NOT NULL ORDER BY TotalMinutes ) AS BottomHalf ) + ( SELECT MIN(TotalMinutes) FROM ( SELECT TOP 50 PERCENT TotalMinutes FROM #TotalPopulation WHERE ObservationDateTime IS NOT NULL OR ObservationPatient IS NOT NULL ORDER BY TotalMinutes DESC ) AS TopHalf ) ) / 2 ) /* Calculate median for Psych patients (top 50% bottom row + bottom 50% top row divided by 2) */ SET @NumeratorPsych = ( SELECT ( ( SELECT MAX(TotalMinutes) FROM ( SELECT TOP 50 PERCENT TotalMinutes FROM #TotalPopulation WHERE Diagnosis BETWEEN '290' AND '319' ORDER BY TotalMinutes ) AS BottomHalf ) + ( SELECT MIN(TotalMinutes) FROM ( SELECT TOP 50 PERCENT TotalMinutes FROM #TotalPopulation WHERE Diagnosis BETWEEN '290' AND '319' ORDER BY TotalMinutes DESC ) AS TopHalf ) ) / 2 ) /* Output for Attestation */ SELECT @MeasureNumberIdentifier AS [Measure] ,@ReportName AS [Report Name] ,@Denominator AS [Denominator] ,@NumeratorExcludingPsychObs AS [All ED Patients (1-1) Numerator] ,@Exclusions AS [All ED Patients (1-1) Exclusions] ,@NumeratorObs AS [Observation ED Patient (1-2) Numerator] ,'N/A' AS [Observation ED Patient (1-2) Exclusions] ,@NumeratorPsych AS [Psychiatric/Mental Health Disorder Patient (1-3) Numerator] ,'N/A' AS [Psychiatric/Mental Health Disorder Patient (1-3) Exclusions] /* Output Detail */ IF @Detail = 'Y' SELECT AccountNumber ,UnitNumber ,Diagnosis ,Observation ,ArrivalDateTime ,DepartOrAdmitDateTime ,TotalMinutes FROM #TotalPopulation ORDER BY AccountNumber GO ========/* Execute the procedure */============= EXEC mtzcus_ED_Throughput_1 '2012-10-13 00:00:00:000', '2012-10-13 23:59:59:999'

when I run this in management studio it works and I have to use this whole script for the report. My thinking is that if I want this to be run from Reporting services am I posting the whole script or partial? Hello, You want to execute this complete script from the data set query incl CREATE PROC within the report itself? This is not possible and also not necessary. You have to create the stored procedure only once, running the script in SSMS. In your report query you then only call the procedure with the last EXEC in your script: EXEC mtzcus_ED_Throughput_1 '2012-10-13 00:00:00:000', '2012-10-13 23:59:59:999' Olaf Helper Blog Xing

In addition to what Olaf mentioned, I'm replying with this part of your stored proc in perspective: /******************************************************************/ /***** These variables are to be changed by the report writer *****/ /******************************************************************/ DECLARE @SourceID VARCHAR(5) /* Change to your site-specific values */ SET @SourceID = 'hospital' If you provide a stored proc for your report writers, they should not be required to modify the code of the stored proc. Instead, you should make @SourceID a parameter of the stored proc. That way the report writer can just pass it in. In fact, it may even be interesting to make it a report paramater as well. Implementing that will make the report more flexible: report users would/should be able to select the different values for SourceID from a dropdown (can be done by setting the available values of the report parameter).MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)

Hi Haseef, As Olaf posted, you have to create the stored procedure in the SSMS, and then create the dataset based on the store procedure in the Report Designer. Reporting Services supports stored procedures that return only one set of data. If a stored procedure returns multiple result sets, only the first one is used. If a stored procedure has a parameter with a default value, you can access that value in Reporting Services by using the DEFAULT keyword as a value for the parameter. If the query parameter is linked to a report parameter, the user can type or select the word DEFAULT in the input box for the report parameter. For more information, please see: Graphical Query Designer User Interface Regards,Mike Yin TechNet Community Support