I have a Stored Procedure/SQL function that makes calculations on a specific record returning 0.00 or another DEC value. It takes the record's primary key as a parameter.

I want to implement this function/Stored procedure in Crystal Reports by passing the primary key database field for each individual record. However, when I try to do this, it requires me to manually input the id. The crystal report that I implement is grouped by the id so that there'll be a report for each record.

Is there a way I can pass a database field as a parameter for a Stored Procedure or a SQL function? Is there something similar I can use?

CodePudding user response：

If I've understood you correctly, you want to execute some code (a stored procedure or function) which uses the value of a column as the input to the code, and you want to do that for multiple rows at once.

If you write the code as a user defined function (ideally an inline table valued function), which is sort of like a view that takes a parameter, then you can use cross apply or outer apply to execute the code for each row.

Here's a simple example. Suppose I have a block of code written as a user defined function. It takes an integer input parameter and just adds one to the input parameter:

create or alter function MyFunction(@MyParam int) returns table as 
return
(
   select @MyParam   1 as MyResult
);
go

Because this function returns a table, I get the result by selecting from it, just like selecting from a regular table or view, like this:

select MyResult from MyFunction(68);

-- returns a result set with a single row and column, with a value of 69

But the input parameter value can also come from values in a column. We provide the value for each row by cross applying the function to the table, because "cross apply" means "run this function for each row". Suppose I have some table with 2 rows in it:

create table MyTable(i int primary key);
go
insert MyTable(i) values (1), (2);

I want to run my function against all of the values of i in the table. I do this using cross apply, and I pass the name of the column as the input parameter:

select      f.MyResult
from        dbo.MyTable         t
cross apply dbo.MyFunction(t.i) f;

The above code returns the following result set:

CodePudding user response：

An alternative approach is to insert a subreport using the SP as its data source into the Group Header section. Pass the ID as the subreport link, so it gets used as the SP parameter.
If you need the returned value in main report calculations, use a subreport formula to load the value into a Shared variable so it is accessible to main report formulas.

Another option is to use a Crystal Reports UFL (User Function Library). Ken Hamady maintains a list of UFLs here. At least one of them provides a function that allows you to call a dynamic SQL statement and return a value.