Menu
I have a sql query that I will be reusing in multiple stored procedures. The query works against multiple tables and returns an integer value based on 2 variables passed to it.
Rather than repeating the query in different stored procedures I want to share it and have 2 options:
I am leaning towards option 1 but would like opinions on which is better and common practice. Which would be better performance wise etc. (joining to a view or calling function)
EDIT: The RDBMS is SQL Server
259
The code to implement a given logic should be implemented once, and once only, and reused by all applications that need it. However, of course, due care must be taken when reusing SQL code. Careless reuse of code can lead to maintenance and performance issues, especially when this reuse takes the form of scalar UDFs.
To reuse a case expression value in multiple places within a SQL statement, you would have to define and create a User defined function that outputs that case statement value. @user2391001 You may be able to use a common table expression, but that would depend on the query.
The SQL WITH clause is good when used with complex SQL statements rather than simple ones. It also allows you to break down complex SQL queries into smaller ones which make it easy for debugging and processing the complex queries. The SQL WITH clause is basically a drop-in replacement to the normal sub-query.
To have multiple WITH clauses, you do not need to specify WITH multiple times. Rather, after the first WITH clause is completed, add a comma, then you can specify the next clause by starting with <query_name> followed by AS. There is no comma between the final WITH clause and the main SQL query.
If you will always be using the same parametrised predicate to filter the results then I'd go for a parametrised inline table valued function. In theory this is treated the same as a View in that they both get expanded out by the optimiser in practice it can avoid predicate pushing issues. An example of such a case can be seen in the second part of this article.
As Andomar points out in the comments most of the time the query optimiser does do a good job of pushing down the predicate to where it is needed but I'm not aware of any circumstance in which using the inline TVF will perform worse so this seems a rational default choice between the two (very similar) constructs.
The one advantage I can see for the View would be that it would allow you to select without a filter or with different filters so is more versatile.
Inline TVFs can also be used to replace scalar UDFs for efficiency gains as in this example.
104
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
