• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2247
  • Last Modified:

How To Pass a parameter from SSRS 2008 to Stored Procedure

Hi,

I have a Stored Procedure that pulls data from a table as shown below:

ALTER PROCEDURE [dbo].[TESTPROC]
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
SELECT   *
FROM         dbo.DimDiary
WHERE     dbo.DimDiary.RefType = 'Tenancy' AND dbo.DimDiary.EntryText LIKE 'INSURANCE1%' AND dbo.DimDiary.Curr = '1'
END

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.

Debs
0
deborahhowson00
Asked:
deborahhowson00
1 Solution
 
Ryan McCauleyData and Analytics ManagerCommented:
Unfortunately, the "LIKE" operator doesn't work with multiple values like that (it would be really nice if it did) - you have to specific LIKE values one at a time, and you may need to call the stored procedure once for each item.

However, you do have the option of using "IN" for cases like this, but you have to specify the items precisely (rather than with a wildcard, like you're doing with LIKE).

I know it's exactly the answer you're looking for, but hopefully it helps.
0
 
srikanthreddyn143Commented:
Try using CHARINDEX

CHARINDEX(',' + dbo.DimDiary.EntryText  + ',', ',' + @paramname+ ',') > 0)
0
 
Anthony PerkinsCommented:
Consider enabling and configuring Full-Text Search.  It then becomes as simple as:
WHERE CONTAINS(YourColumn, '"INSURANCE1*" OR "INSRUANCE2*"').
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Nico BontenbalCommented:
To parameterize your stored procedure change it to this:
alter  PROCEDURE [dbo].[TESTPROC] 
 @Entry varchar(100)

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
SELECT   *
FROM         dbo.DimDiary
WHERE     dbo.DimDiary.RefType = 'Tenancy' AND dbo.DimDiary.EntryText LIKE ( @Entry  + '%') AND dbo.DimDiary.Curr = '1'
END

Open in new window

In SSRS specify this as the query:
TESTPROC @Entry

Open in new window

The report will now have a parameter.
Please let me know when you got this working. Then we'll move on to the next part of your question: " I also need to pass multiple values into the parameter".
0
 
deborahhowson00Author Commented:
nicobo I have done as you have requested. I did try to run it in Report Builder 3 with the parameter specified though and it came back with an error saying it cannot report from the dataset.  See attached file
procerror.docx
0
 
deborahhowson00Author Commented:
I have execute permission on this stored procedure on the datasource server I have checked.
0
 
deborahhowson00Author Commented:
blah, forget that last bit, as I pressed send I realised the datasource doesn't use pass through authentication, it has a sql login to connect to the database so have given that execute permission and now the report runs 'doh'.  so... the next step.... please!

Thanks,
Debs
0
 
Nico BontenbalCommented:
Change your procedure to this:
ALTER  PROCEDURE [dbo].[TESTPROC] 
 @Entry varchar(100)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    --CTE to split the @Entry parameter
    declare @Delimiter varchar(1)
    set @Delimiter = ','


    DECLARE @Len INT = LEN(@Entry) + 1;

    WITH entries AS
    (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, @Entry, 1), 0), @Len),
           [value] = LTRIM(RTRIM(SUBSTRING(@Entry, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, @Entry, 1), 0), @Len)-1)))
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, @Entry, [end] + 1), 0), @Len),
           [value] = LTRIM(RTRIM(SUBSTRING(@Entry, [end] + 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, @Entry, [end] + 1), 0), @Len)-[end]-1)))
       FROM entries
       WHERE [end] < @len
    )
      
      --return the rows
    SELECT
        *
    FROM
        dbo.DimDiary
    WHERE
        dbo.DimDiary.RefType = 'Tenancy' 
        AND Exists(select * from entries where dbo.DimDiary.EntryText LIKE (value  + '%') )
        AND dbo.DimDiary.Curr = '1';
END

Open in new window

(Google for:
"Splitting a list of integers another roundup"
(including the quotes) for various methods for splitting a string into a table.)
Now you can test your procedure like this:
EXEC [TESTPROC] 'test'
EXEC [TESTPROC] 'insurance'
EXEC [TESTPROC] 'insurance1'
EXEC [TESTPROC] 'insurance1, insurance2'
You report will still work but the user can enter multiple values into the parameter and separate them with a comma.
0
 
deborahhowson00Author Commented:
Hi many thanks for that, and it works using the code you have provided.  However to simplify things I actully started with a fairly basic query to make things easier, but the actual query is included in the following code and won't run as it says it doesn't recognise the entries and value selections?
ALTER PROCEDURE [dbo].[MAXDiaryEntry]
@Entry varchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	    --CTE to split the @Entry parameter
    declare @Delimiter varchar(1)
    set @Delimiter = ','


    DECLARE @Len INT = LEN(@Entry) + 1;

    WITH entries AS
    (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, @Entry, 1), 0), @Len),
           [value] = LTRIM(RTRIM(SUBSTRING(@Entry, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, @Entry, 1), 0), @Len)-1)))
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, @Entry, [end] + 1), 0), @Len),
           [value] = LTRIM(RTRIM(SUBSTRING(@Entry, [end] + 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, @Entry, [end] + 1), 0), @Len)-[end]-1)))
       FROM entries
       WHERE [end] < @len
    )
      
      --return the rows
select *
with cte as(
SELECT     ROW_NUMBER() OVER (PARTITION BY dbo.DimDiary.Reference, dbo.DimDiary.EntryType, dbo.DimDiary.EntryStatus
ORDER BY dbo.DimDiary.CreatedDate DESC, dbo.DimDiary.CreatedTime DESC) AS RowNo, dbo.DimDiary.EntryType, dbo.DimDiary.EntryStatus, dbo.DimDiary.Reference, dbo.DimDiary.CreatedDate, 
dbo.DimDiary.CreatedTime, dbo.DimDiary.EntryText, dbo.DimDiary.CreatedUser
FROM         dbo.DimDiary
WHERE     dbo.DimDiary.RefType = 'Tenancy' 
AND Exists(select * from entries where dbo.DimDiary.EntryText LIKE (value  + '%') )
AND dbo.DimDiary.Curr = '1')
    SELECT     RowNo, cte.Reference, cte.EntryText, cte.CreatedDate, cte.CreatedTime, cte.EntryType, cte.EntryStatus, cte.CreatedUser
     FROM         cte
     WHERE     RowNo < 2
END

Open in new window

0
 
Nico BontenbalCommented:
Looks like you already had a CTE construction in your existing procedure. I think this is what you are looking for.
alter PROCEDURE [dbo].[MAXDiaryEntry]
@Entry varchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	    --CTE to split the @Entry parameter
    declare @Delimiter varchar(1)
    set @Delimiter = ','


    DECLARE @Len INT = LEN(@Entry) + 1;

    WITH entries AS
    (
        SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, @Entry, 1), 0), @Len),
           [value] = LTRIM(RTRIM(SUBSTRING(@Entry, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, @Entry, 1), 0), @Len)-1)))
        UNION ALL
        SELECT
           [start] = CONVERT(INT, [end]) + 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, @Entry, [end] + 1), 0), @Len),
           [value] = LTRIM(RTRIM(SUBSTRING(@Entry, [end] + 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, @Entry, [end] + 1), 0), @Len)-[end]-1)))
        FROM entries
        WHERE [end] < @len
    )
    ,cte as
    (
        SELECT     
            ROW_NUMBER() OVER (PARTITION BY dbo.DimDiary.Reference, dbo.DimDiary.EntryType, dbo.DimDiary.EntryStatus ORDER BY dbo.DimDiary.CreatedDate DESC, dbo.DimDiary.CreatedTime DESC) AS RowNo, 
            dbo.DimDiary.EntryType, 
            dbo.DimDiary.EntryStatus, 
            dbo.DimDiary.Reference, 
            dbo.DimDiary.CreatedDate, 
            dbo.DimDiary.CreatedTime, 
            dbo.DimDiary.EntryText, 
            dbo.DimDiary.CreatedUser
        FROM         
            dbo.DimDiary
        WHERE     
            dbo.DimDiary.RefType = 'Tenancy' 
            AND Exists(select * from entries where dbo.DimDiary.EntryText LIKE (value  + '%') )
            AND dbo.DimDiary.Curr = '1'
    )
    SELECT     
        RowNo, cte.Reference, 
        cte.EntryText, 
        cte.CreatedDate, 
        cte.CreatedTime, 
        cte.EntryType, 
        cte.EntryStatus, 
        cte.CreatedUser
    FROM
        cte
    WHERE
        RowNo < 2
END

Open in new window

If this doesn't work you can use a construction where you use the entries CTE to fill a temp table or a table variable. Then you can still use your original CTE. If you need any help with that please post another question. I think your original question about passing a parameter to a procedure is answered now.
0
 
deborahhowson00Author Commented:
You are fantabulous!

Thank you very much, if only we could award more points :/

Deborah
0
 
deborahhowson00Author Commented:
Brilliant!
0
 
Nico BontenbalCommented:
No thanks. Nice to hear that your problem is solved.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now