Can we create parameterized VIEW in SQL Server 2008.
Or Any other alternative for this ?
Add a comment
|
6 Answers
Try creating an inline table-valued function. Example:
CREATE FUNCTION dbo.fxnExample (@Parameter1 INTEGER)
RETURNS TABLE
AS
RETURN
(
SELECT Field1, Field2
FROM SomeTable
WHERE Field3 = @Parameter1
)
-- Then call like this, just as if it's a table/view just with a parameter
SELECT * FROM dbo.fxnExample(1)
If you view the execution plan for the SELECT you will not see a mention of the function at all and will actually just show you the underlying tables being queried. This is good as it means statistics on the underlying tables will be used when generating an execution plan for the query.
The thing to avoid would be a multi-statement table valued function as underlying table statistics will not be used and can result in poor performance due to a poor execution plan.
Example of what to avoid:
CREATE FUNCTION dbo.fxnExample (@Parameter1 INTEGER)
RETURNS @Results TABLE(Field1 VARCHAR(10), Field2 VARCHAR(10))
AS
BEGIN
INSERT @Results
SELECT Field1, Field2
FROM SomeTable
WHERE Field3 = @Parameter1
RETURN
END
Subtly different, but with potentially big differences in performance when the function is used in a query.
4 Comments
TWood
This was a helpful answer for me. Thanks AdaTheDev!
Timofey
There is a problem if one needs make a call between Linked Servers, table-valued functions are not allowed.
Jeff Widmer
The example of what to avoid is very, very important. I just made one change to match my function to the first example (moving it away from the "what to avoid") and i got a huge performance improvement.
G. Stoynev
Is a UNION in the "good" example going to move it to "avoid" category?
No, you cannot. But you can create a user defined table function.
Comments
in fact there exists one trick:
create view view_test as
select
*
from
table
where id = (select convert(int, convert(binary(4), context_info)) from master.dbo.sysprocesses
where
spid = @@spid)
... in sql-query:
set context_info 2
select * from view_test
will be the same with
select * from table where id = 2
but using udf is more acceptable
4 Comments
Adriaan Stander
I would seriously CRY, lots and lots of crying, buckets full. Sleepless nights...
gjvdkamp
No disrespect, but this is a good example of the difference between what you can do, and what you should (or not) do.
heximal
i don't insist on that solution. and i never used it in mssql myself. i've just mentioned that it is possible theoretically
Chris Cudmore
Upvoted for the interesting hack. Not that this should be used.
As astander has mentioned, you can do that with a UDF. However, for large sets using a scalar function (as oppoosed to a inline-table function) the performance will stink as the function is evaluated row-by-row. As an alternative, you could expose the same results via a stored procedure executing a fixed query with placeholders which substitutes in your parameter values.
(Here's a somewhat dated but still relevant article on row-by-row processing for scalar UDFs.)
Edit: comments re. degrading performance adjusted to make it clear this applies to scalar UDFs.
2 Comments
AdaTheDev
If you write the UDF as an inline table-valued function, there is no reason why performance should be bad at all. If you're talking about scalar functions then yes, the function will be evaluated for each row
davek
sorry for the oversight - you're quite right. I'll edit my answer to make that clear.
I know this is more than a decade question but I'd like to mention that there is another option to create a parameterized view to get the data by using parameters.
With common table expression (CTE), you can use it as a view. There's no need to create a variable temp table and worry to delete at the end. You can also see it as an inline view.
For example, a table has many columns but we want to display only columns that we want to.
DECLARE @Parameter1 varchar(50)
;WITH MyView AS (
SELECT Column1, Column2, Column3, Column4, Column5, Column6
FROM TableA
)
SELECT Column2, Column4
FROM MyView
WHERE Column6 = @Parameter1
1 Comment
BimmerM3
A CTE is not the same thing as a view.
no. You can use UDF in which you can pass parameters.
1 Comment
Adriaan Stander
Have you read any of the other answers? Might this already have been mentioned?