SSRS Report - SQL parameter not available

• Adding parenthesis around parameters is always a good habit to get into as is easier to add multiple parameters
• I normally place the set nocount on before the begin and without a ;
• Ending with a select statement instead of a return

Although not sure why any of these would affect how BIDS sees parameters depending on the insert statement being present or not.

I am assuming this is a snippet as otherwise the insert into the table is superfluous as you could just run the insert select statement directly

The following changes are personal preference

• using a table variable instead of #temp table - especially for smaller datasets there is no difference ion speed, although if you need to add extra indexes outside of primary key then you will need #temp, plus with table variables you never need to remember to drop the table which when testing the sql direct can be pain with #temp tables
• adding primary key to perf_no - which works for the snippet but may need changing as you progress the sql
• using alias = col_name rather than col_name as alias (just think it reads easier as more obvious what your column names will be)
• Commented out create statement for creating the temp # version of the stored procedure - this allows you to create a version of the stored procedure that is only active in your session, allows you to add addition select and print statements for see what data is being stored when debugging issues with Live data without affecting anyone using the live stored procedure/report in the mean time

Here is what your code snippet  would look like if I had written it

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] (
--CREATE PROCEDURE #LP_PAF_PERF_PROOF_1 ( -- create statement for a temporary stored procedure which is useful for testing when sp is running Live
     @season int = null
)
AS
SET NOCOUNT ON
/***********************************************************************************************************************************

Always have a block of comments at the start so I can write relevant details about the sp

Always include the execute statement to run, as makes it easier for someone else to know what should return results
execute LP_PAF_PERF_PROOF_1 @season=100

Also execute for the temp procedure if you use that
execute #LP_PAF_PERF_PROOF_1 @season=100

***********************************************************************************************************************************/

BEGIN

     declare @results as table
     (
          perf_no int primary key,
          perf_name varchar(100),
          perf_code varchar(20),
          perf_dt datetime
     )

     insert @results (perf_no, perf_name, perf_code, perf_dt)
     select
          i_perf_no      = p.perf_no,
          i_perf_name = i2.description,
          i_perf_code  = p.perf_code,
          i_perf_dt       = p.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

END

Hope that helps

Mark

• Adding parenthesis around parameters is always a good habit to get into as is easier to add multiple parameters
• I normally place the set nocount on before the begin and without a ;
• Ending with a select statement instead of a return

Although not sure why any of these would affect how BIDS sees parameters depending on the insert statement being present or not.

I am assuming this is a snippet as otherwise the insert into the table is superfluous as you could just run the insert select statement directly

The following changes are personal preference

• using a table variable instead of #temp table - especially for smaller datasets there is no difference ion speed, although if you need to add extra indexes outside of primary key then you will need #temp, plus with table variables you never need to remember to drop the table which when testing the sql direct can be pain with #temp tables
• adding primary key to perf_no - which works for the snippet but may need changing as you progress the sql
• using alias = col_name rather than col_name as alias (just think it reads easier as more obvious what your column names will be)
• Commented out create statement for creating the temp # version of the stored procedure - this allows you to create a version of the stored procedure that is only active in your session, allows you to add addition select and print statements for see what data is being stored when debugging issues with Live data without affecting anyone using the live stored procedure/report in the mean time

Here is what your code snippet  would look like if I had written it

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] (
--CREATE PROCEDURE #LP_PAF_PERF_PROOF_1 ( -- create statement for a temporary stored procedure which is useful for testing when sp is running Live
     @season int = null
)
AS
SET NOCOUNT ON
/***********************************************************************************************************************************

Always have a block of comments at the start so I can write relevant details about the sp

Always include the execute statement to run, as makes it easier for someone else to know what should return results
execute LP_PAF_PERF_PROOF_1 @season=100

Also execute for the temp procedure if you use that
execute #LP_PAF_PERF_PROOF_1 @season=100

***********************************************************************************************************************************/

BEGIN

     declare @results as table
     (
          perf_no int primary key,
          perf_name varchar(100),
          perf_code varchar(20),
          perf_dt datetime
     )

     insert @results (perf_no, perf_name, perf_code, perf_dt)
     select
          i_perf_no      = p.perf_no,
          i_perf_name = i2.description,
          i_perf_code  = p.perf_code,
          i_perf_dt       = p.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

END

Hope that helps

Mark