Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 976
  • Last Modified:

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?

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
LinInDenver
Asked:
LinInDenver
  • 2
2 Solutions
 
BrandonGalderisiCommented:
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
 
crgary_txCommented:
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
 
LinInDenverAuthor Commented:
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
 
LinInDenverAuthor Commented:
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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