Dynamic SQL in a SP - Crystal in Visual Studio.Net 2003

[Juice05]

I am having an issue with Crystal, imagine that, in Visual Studio.Net.

I have created a Stored Procedure that has a "@Programs Varchar(1000)" parameter that maintains my "In" criteria. i.e. Where ProgramID In ( + @Programs" + ). @Programs is a comma seperate list of ProgramIDs. If I use Crystal 9.2.0 Standard edition I hav no problem verifying the database, however if I try to verify the database through my Visual Studio.Net version of Crystal, which is 9.2.2 it errors saying that my dynamic sql has errors (Which I know is untrue). Below if the SP syntax...Has anyone else went through this nightmare?

CREATE PROCEDURE dbo.CommPagersByProgram @ProgramID Varchar(1000) AS

Declare @SQL as Varchar(5000)

Set @SQL = 'Select Employees.FirstName + '' '' + Employees.LastName Employee,Pagers.CAPCode,Pagers.PagerNumber,Pagers.Pin,PS.PagerStatus,SR.Reason,Accounts.AccountNumber,
Sum(Invoice.Total) Total, Programs.ProgramCode,Programs.ProgramName

From Pagers

Inner Join CommunicationDetails CD On CD.CommunicationID = Pagers.CommunicationID
Left Join Accounts On Accounts.AccountID = CD.AccountID
Left Join DetailPrograms DP On DP.DetailID = CD.DetailID
Left Join Employees On Employees.EmployeeID = CD.EmployeeID
Left Join Invoice On Invoice.DetailID = CD.DetailID
Left Join Programs On Programs.ProgramID = DP.ProgramID
Left Join PagerStatuses PS On PS.PagerStatusID = Pagers.PagerStatusID
Left Join StatusReasons SR On SR.ReasonID = Pagers.ReasonID

Where CD.EndDate Is Null And DP.ProgramID In (' + @ProgramID + ')

Group By CD.DetailID,Employees.FirstName + '' '' + Employees.LastName,Pagers.CAPCode,Pagers.PagerNumber,Pagers.Pin,PS.PagerStatus,SR.Reason,Accounts.AccountNumber
,Programs.ProgramCode,Programs.ProgramName

Order By Employee'

Exec (@SQL)
GO

 

[vidru]

When you verify the database, it should prompt you for the parameter. When it does, you have to enter a valid value in order for it to execute correctly, otherwise, it'll send a NULL to the procedure, which will cause the dynamic sql statement to throw up, or simply not return anyting (including column names).

If you aren't getting prompted, the a workaround would be to alter the procedure to return at least the column names if the parameter is NULL or an empty string:

IF @ProgramID IS NULL OR @ProgramID BEGIN
  SELECT Employee = '', CAPCode = '', PagerNumber = '',
       Pin = '', PagerStatus = '', Reason= '', AccountNumber = '',
       Total = 0, ProgramCode = '', ProgramName = ''
END
ELSE
BEGIN
  -- The rest of the procedure...
END

-dave

 

[Juice05]

The problem is that Crystal in Visual Studion.Net won't even verify the stored procedure. It errors out saying that the sytax is incorrect. "Incorrect Syntax near ')'". I know that this is bogus because Crystal Standard 9.2.0 doesn't complain...neither does SQL Server.

 

[MisterMo]

Try this at the start of your SP

AS
BEGIN
SET NOCOUNT ON


don't forget to put an END after yor exec

Crystal is a bit fussy with stored Procedures Syntax

Mo

 

[DavidRover]

Hi,

I've got the same problem as Juice05.

I've tried adding the
"AS
BEGIN
SET NOCOUNT ON

END"

But still no joy. Can someone me what I'm doing wrong?

Here's the SQL...

"

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO





ALTER PROCEDURE dbo.PSP_PLEARNPROG_GetAllForCandConfReport
(
-- Parameters
@TRAINLOCCS_TK varchar(8000),
@COLLEGE_TK [int],
@TUTOR_UK varchar(8000),
@PROGRAMME_UK varchar(8000),
@TRACKINGID [int],
@DAYS_LEFT [int],
@LP_STATUS_UK varchar(8000)
)
as


BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(8000)

SET @SQL = 'select
lp.learnprog_tk,
lp.college_tk,
g.tutor_uk,
lp.trainlocation_tk,
lp.programme_uk,
lp.status_uk,
lp.learner_tk,
l.first_nm + '' '' + l.last_nm learner_nm,
t.besttimecand_desc,
(CASE WHEN
((SELECT COUNT(pdc.collection_tk) FROM pdoccollection pdc
WHERE pdc.collection_tk = lp.collection_tk
AND pdc.collmethod_uk = ''Names Only'') > 0)
THEN
1
ELSE
0
END) AS namesonly,
c.status_desc,
d.horeason_desc,
m.month_desc,
ay.academicyr_desc,
lp.completed_ind,
h.firstname + '' '' + h.lastname Tutor_nm,
h.address,
h.postcode,
h.day_phone_no,
h.even_phone_no,
h.mob_phone_no,
h.fax_no,
h.email,
tl.trainloccs_tk,
tl.Company_NM,
tl.company_address,
tl.company_postcode,
tl.contact_name,
tl.contact_job_title,
tl.contact_phone,
tl.contact_mobile_phone,
tl.contact_fax,
tl.contact_email,
tl.besttime_contact,
dth.tracking_tk,
dth.sent_dt,
dth.final_accept_dt,
dth.final_enrol_dt,
dth.final_foc_dt,
lp.created_dt,
lp.requalified_dt,
x.college_nm,
w.programme_title



from plearnprog lp inner join plearner l on lp.learner_tk = l.learner_tk
left outer join plpstatus c on lp.status_uk = c.status_uk
left outer join phoreason d on lp.horeason_uk = d.horeason_uk
inner join plptutalloc g on lp.LEARNPROG_TK = g.LEARNPROG_TK and g.current_ind = 1
left outer join ptutor h on g.tutor_uk = h.tutor_uk
left outer join pbesttimecand t on lp.besttimecand_uk = t.besttimecand_uk
left outer join pmonth m on lp.month_uk = m.month_uk
left outer join pacademicyr ay on lp.academicyr_uk = ay.academicyr_uk
left outer join ptrainlocation tl on lp.trainlocation_tk = tl.trainlocation_tk
left outer join pdoctrackhistory dth on lp.learnprog_tk = dth.learnprog_tk and dth.current_ind = 1
left outer join pdoccollection pdc on pdc.collection_tk = lp.collection_tk AND pdc.collmethod_uk <> ''Names Only''
left outer join pcollege x on lp.college_tk = x.college_tk
left outer join pprogramme w on lp.programme_uk = w.programme_uk
WHERE 1=1
AND

(lp.STATUS_UK = ''PREENROLL'' OR
lp.STATUS_UK = ''SENTTOCOLL'' OR
lp.STATUS_UK = ''ONPROG'') '


if @COLLEGE_TK is not null AND @COLLEGE_TK > 0
begin
set @SQL = @SQL + N' AND lp.COLLEGE_TK = ' + cast(@COLLEGE_TK as varchar)
end

if @TRACKINGID is not null AND @TRACKINGID > 0
begin
set @SQL = @SQL + N' AND dth.TRACKING_TK = ' + cast(@TRACKINGID as varchar)
end


if @TRAINLOCCS_TK is not null AND @TRAINLOCCS_TK > 0
begin
set @SQL = @SQL + N' AND tl.TRAINLOCCS_TK IN (' + @TRAINLOCCS_TK + ')'
end


if @TUTOR_UK is not null AND @TUTOR_UK <> '0'
begin
set @SQL = @SQL + N' AND g.tutor_uk IN (' + @TUTOR_UK + ')'
end

if @PROGRAMME_UK is not null AND @PROGRAMME_UK <> '0'
-- set @SQL = @SQL + N' a.PROGRAMME_UK IN (' + cast(@PROGRAMME_UK as varchar(8000)) + ')'
begin
set @SQL = @SQL + N' AND lp.PROGRAMME_UK IN (' + @PROGRAMME_UK + ' )'
end

if @LP_STATUS_UK is not null AND @LP_STATUS_UK <> '0'
-- set @SQL = @SQL + N' a.PROGRAMME_UK IN (' + cast(@PROGRAMME_UK as varchar(8000)) + ')'
begin
set @SQL = @SQL + N' AND lp.status_uk IN (' + @LP_STATUS_UK + ' )'
end





EXEC(@SQL)

end






GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


"

 

[MisterMo]

There is an error on your SP

if @TRAINLOCCS_TK is not null AND @TRAINLOCCS_TK > 0

@TRAINLOCCS_TK being a VARCHAR should be

if @TRAINLOCCS_TK is not null AND @TRAINLOCCS_TK <> '0'

that is if you pass 0 values from your application

hope it helps

Mo

 

[Juice05]

Still no answer.

 

[synapsevampire]

Juice5:

Sounds like it may be a .net concern.

Have you applied the service pack?

http://support.businessobjects.com/...cks/default.asp#Crystal Reports Service Packs

I don't understand why you're using dynamic SQL anyway in your SP, it might only slow you down by tripping up the execution plan, rewrite it using static SQL.

You didn't mention your database type nor version, an odd thing to overlook when requesting assistance with an SP.

I'll assume that you're using SQL Server, in which case I see nothing wrong with your syntax, but I'd make sure that you have the latest ODBC driver.

-k