Passing Two Parameters to a stored procedure

[Mack2]

From my data set with ssrs I am calling a stored procedure. That stored procedure is passing two parameters. For some reason, it will not let me pass two parameters. I can pass one, just fine.
The error I get is "The value expression for the query @SchoolParameter contains an error"
I am using the code below to call the proc.

EXEC StudentInformationMart.dbo.sp_SchoolAttritionReport_GetStudentTenure_SchoolSpecific @SchoolParameter, @AcademicYear

THANK YOU FOR ANY HELP YOU CAN PROVIDE!!!!!

 

[RiverGuy]

You want to change your DataSet's code to use the command type of stored procedure, with the command text of StudentInformationMart.dbo.sp_SchoolAttritionReport_GetStudentTenure_SchoolSpecific. Then, on the parameters tab, enter your parameter mappings there. Don't use the exec statement as you have shown.

 

[Mack2]

The problem is, that I execute the stored procedure with the dataset, then I insert the results of the sp into a temp table. So if I set the type to stored procedure, it removes all the code within the data set. I have the top part of the data set below. THANKS!!!!!!

CREATE TABLE #FinalResult
(
StudentDWID INT,
EffectiveTotalNumberOfDaysEnrolled INT,
TenureDMID INT,
YearsAtNHA VARCHAR(255),
TenureDescription VARCHAR(255)
)
INSERT #FinalResult--
EXEC StudentInformationMart.dbo.sp_SchoolAttritionReport_GetStudentTenure_SchoolSpecific @SchoolParameter,@AcademicYear

-- Pivot
SELECT
YearsAtNHA
,[Withdrawn]
,[Enrolled]
,[Total Enrolled]
-- Pivot source...
FROM (
-- Get Withdrawn Counts...

 

[Mack2]

Hi Riverguy,

I forgot to mention that everything works fine when I pass one parameter. It is when I pass two parameters that an error comes up ( @SchoolParameter,@AcademicYear).

THANK YOU FOR ANY HELP YOU CAN PROVIDE

 

[RiverGuy]

I would just wrap a new stored procedure around this.

 

[rose4567]

You might try calling via a subquery. I think this is the easiest way. See copy/paste of code I'm using in one of my reports. Feel free to modify the code for your own use.

In my case, the function is set to use two parameters - site_id and stock_item_nbr. You can see how I've called to them below and then in my where statement I pass parameters based on fields in the main query.

I think you'll want to remove the "@" symbol if you are calling in the data view. That looks like where the error is coming from. Notice where I define my two function parameters in the subquery below. No @ symbol prefixes them.

(SELECT     MAX(TotalDollar) AS Expr1
 FROM         dbo.fnFIFO(site_stock_items.site_id, site_stock_items.stock_item_nbr) AS fnFIFO_1
WHERE     (site_id = site_stock_items.site_id) AND (stock_item_nbr = site_stock_items.stock_item_nbr)) AS fifo

Good luck! Hope this helps.

 

[Mack2]

Thank you for your help.
Turns out, there was another table in the report that was calling the same stored procedure. So it was complaining that I did not update that table with the correct parameters. I did not create the report, so no idea this was happening.

Thanks again!!