Solved

When using SQL Server 2005 and Crystal Reports XI, how do I change a variable in the SP to accept multiple values from the Report Parameter?

Posted on 2008-10-29
4
953 Views
Last Modified: 2012-05-05
Hello there,
I am an experienced Crystal Developer, but this is my first time using Crystal with a Stored Proc in SQL Server 2005. I have been tasked to change a report paramater to accept multiple values (the parameter currently only accepts a single value). This seemed simple at first, until I realized it is linked into a stored proc. :-)

The variable (@AppID) in the stored proc is a varchar(20).

Does anyone have any suggestions?

Thank you!
Lin
0
Comment
Question by:LinInDenver
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 125 total points
ID: 22837063
You can't pass multiple values into a single procedure.  But, depending upon how Crystal can build the parameter string (ie. a delimited string) you can split it later in the stored procedure.



--Old
create procedure up_GetStuff @AppID varchar(20)
as
select * from MyTable
where AppID = @AppID
 
go
exec up_GetStuff 'ABC'
go
--New
create procedure up_GetStuff @AppID varchar(max)
as
select * from MyTable
where AppID in (select theValue from [dbo].[fn_DelimitedToTable](@AppID),',')
 
go
exec up_GetStuff 'ABC,DEF,GHI'
go
--And use this function
if object_id('[dbo].[fn_DelimitedToTable]') is not null
     drop function [dbo].[fn_DelimitedToTable]
go
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString) 
*                   on a delimiter (@delimiter) and outputs 
*                   a table of values.
*    
*
*************************************************************/
begin
 
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
		from	dbo.vw_Nums
		where
			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 
 
 
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable]('a','|')
select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')
*/
GO

Open in new window

0
 
LVL 13

Assisted Solution

by:crgary_tx
crgary_tx earned 125 total points
ID: 22841611
There are two ways you can circumvent this problem.
1. One is to convert the stored procedure in to view having the parameters on the report side. With the effecient record selection formula in place the record selection is still pushed to the server side. With this the user can still select the multiple values for the parameter from the available list without typing them comma delimited..

2. If the stored procedure parameter is used in the inline query, then it may not be easy to strip the parameter from the procedure and use it on the report side. In that case you have to have logic built into your stored procedure so that it can accept and parse the comma de-limited string for the parameter..
I have done this in Oracle but not in SQL Server. In Oracle you can use the famous totable function by Thomas (asktom!) Kyte which accepts the string and return as collection.. check it out and see if you can take this to sql server.

Run the script attached in the code snippet below..

Modify the selection criteria in the stored procedure involving this parameter to something like below..
--sample showing how to use this function in sql
--select * from table(totable('abc,jaghd,dhsdg,as'));
select
* from ......
 where
..
..
cast(<your field> as char(1000)) in (select cast(column_value as char(1000)) from table(cast(str2tbl(V_PARAM)as VARCHAR2_TAB)))
..
..

hth..
Gary

create or replace type varchar2_tab as table of varchar2(30)
/
CREATE OR REPLACE function CISADM.totable( p_str in varchar2,
                                    p_delim in varchar2 default ',' )
 
return str2tblType as
                 
l_str long default p_str || p_delim;
l_n   number;
l_data str2tblType := str2tblType();
     begin
        loop
           l_n := instr( l_str, p_delim );
           exit when (nvl(l_n,0) = 0);
           l_data.extend;
           l_data(l_data.count) := ltrim(rtrim(substr(l_str,1,l_n-1)));
           l_str := substr( l_str, l_n+1 );
          end loop;
       return l_data;
       end;
/

Open in new window

0
 
LVL 14

Author Comment

by:LinInDenver
ID: 22881525
Thanks guys!! I used a combination of these solutions and am now finalizing the report.

We changed @appid into a larger varchar field to allow multiples. We then found a fn_split function (SQL Server) that accepts a string and a delimiter and converts results into a temp table.

we then used a nested select...
select * from table where appid in (select value from fn_split (@appid, ',')).
0
 
LVL 14

Author Closing Comment

by:LinInDenver
ID: 31511489
Thanks guys!! I used a combination of these solutions and am now finalizing the report.

We changed @appid into a larger varchar field to allow multiples. We then found a fn_split function (SQL Server) that accepts a string and a delimiter and converts results into a temp table.

we then used a nested select...
select * from table where appid in (select value from fn_split (@appid, ',')).
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

756 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