Using Stored Procedures, DataSet Report Parms? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Analysis Server and Reporting Services (2005)

Using Stored Procedures, DataSet Report Parms?

Author

Topic

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-08 : 15:14:01

I have a stored procedure named AAAsp_DeptInfo that has a where clause with the parameter @Dept_Select

Select *
from ED_Dept
where (Dept_Name = @Dept_Select)

I have a dataset Get_Dept defined that allows the user to select a valid department drom the dropdown. Date Set returns a field label Dept_Passed

SELECT DISTINCT Dept_Name AS Dept_Passed
FROM ED_Dept

I know reporting services identifies the @Dept_Select from the stored procedure because under erport parameters it shows up as the only parameter. I defined it as From Query with Value Field and Label both as Dept_Passed

My question is
How do I pass the parms to the stored procedure?

I have tried:
exec AAAsp_DeptInfo @Dept_Select=Dept_Passed
and
exec AAAsp_DeptInfo Dept_Passed and I receive zero records after selecting the department

John

"The smoke monster is just the Others doing barbecue"

svicky9
Posting Yak Master

232 Posts

Posted - 2009-09-08 : 16:59:21

On the Dataset query field..tick it as stored procedure and in the drop down select the stored procedure..Once you click OK on the Dataset ... Reporting services automatically identifies what parameter to set...

Or in text mode Typeit as exec ProcedureName ?

http://www.sqlserver007.com

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-09 : 08:10:07

svicky9,

Jeepers... I never noticed the drop down before. I had always keyed exec xxxxx directly but I never had parapeters before. It does function differently for me but something is still wrong. Now, when I test the dataset from the Data tab, it works perfectly. I have defined my parameter to the report but when I preview the report and select a valid department it still gives me the error:
"Procedure or Function 'AAAsp_DeptInfo' expects parameter '@Dept', which was not supplied."

John

"The smoke monster is just the Others doing barbecue"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources