Stored Procedures Vs. Views

sql sql-server-2008 stored-procedures comparison views
Vishal picture Vishal · Sep 22, 2010 · Viewed 68.2k times · Source

I have used both but what I am not clear is when I should prefer one over the other. I mean I know stored procedure can take in parameters...but really we can still perform the same thing using Views too right ?

So considering performance and other aspects when and why should I prefer one over the other ?

Answer

gbn picture gbn · Sep 22, 2010

Well, I'd use stored proc for encapsulation of code and control permissions better.

A view is not really encapsulation: it's a macro that expands. If you start joining views pretty soon you'll have some horrendous queries. Yes they can be JOINed but they shouldn't..

Saying that, views are a tool that have their place (indexed views for example) like stored procs.

Related questions

SQL Server Insert if not exists

I want to insert data into my table, but insert only data that doesn't already exist in my database. Here is my code: ALTER PROCEDURE [dbo].[EmailsRecebidosInsert] (@_DE nvarchar(50), @_ASSUNTO nvarchar(50), @_DATA nvarchar(30) ) AS BEGIN INSERT INTO EmailsRecebidos (De, Assunto, …

Read More
SQL Server stored procedure parameters

I am developing a framework, where in I am a calling stored procedure with dynamically created parameters. I am building parameter collection at the runtime. The problem occurs when I am passing a parameter to stored procedure, but stored proc …

Read More
SQL Server: Invalid Column Name

I am working on modifying the existing SQL Server Stored Procedure. I added two new columns to the table and modified the stored procedure as well to select these two columns as well. Although the columns are available in the …

Read More