Catastrophic Error using a Parameter Stored Procedure as a Datasource

[wooddsw]

I’ve developed the attached query that needed to perform a Union. Previously a view, this query now needed to incorporate a dynamic where clause as well as receive a ‘Product’ parameter. The conditional logic works properly when executed in ISQL. When attempting to use this SP as a datasource in the data explorer on a new report I’m prompted for a parameter value. I’ve seen numerous posts regarding this and understand the Crystal automatically creates the parameter and has to resolve to a dataset in order to allow me to pick the fields for the report design. However, once I’ve entered a valid parameter it appears to churn for awhile and then issues a “Catastrophic Failure” database error. I can tap off the error and still remain on the Data Explorer to add another source.

Has anyone seen this error and identified a work around? Also, given someone can help me get through this, is the parameter automatically created and available in the Field Explorer so I can set the discreet values to valid runtime parameter selections?

Any assistance is greatly appreciated

Crystal 8.5.0.217
SQL/Server 2000

Drop proc SP_CYTOTargetAcctAffilContacts
Go
Create proc sp_CYTOTargetAcctAffilContacts
@Product char(12)
AS
SET nocount on
Declare @SQL varchar(3000)
SET @sql = 'select(select username from userinfo where userid = t1.accountmanagerid) as POR, 
	(select username from userinfo where userid = COSid) as COS,t1.account,'' '' as Affiliation, Lastname + '', '' + Firstname as ContactName,Title, t9.Type
	from account t1
	inner join mcg_acc_Ext t2 on t1.accountid = t2.accountid
	inner join contact t9 on t1.accountid = t9.accountid'
	if @product = 'Quadramet'
		Set @SQL = @SQL + ' where adminsite = ''T'' and QuadrametTarget = ''T'''
	else
		Set @SQL = @SQL + ' where prostascinttarget = ''T'' AND SUBSTRING(T1.STATUS,1,3) IN (''A -'',''B -'',''C -'')'
set @SQL = @sql + 'UNION 
Select (select username from userinfo where userid = t3.accountmanagerid) as POR, 
		(select username from userinfo where userid = t3.COSid) as COS,t3.account, 
		(Select Account from Account where accountid = toid) as Affiliation, LastName + '', '' + FirstName as ContactName, Title, t5.Type		
	from  (select T1.AccountID, t1.account, t1.accountmanagerID, t2.cosid
		from account t1
		inner join mcg_acc_Ext t2 on t1.accountid = t2.accountid'
		if @product = 'Quadramet'
			Set @SQL = @SQL + ' where adminsite = ''T'' and QuadrametTarget = ''T'''
		else
			Set @SQL = @SQL + ' where prostascinttarget = ''T'' AND SUBSTRING(T1.STATUS,1,3) IN (''A -'',''B -'',''C -'')'
set @SQL = @SQL + ') t3
	inner join association t4 on t3.accountid = t4.fromid
	inner join contact t5 on t4.toid = t5.accountid'
exec (@SQL)

 

[synapsevampire]

Yes, you can set default values in Crystal for this.

As for catastrophic errors, it may be that on occasion something hiccups, or you don't return a recordset, which will cause Crystal to choke.

Your dynamic WHERE clause isn't so dynamic and could be written without dynamic SQL.

Dynamic SQL tends to trip un execution plans and slows SPs.

People like it because it's slick, however I tend to go for tuning.

-k