How To Pass a parameter from SSRS 2008 to Stored Procedure
Posted on 2013-11-25
I have a Stored Procedure that pulls data from a table as shown below:
ALTER PROCEDURE [dbo].[TESTPROC]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
WHERE dbo.DimDiary.RefType = 'Tenancy' AND dbo.DimDiary.EntryText LIKE 'INSURANCE1%' AND dbo.DimDiary.Curr = '1'
I have SSRS and have created a Datasource and a Dataset that points to this Stored Procedure and pulls back the fields.
Now see the bit that specify's "LIKE 'INSURANCE%' in my WHERE clause in the stored procedure, I would like to Pass this as a Parameter from my SSRS Report TO the Stored Procedure instead of having it hard-coded. I also need to pass multiple values into the parameter such as IN ('INSURANCE1%','INSRUANCE2%').
The stored procedure pulls back diary entries for letters we have created but users may want to enter several subjects and I won't know what subjects the user wants to pull back, that will be thier choice in the parameter setting in the ssrs report.
Hope that all makes sense, thanks in anticipation.