A very good practice is to use SQL Server stored procedures for data modification and data retrieval. Stored procedures provide many benefits. Some of the benefits include:

• Data abstraction: Client applications don't need to work with the data directly; they rather call the stored procedures. Underlying schema might even get modified without an impact on an application, as long as you change the stored procedures that work with the objects with modified schema appropriately.
• Security: Client applications can access data through stored procedures and other programmatic objects only. For example, even if an end user uses their own SQL Server Management Studio instead of the client application that the user should use, the user still cannot modify the data in an uncontrolled way, directly in the tables.
• Performance: Stored procedures can reduce network traffic, because you can execute many statements inside the procedure within a single call to a stored procedure. In addition, SQL Server has a lot of work with the optimization and compilation of the code an application is sending. SQL Server optimizes this by storing the optimized and compiled code in memory. The compiled execution plans for stored procedures are typically held longer in memory than the execution plans for ad hoc queries, and thus get more frequently reused.
• Usage: Stored procedures accept input and can return output parameters, so they can be easily coded to serve multiple users.

The code in the following example creates a stored procedure to insert a row into the dbo.SimpleOrders table. The procedure accepts one input parameter for each column of the table:

CREATE PROCEDURE dbo.InsertSimpleOrder 
(@OrderId AS INT, @OrderDate AS DATE, @Customer AS NVARCHAR(5)) 
AS 
INSERT INTO dbo.SimpleOrders 
 (OrderId, OrderDate, Customer) 
VALUES 
 (@OrderId, @OrderDate, @Customer); 

Here is a similar procedure for inserting data into the dbo.SimpleOrderDetails table:

CREATE PROCEDURE dbo.InsertSimpleOrderDetail 
(@OrderId AS INT, @ProductId AS INT, @Quantity AS INT) 
AS  
INSERT INTO dbo.SimpleOrderDetails 
 (OrderId, ProductId, Quantity) 
VALUES 
 (@OrderId, @ProductId, @Quantity); 

Let's test the procedures. In the first part, the two calls to the dbo.InsertSimpleOrder procedure insert two new orders:

EXEC dbo.InsertSimpleOrder 
 @OrderId = 5, @OrderDate = '20160702', @Customer = N'CustA'; 
EXEC dbo.InsertSimpleOrderDetail 
 @OrderId = 5, @ProductId = 1, @Quantity = 50; 

The following code calls the dbo.InsertSimpleOrderDetail procedure four times to insert four order details rows:

EXEC dbo.InsertSimpleOrderDetail 
 @OrderId = 2, @ProductId = 5, @Quantity = 150; 
EXEC dbo.InsertSimpleOrderDetail 
 @OrderId = 2, @ProductId = 6, @Quantity = 250; 
EXEC dbo.InsertSimpleOrderDetail 
 @OrderId = 1, @ProductId = 5, @Quantity = 50; 
EXEC dbo.InsertSimpleOrderDetail 
 @OrderId = 1, @ProductId = 6, @Quantity = 200; 

The following query checks the state of the two tables after these calls:

SELECT o.OrderId, o.OrderDate, o.Customer, 
  od.ProductId, od.Quantity 
FROM dbo.SimpleOrderDetails AS od 
  RIGHT OUTER JOIN dbo.SimpleOrders AS o 
    ON od.OrderId = o.OrderId 
ORDER BY o.OrderId, od.ProductId; 

Here is the result after the inserts through the stored procedures:

    OrderId     OrderDate  Customer ProductId   Quantity
    ----------- ---------- -------- ----------- -----------
    1           2016-07-01 CustA    3           150
    1           2016-07-01 CustA    5           50
    1           2016-07-01 CustA    6           200
    1           2016-07-01 CustA    7           100
    2           2016-07-01 CustB    4           200
    2           2016-07-01 CustB    5           150
    2           2016-07-01 CustB    6           250
    3           2016-01-01 CustC    NULL        NULL
    4           2016-01-01 CustD    NULL        NULL
    5           2016-07-02 CustA    1           50
  

You can see in the result of the previous query that there are still some orders without order details in your data. Although this might be unwanted, it could happen quite frequently. Your end users might often need to quickly find orders without details. Instead of executing the same complex query over and over again, you can create a view which encapsulates this complex query. Besides simplifying the code, views are also useful for tightening security. Just like stored procedures, views are securables as well. A DBA can revoke the direct access to tables from end users, and give them access to the views only.

The following example creates a view that finds the orders without details. Note that a view in SQL Server can consist of a single SELECT statement only, and that it does not accept parameters:

CREATE VIEW dbo.OrdersWithoutDetails 
AS 
SELECT o.OrderId, o.OrderDate, o.Customer 
FROM dbo.SimpleOrderDetails AS od 
  RIGHT OUTER JOIN dbo.SimpleOrders AS o 
    ON od.OrderId = o.OrderId 
WHERE od.OrderId IS NULL; 

Now the query that finds the orders without details becomes extremely simple—it just uses the view:

SELECT OrderId, OrderDate, Customer 
FROM dbo.OrdersWithoutDetails; 

Here is the result, the two orders without order details:

    OrderId     OrderDate  Customer
    ----------- ---------- --------
    3           2016-01-01 CustC
    4           2016-01-01 CustD
  

If you need to parameterize a view, you have to use an inline table-valued function instead. Such a function serves as a parameterized view. SQL Server also supports multi-statement table-valued functions and scalar functions. The following example shows an inline table-valued function that retrieves the top two order details ordered by quantity for an order, where the OrderID is a parameter:

CREATE FUNCTION dbo.Top2OrderDetails 
(@OrderId AS INT) 
RETURNS TABLE 
AS RETURN 
SELECT TOP 2 ProductId, Quantity 
FROM dbo.SimpleOrderDetails 
WHERE OrderId = @OrderId 
ORDER BY Quantity DESC; 

The following example uses this function to retrieve the top two details for each order with the help of the APPLY operator:

SELECT o.OrderId, o.OrderDate, o.Customer, 
  t2.ProductId, t2.Quantity 
FROM dbo.SimpleOrders AS o 
  OUTER APPLY dbo.Top2OrderDetails(o.OrderId) AS t2 
ORDER BY o.OrderId, t2.Quantity DESC; 

Note that another form of the APPLY operator is used, the OUTER APPLY. This form preserves the rows from the left table. As you can see from the following result, the query returns two rows for orders with two or more order details, one for orders with a single order detail, and one with NULL values in the place of the order details columns for orders without an order detail:

    OrderId     OrderDate  Customer ProductId   Quantity
    ----------- ---------- -------- ----------- -----------
    1           2016-07-01 CustA    6           200
    1           2016-07-01 CustA    3           150
    2           2016-07-01 CustB    6           250
    2           2016-07-01 CustB    4           200
    3           2016-01-01 CustC    NULL        NULL
    4           2016-01-01 CustD    NULL        NULL
    5           2016-07-02 CustA    1           50