Performing CRUD Operations with Stored Procedures

Performing CRUD Operations with
Stored Procedures
It ’ s a common IT department policy to limit database access by users and most applications to stored
procedures for all CRUD operations. The primary benefit of substituting stored procedures for SQL
batch operations is increased security. SQL Server requires individual users and applications or groups
to be assigned explicit EXECUTE permission on each stored procedure; other commercial and open - source
RDBMSs have similar restrictions. The major drawback of stored procedures is loss of eSQL query
features. However, read - only views can provide a reasonable compromise between security and
flexibility for many types of enterprise data.
SQL Server stored procedures no longer offer significant performance benefits because, effective with
version 7.0, the database engine no longer compiles queries into stored procedures. Instead,
parameterized queries are cached and reused by multiple - client applications. The parameterized SQL
queries that EF generates from eSQL and executes with sp_executesql are generally accepted to be
immune to common SQL - injection attacks.
The “ Function Subelements and Subgroups ” section and later sections of Chapter 9 , “ Defining Storage,
Conceptual, and Mapping Layers, ” describe the Function and FunctionImport groups of the *.edmx
file ’ s StorageModels group. Fortunately, the Entity Data Model Wizard supports tables, views, stored
procedures and user - defined functions as data sources.