Problem with SP from CR

[cwsstins]

I've got a CR (XI) calling a SP (SQL Server 2005) but returning no data. So I go to check out the (new, by the way) SP. It looks something like this:

@Prm char

AS

SELECT tbl1.fld1, tbl2.fld1, tbl3.fld1, tbl1.fld2, tbl2.fld2, tbl3.fld2
FROM (TABL1 tbl1 INNER JOIN TABL2 tbl2 ON tbl1.fld1 = tbl2.fld1) 
INNER JOIN TABL3 tbl3 ON tbl1.fld2 = tbl3.fld2
WHERE tbl1.fld3 = @Prm

When I call the SP from within SQL Server, it also brings back no data. Yet if I run the select statement only, with a valid value in place of the parameter, it brings back data. What am I doing wrong?

 

[cwsstins]

anybody?

 

[MzKitty]

where are you telling it what value is in @Prm? Is it getting a value from a parameter in the crystal report? if it is then you need "inPrm (the name of the parameter in the report)" in your stored procedure.

Cathy

 

[cwsstins]

Yes, it would be from a parameter in the CR. However, I've noticed that it isn't displaying data when called directly in SQL Server also. If I execute the line exec sp_mySPname 'ABC123' it brings back 0 records. But when I run that statement as just a SELECT statement and manually put in 'ABC123' instead of a parameter, there is data for ABC123.

 

[MzKitty]

In sql you have the @Prm defined as char, but you haven't assigned it any value so when you run it directly in Sql, you wouldn't get anything. You need to set your report up to prompt you for the value of @Prm and then pass it to the stored procedure. If you don't pass it a value, you will never get any data because this phrase WHERE tbl1.fld3 = @Prm won't mean anything. It's like saying, WHERE tbl1.fld3 = (nothing).

Here is an example of a stored procedure that I have that is passing parameters from the crystal report to the procedure:

CREATE PROCEDURE "GetReprintTkts"(in :LocID varchar (10), in :CustID varchar (10), in :FromDate date, in :ToDate date, in :FromTrk varchar (15), in :ToTrk varchar (15))

returns (TktNo int, TktUnqID int, TktDate date, TktLoc varchar (10), TktCust varchar (12),
TktOrderID varchar (12), TktVehicleID varchar(12));
begin

if CustID <> ' ') then
select t1.TicketNo,
t1.UniqueID, t1.TicketDate, t1.LocationID, t1.CustomerID, t1.OrderID, t1.VehicleID
from tkhist1 t1
where t1.LocationID = :LocID and
t1.CustomerID = :CustID and
(t1.TicketDate >= :FromDate and t1.TicketDate <= :ToDate) and (t1.VehicleID >= :FromTrk and t1.VehicleID <= :ToTrk)
ORDER BY t1.TicketNo,
t1.UniqueID;
end if ;
end;

I hope this helps.
Cathy

 

[cwsstins]

I do have a parm in the report. But when I run it in SQL, I'm supplying the value when I call the proc: exec sp_mySPname 'ABC123'

Isn't that the same as running it as if the SELECT statement had WHERE tbl1.fld3 = 'ABC123'?

Also, what is the purpose of the colons in your example? I am getting errors when I try to add : after IN.

 

[cwsstins]

In case anyone else runs into the same problem, I learned that anytime you use a text string (in my case, char) as a variable, you must declare the size of the field or it will default to 1. So when I ran the SP, it was only looking at the first character of the value supplied. I modified my @Prm char to @Prm char(15) and everything works fine now.