• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

options for query to be converted to report.

when the results of a query have to be transformed to a report which different people can hit/run at the same time, what the best options to capsulate the query in? (like view vs proc vs udf).. at least 2 parameters maybe part of the input from user which will be used in the query to make the report.
3 Solutions
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'hit/run'.

Assuming SSRS..
If we're talking parameters, a stored procedure will work every time, and I almost always use SP's behind reports.
Only materialized views will accept parameters, which imply a host of other side effects that may not be desired.
No clue on the udf.
Kevin CrossChief Technology OfficerCommented:
User-defined functions can take parameters; however, you may sacrifice performance depending on the type of UDF.  I agree with JIm that stored procedures are a good option.  I also use UDFs in some situations with success, so you can take that approach if you need specific functionality like JOINing the results to another table.
I agree with JIm that stored procedures are a best option

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now