January 6, 2011 at 2:35 pm
I'm currently trying to add an extra field to a table that gets its dataset from a stored procedure. The field I'm trying to add depends on one of the fields in the dataset returned from the stored procedure.
Similar to:
Stored procedure returning transaction information, including product_id of product sold in the transaction.
I need to add a field containing the name of the product that was sold to the report, but I can't modify the stored procedure, and copying it to a query would be less than ideal, as it would mean two copies of the same report in circulation.
I've looked at using two datasets, and using a subreport to pull the product's name from the database, but I haven't been successful either way, and would appreciate some direction.
January 7, 2011 at 7:04 am
sqlservercentral.com 32358 (1/6/2011)
I'm currently trying to add an extra field to a table that gets its dataset from a stored procedure. The field I'm trying to add depends on one of the fields in the dataset returned from the stored procedure.Similar to:
Stored procedure returning transaction information, including product_id of product sold in the transaction.
I need to add a field containing the name of the product that was sold to the report, but I can't modify the stored procedure, and copying it to a query would be less than ideal, as it would mean two copies of the same report in circulation.
I've looked at using two datasets, and using a subreport to pull the product's name from the database, but I haven't been successful either way, and would appreciate some direction.
Can you not change the stored procedure because a) you don't have the rights and you don't want to ask someone else to change it for you, b) no one in your company has the ability (i.e. vendor supplied etc.), or c) some other reason.
It seems the best way to approach this would be to modify the stored procedure, if everything else was written to appropriate coding standards(i.e. not using Select * and other such shortcuts so that require a certain number of columns to be returned in a certain order.
If that truly is something that can't be done, it seems like you are destined to have 2 different reports, one that shows the product name and one that doesn't. Since that's the case I'd suggest doing it in the dataset query, either with a new stored procedure that contains the extra column or perhaps a view that can be queried. If those two are not options then you might have some luck writing the result of the stored procedure to a temp table and joining that back in the next part of your query, but again that seems much more complicated than either modifying the procedure or just using a new query.
-Luke.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply