Local Parameters in Inline function or view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Local Parameters in Inline function or view

Author

Topic

amnon kariv
Starting Member

1 Post

Posted - 2009-11-24 : 11:13:24

Hi all,
I want to write an inline function or a view that runs a query that is filter by few parameters.
Due to the fact that in both cases, DECLARE is not permitted, i'm using a WITH statement which select one row with all the required parameters.
Example:

WITH Parameters (Param1, Param2) AS
(
SELECT
(SELECT ... FROM ... ) AS Param1,
(SELECT ... FROM ... ) AS Param2
)
SELECT * FROM Parameters JOIN Transactions ON 1=1
INNER JOIN ... ON ... AND Type1 = Parameters.Param1
INNER JOIN ... ON ... AND Type2 = Parameters.Param2
...

Q1: Is that the way to accomplish my goal or there is some better solution?

Q2: When I'm using the function as above, it runs much slower then using constant values.
Example:
INNER JOIN ... ON ... AND Type1 = 1
INNER JOIN ... ON ... AND Type2 = 5

and even slower (1.5 times slower) then using constants for the WITH.
Example:
WITH Parameters (Param1, Param2) AS
(
SELECT 1, 5
)

Any idea why or what can I do to improve the performance?

Thanks
Amnon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources