Any advantage using stored proc over a view?

Question

I have a stored proc that performs a fairly complex SELECT statement. The stored proc accepts to params which are used in the query:

ALTER PROCEDURE [dbo].[GetConnections]
    @equipmentId int,
    @equipmentPortNum int
AS
    SELECT ..SELECT QUERY HERE..
END

I'm considering changing this to a view, and just filtering the view when I call it:

SELECT * from ConnectionsView Where EquipmentID = XXX and EquipmentPortNum = YYY

My question is: Is there any advantage of one over the other - esp wrt performance?

Since I will be calling the view from an ORM, the query will have parametised filter options and result in a cached execution plan, meaning (unless I'm wrong) that the stored proc won't be any more performant over the view - is this correct?

possible duplicate of [What is more powefull between a stored procedure and a view?](http://stackoverflow.com/questions/4507399/what-is-more-powefull-between-a-stored-procedure-and-a-view) — Neil Knight, Feb 15 '11 at 09:52
Why not a UDF, since that seems to be what you're describing (a single select that needs to be parameterised)? Unless your ORM doesn't support them... — Damien_The_Unbeliever, Feb 15 '11 at 09:59
Possible duplicate of [SQL-Server Performance: What is faster, a stored procedure or a view?](https://stackoverflow.com/questions/1603853/sql-server-performance-what-is-faster-a-stored-procedure-or-a-view) — Jamal, Jun 10 '17 at 20:05

score 0 · Accepted Answer · edited May 23 '17 at 12:04

0

Duplicate.. A question asked by myself Here

And another one Here asked by Roberto Sebestyen

I hope you'll find answer in these ones..

edited May 23 '17 at 12:04

Community

1
1

answered Feb 15 '11 at 09:50

bAN

13,375
16
60
93

Any advantage using stored proc over a view?

1 Answers1