Solved

How To Pass a parameter from SSRS 2008 to Stored Procedure

Posted on 2013-11-25
13
2,073 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
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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

747 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now