How can I use a Dynamic Stored Procedure ?

[Siggy19]

I have multiple parameters that I want to use and each can be set to 'ALL' or a specific value.

Rather than using multiple IF statements in the Stored Procedure to select the correct SQL statement, I am creating a single SQL Statement dynamically and then executing it using the EXECUTE command. (The fields returned by the SQL statement are always the same)

This works fine when I try running it as a Stored Procedure, but Crystal 8.5 will not let me Verify the Database using this query saying that there are no fields in the file.

I tried populating a temporary table using the execute command and that didn't even compile.

Any ideas ?

 

[synapsevampire]

I thought that I'd addressed this, perhaps in another post.

What database are you using?

Oracle requires that SP's use In/Out cursors, and there are other restrictions.

The connectivity is also important, use the CR ODBC driver and in the advanced tab enable the procedure returns results.

Here's the CD whitepaper:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf.asp

-k

 

[Siggy19]

I found a solution.

I am actually using SQL Server 2000.

Using the EXECUTE command, it worked fine when I tested it using Query Analyser but Crystal would not Verify it claiming that no fields exist.

So... I have an IF statement depending upon whether the start date parameter is NULL. If it is, the SP simply runs a simple SQL returning all the relevent fields. If the date is not NULL, it does the EXECUTE and IT WORKS !

It is much more complicated than the original but it also typically runs in a few seconds rather than 45 minutes.

 

[synapsevampire]

If you had a report running for 45 minutes and now it's a few seconds, it's because the report was poorly designed, likely the result of NOT pass the SQL to the database.

SP's are almost always faster, but your difference means that the original SQL was bad.

-k

 

[Siggy19]

Trueish... we have a number of parameters (Customer Name, Customer Site, Division and 'Event') for which we allow multiple selections. The Stored Proc parameters cannot cope with these and so they have to be handled by the Crystal Record Selection. This means that, apart from the date range, all the data has to be selected and passed to Crystal before being filtered properly.

Part of my change is to allow only a single selection but to allow the use of LIKE, if required. This could be passed to the stored procedure and performance would then be much better even without the other changes.

However, we would then need to have multiple SQL statements depending upon which of the selections were being used (it is very rare for them all to be). With four options, that would lead to 16 possible SQL statements.

Therefore, using the dynamic SQL is actually simpler than the alternatives.

For your information, this is the Stored Procedure I created;

[tt]
/******************************************************************************************************************************
** File: Open Event Aging Detail
** Name: RPT_Open_Event_Aging_Detail (SP)
**
** Auth: Deb Jamrog
** Date: 09/20/02
********************************************************************************************************************************
** Change History
********************************************************************************************************************************
**
** Date: Author: Description:
**
** 09/20/02 Deb Jamrog New report.
** 08/08/03 Simon Holzman Optimized.
**
********************************************************************************************************************************/

ALTER PROCEDURE dbo.RPT_Open_Event_Aging_Detail_NEWEST

@CustName VARCHAR(60),
@CustName_Match VARCHAR(10),
@CustLoc VARCHAR(60),
@CustLoc_Match VARCHAR(10),
@Event VARCHAR(20),
@Event_Match VARCHAR(10),
@Division VARCHAR(25),
@StartDate DATETIME,
@EndDate DATETIME

AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @CustName_SQL VARCHAR(100),
@CustLoc_SQL VARCHAR(100),
@Event_SQL VARCHAR(100),
@Division_SQL VARCHAR(100),
@SQL_Statement VARCHAR(8000)

--SELECT @Startdate = convert(char(10), @StartDate, 101) +' 00:00:00.000'
--SELECT @Enddate = convert(char(10), @EndDate, 101) + ' 23:59:59.997'


-- Generate SQL Code depending upon the Parameters given


SELECT @CustName_SQL = CASE @CustName_Match
WHEN 'ALL' THEN ''
WHEN 'EXACT' THEN 'AND c.c_name = ''' + RTRIM(@CustName) + ''''
ELSE 'AND c.c_name LIKE ''%' + RTRIM(@CustName) + '%'''
END


SELECT @CustLoc_SQL = CASE @CustLoc_Match
WHEN 'ALL' THEN ''
WHEN 'EXACT' THEN 'AND ce.ce_name = ''' + RTRIM(@CustLoc) + ''''
ELSE 'AND ce.ce_name LIKE ''%' + RTRIM(@CustLoc) + '%'''
END


SELECT @Event_SQL = CASE @Event_Match
WHEN 'ALL' THEN ''
WHEN 'EXACT' THEN 'AND t.t_status = ''' + RTRIM(@Event) + ''''
ELSE 'AND t.t_status LIKE ''%' + RTRIM(@Event) + '%'''
END


SELECT @Division_SQL = CASE @Division
WHEN 'ALL' THEN ''
ELSE 'AND o.o_type = ''' + RTRIM(@Division) + ''''
END


-- Now Generate the full SQL Query to be used

SET @SQL_Statement =
-- 'ALTER VIEW ' + @ViewName + ' AS' + CHAR(13) + CHAR(10) +
'SELECT o.o_num,' + CHAR(13) + CHAR(10) +
'o.o_type,' + CHAR(13) + CHAR(10) +
'o.o_amt,' + CHAR(13) + CHAR(10) +
'o.o_refcnty,' + CHAR(13) + CHAR(10) +
'o.o_ref,' + CHAR(13) + CHAR(10) +
'c.c_name,' + CHAR(13) + CHAR(10) +
'ce.ce_name,' + CHAR(13) + CHAR(10) +
't.t_status,' + CHAR(13) + CHAR(10) +
't.t_in,' + CHAR(13) + CHAR(10) +
't.t_out,' + CHAR(13) + CHAR(10) +
'CONVERT(VARCHAR(250), t.t_msg) AS t_msg,' + CHAR(13) + CHAR(10) +
'p.p_address,' + CHAR(13) + CHAR(10) +
'p.p_city,' + CHAR(13) + CHAR(10) +
'p.p_cnty,' + CHAR(13) + CHAR(10) +
'p.p_state,' + CHAR(13) + CHAR(10) +
'pa.pa_last + '', '' + pa.pa_first as borrower,' + CHAR(13) + CHAR(10) +
'e.e_first + '' '' + e.e_last as input_by,' + CHAR(13) + CHAR(10) +
'd.div_name,' + CHAR(13) + CHAR(10) +
'ct.ct_name,' + CHAR(13) + CHAR(10) +
's.s_name' + CHAR(13) + CHAR(10) +
'FROM orders o WITH(NOLOCK)' + CHAR(13) + CHAR(10) +
'JOIN track t WITH(NOLOCK) ON o.o_num = t.o_num' + CHAR(13) + CHAR(10) +
'LEFT JOIN vw_cancel x WITH(NOLOCK) ON o.o_num = x.o_num' + CHAR(13) + CHAR(10) +
'LEFT JOIN parties pa WITH(NOLOCK) ON o.o_num = pa.o_num' + CHAR(13) + CHAR(10) +
'LEFT JOIN prop p WITH(NOLOCK) ON o.o_num = p.o_num' + CHAR(13) + CHAR(10) +
'LEFT JOIN cust c WITH(NOLOCK) ON o.c_num = c.c_num' + CHAR(13) + CHAR(10) +
'LEFT JOIN cust_ent ce WITH(NOLOCK) ON (o.c_num = ce.c_num' + CHAR(13) + CHAR(10) +
'AND o.ce_num = ce.ce_num)' + CHAR(13) + CHAR(10) +
'LEFT JOIN emp e WITH(NOLOCK) ON e.e_num=t.e_num' + CHAR(13) + CHAR(10) +
'JOIN division d WITH(NOLOCK) ON d.div_num = o.o_type' + CHAR(13) + CHAR(10) +
'LEFT JOIN cust_emp ct WITH(NOLOCK) ON (o.c_num = ct.c_num' + CHAR(13) + CHAR(10) +
'AND o.ce_num = ct.ce_num' + CHAR(13) + CHAR(10) +
'AND ct.ct_num=o.ct_num)' + CHAR(13) + CHAR(10) +
'LEFT JOIN site s WITH(NOLOCK) ON o.o_div = s.s_num' + CHAR(13) + CHAR(10) +
'WHERE t.t_in BETWEEN ''' + CONVERT(VARCHAR(30), @startdate, 101) + ' 00:00:00.000'' AND '''
+ CONVERT(VARCHAR(30), @enddate, 101) + ' 23:59:59.997''' + CHAR(13) + CHAR(10) +
'AND t.t_out IS NULL' + CHAR(13) + CHAR(10) +
'AND x.o_num IS NULL' + CHAR(13) + CHAR(10) +
'AND (pa.pa_num = 1 or pa.pa_num is null)' + CHAR(13) + CHAR(10) +
'AND (p.p_num = 1 or p.p_num is null)' + CHAR(13) + CHAR(10) +
@CustName_SQL + CHAR(13) + CHAR(10) +
@CustLoc_SQL + CHAR(13) + CHAR(10) +
@Event_SQL + CHAR(13) + CHAR(10) +
@Division_SQL

--PRINT ': ' + @SQL_Statement + ' :'


-- Used to view generated code - MUST create the table first and give all rights to PUBLIC !

--INSERT INTO testsiggy2 VALUES(@SQL_Statement)


-- Create and load Table with Data since Crystal cannot cope with an EXEC command since it then
-- does not know what fields exist

IF @StartDate IS NULL
BEGIN
DECLARE @Data TABLE
(
o_num INT,
o_type VARCHAR(10),
o_amt MONEY,
o_refcnty VARCHAR(30),
o_ref VARCHAR(30),
c_name VARCHAR(60),
ce_name VARCHAR(60),
t_status VARCHAR(20),
t_in DATETIME,
t_out DATETIME,
t_msg VARCHAR(250),
p_address VARCHAR(50),
p_city VARCHAR(30),
p_cnty VARCHAR(30),
p_state VARCHAR(2),
borrower VARCHAR(42),
input_by VARCHAR(37),
div_name VARCHAR(60),
ct_name VARCHAR(60),
s_name VARCHAR(50)
)

SELECT *
FROM @Data
ORDER BY c_name ASC,
o_num
END
ELSE
EXECUTE(@SQL_Statement)

[/tt}

 

[synapsevampire]

All of this could have been accomplished from within CR, getting CR to pass the SQL is tricky, but it can be done, inclusive of using the Like predicate.

I've posted a FAQ on passing SQL in the Formulas forum:

faq767-3825

SP's are faster, though when you construct dynamic SQL, less so.

-k