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
919 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

14 Experts available now in Live!

Get 1:1 Help Now