Calling a function in my dataset

[dleewms]

I would like to know how to call a function in the SQL dataset. For example, I have a function that pulls back the number of children for each parent. I want to supply the parent's ID field and have the number of children returned for this parent along with their first and last names.

Select FirstName, LastName, GetNumberChildren(ParentsID)
From MainTbl
Where ParentsID = 12345

When I go to the Layout tab, I get the following message "Could not generate a list of fields for the query. Check the query syntax or click Refresh Fields on the query toolbar." Invalid column name.

Any help would be appreciated.

Thanks,
DLW

 

[Catadmin]

Unfortunately, when creating a dataset, all the parameters in said dataset have to be declared in the report or RS will be very confused.

Your best bet is to make your query a stored procedure instead of a select stmt. Test the SP in QA to make sure it works. Then execute the stored procedure from the dataset (Exec MySP). This will avoid the report being confused on your function's parameter issue.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???