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
940 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…

832 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