Reporting off Stored Procedures

[mubaig75]

I am using a stored procedure as a datasource for my report which has 4
parameters. So the problem is after adding this stored procedure I can't see
any fields under Database Fields section. Its understandable that I haven't
given any values to the parameters. I can see the parameters under the
Parameter Fields section. I don't know how to give the parameters values so
that I can get Database Fields and start designing report. I am using
Crystal Reports.NET. I tried to give the parameters values by editing them
but that didn't workout.

I also tried an alternative by putting this procedure into Command:

EXEC afzi.ChangesReports_Web {?PARAMETER1},{?PARAMETER2}

Its also not working..... because again how I am going to give the initial
values...

Is there a way if I can change the SQL thru code?

Any help will be appreciated.

Thanks

 

[vidru]

What kind of db is this (SQL Server, Oracle, etc.)?

The behavior you describe is typically what might happen if row counts are being interpreted as result sets, or the final SELECT statement is selecting variables that aren't aliased (e.g. [tt]SELECT @Var1[/tt] would cause your described behavior, whereas [tt]SELECT Var1 = @Var1[/tt] would not).

Feel free to post the procedure if you like...

-dave

 

[mubaig75]

Thanks a lot for instant reply.
db is SQL Server 2000 and below is my Stored Procedure:


ALTER PROCEDURE afzi.ChangesReport_Web
( @ReportStyle TINYINT,
@OneOrAll BIT,
@ID1 INT,
@LatestBy SMALLDATETIME )
AS
DECLARE @tbl1 TABLE ( ClientName VARCHAR(30), Div_Name VARCHAR(30), [Desc] VARCHAR(30),
Class_Desc VARCHAR(30), Emp_ID BIGINT, [ID] BIGINT, LastName VARCHAR(30),
Middle VARCHAR(30), FirstName VARCHAR(30), DOB SMALLDATETIME, Sex CHAR(1),
Prov VARCHAR(30), TaxRate DECIMAL(4,2), strChange VARCHAR(2048),
LatestBy SMALLDATETIME, [Action] VARCHAR(30), ChangeDate SMALLDATETIME,
ForMonth SMALLDATETIME )

DECLARE @tbl2 TABLE ( BEmp_ID BIGINT, Benefit VARCHAR(8), OrigCov VARCHAR(10), oWaived VARCHAR(4),
DateImp VARCHAR(13), NewCov VARCHAR(10), nWaived VARCHAR(4),
Terminated VARCHAR(3), EffFrom VARCHAR(13), EE_Adj DECIMAL(7,2),
ER_Adj DECIMAL(7,2), Tax DECIMAL(7,2), Ben_IDs INT, Taxable BIT )

DECLARE @curEmp BIGINT
DECLARE @TaxRate DECIMAL(5,2)

INSERT INTO @tbl1
SELECT * FROM afzi.fn_Return_ChangedEmp_IDs_ForChangeReports_Web(0,1,1,'07/31/2004')

SELECT @curEmp = MIN(Emp_ID) FROM @tbl1
SELECT @TaxRate = TaxRate FROM @tbl1 WHERE Emp_ID = @curEmp

WHILE @curEmp IS NOT NULL
BEGIN
INSERT INTO @tbl2
SELECT * FROM afzi.fn_Return_Emp_BenefitChanges_Web(@curEmp, '07/31/2004', @TaxRate)

SELECT @curEmp = MIN(Emp_ID), @TaxRate = MAX(TaxRate) FROM @tbl1
WHERE Emp_ID > @curEmp
END

SELECT * FROM @tbl1 A JOIN @tbl2 B ON A.Emp_ID = B.BEmp_ID

I appreciate your help!!

 

[vidru]

Man, gotta love those Table functions and variables ...

The procedure looks sound, but I've got a few suggestions:

1) Place a SET NOCOUNT ON statement at the beginning of the procedure so the "n row(s) affected" messages your INSERT statements produce aren't interpreted as results.

2) Use the field names in your final SELECT instead of "*".

3) Do a 'Verify Database' on the report, and when prompted for parameters, enter valid parameters.

-dave

 

[mubaig75]

Thanks Dave.

* I tried Set NOCOUNT ON
* I used a Command with the following SQL:

EXEC afzi.ChangesReport_Web {?ReportStyle}, {?OneOrAll}, {?ID1}, {?LatestBy}

And then I did Verify Database.... I got an error message saying; "No value given to one or more required parameters."

I never prompted for any parameter values..

Can you tell me if I am doing anything wrong.

I still have to try the field names instead of "*". I am going to do this after posting this reply.

I appreciate your help!

 

[vidru]

I wouldn't use the procedure as a Command. Just set it as the report's data source. When prompted for the parameters, make sure you enter something valid.

-dave

 

[mubaig75]

Thanks.

I tried field names instead of "*" but it didn't make any difference.

Then I tried the Stored procedure (didn't use it as a command) and when I Verify Database I got the following error and didn't get any prompts for parameters:

Details: ADO Error Code: 0x80040e14
Source: Microsoft OLE DB Provider for SQL Server
Description: "Incorect syntax near the keyword 'CONVERT'."
SQL State: 42000
Native Error:156

Apart from this, I tried to use a Command below:
EXEC afzi.ChangesReport_Web 1,0,0,'07/31/2004'
and it worked!!

I am wondering if there is a way to change SQL thru code so that I can be able to run above Command with different parameters.

I appreciate your help!