SSRS Report - SQL parameter not available

Hoping I'm just over looking something.  I have the query below in SSMS and it works just fine. (There are additional Insert and Update sections that were removed in this example for clarity.)  When I try to add a Dataset in SQL Server BIDS, the @season parameter isn't available.  When I alter the query and remove the insert statement, the parameter becomes available.  I don't do a lot with SSRS reports normally but trying to move that way.

Thanks in advance

USE [impresario]
GO
/****** Object: StoredProcedure [dbo].[LP_PAF_PERF_PROOF_1] Script Date: 06/05/2018 15:31:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LP_PAF_PERF_PROOF_1]
@season int = null
AS
BEGIN
SET NOCOUNT ON;

create table #results
(
perf_no int,
perf_name varchar(100),
perf_code varchar(20),
perf_dt datetime
)


insert #results (perf_no, perf_name, perf_code, perf_dt)
select
p.perf_no as i_perf_no,
i2.description as i_perf_name,
p.perf_code as i_perf_code,
p.perf_dt as i_perf_dt--,
from T_PERF p
join TR_SEASON s on p.season = s.id
join T_INVENTORY i on p.prod_season_no = i.inv_no
join T_INVENTORY i2 on p.perf_no = i2.inv_no
join TR_PERF_STATUS ps on p.perf_status = ps.id
where p.season = @season
order by p.perf_dt

select *
from #results
order by perf_no


return

END

Parents
  • Not 100% sure if any of the items below will help fix your problem, but I just checked a few of my stored procedures, which feed dozens of custom SSRS reports, and I noticed the following differences:

    • My SPs have parentheses around the parameters at the beginning
    • Mine don't have a semi-colon after SET NOCOUNT ON
    • I don't name my columns within my SELECT statement (e.g., as i_perf_no or i_perf_dt-) - I name them using the table I created
    • I don't have END at the end of any SP

    Best of luck troubleshooting! 

    Sara

Reply
  • Not 100% sure if any of the items below will help fix your problem, but I just checked a few of my stored procedures, which feed dozens of custom SSRS reports, and I noticed the following differences:

    • My SPs have parentheses around the parameters at the beginning
    • Mine don't have a semi-colon after SET NOCOUNT ON
    • I don't name my columns within my SELECT statement (e.g., as i_perf_no or i_perf_dt-) - I name them using the table I created
    • I don't have END at the end of any SP

    Best of luck troubleshooting! 

    Sara

Children
No Data