Solved

How To Pass a parameter from SSRS 2008 to Stored Procedure

Posted on 2013-11-25
13
2,181 Views
Last Modified: 2013-11-28
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
Comment
Question by:deborahhowson00
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39676236
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
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 39676368
Try using CHARINDEX

CHARINDEX(',' + dbo.DimDiary.EntryText  + ',', ',' + @paramname+ ',') > 0)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39679685
Consider enabling and configuring Full-Text Search.  It then becomes as simple as:
WHERE CONTAINS(YourColumn, '"INSURANCE1*" OR "INSRUANCE2*"').
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 39680016
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
 

Author Comment

by:deborahhowson00
ID: 39680495
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
 

Author Comment

by:deborahhowson00
ID: 39680497
I have execute permission on this stored procedure on the datasource server I have checked.
0
 

Author Comment

by:deborahhowson00
ID: 39680505
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
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 39680649
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
 

Author Comment

by:deborahhowson00
ID: 39683518
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
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
ID: 39683620
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
 

Author Comment

by:deborahhowson00
ID: 39683821
You are fantabulous!

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

Deborah
0
 

Author Closing Comment

by:deborahhowson00
ID: 39683822
Brilliant!
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 39684032
No thanks. Nice to hear that your problem is solved.
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question