Views, Stored Procedures and Functions

SQL Server can store queries and logic inside the database itself. You will save queries as views, write stored procedures with parameters, create your own functions, and call a stored procedure from C#.

Updated

Views in T-SQL

  1. Views in T-SQL (Journeyman): A view is a saved query with a name. Query it like a table, and the long join inside is written once. Create it with CREATE OR ALTER VIEW, in a batch of its own.

Stored Procedures in T-SQL

  1. Creating Stored Procedures (Journeyman): A stored procedure is a named, saved batch of SQL that takes parameters. Create one with CREATE OR ALTER PROCEDURE, give parameters defaults, and run it with EXEC.
  2. Calling a Stored Procedure from C# (Journeyman): In ADO.NET, put the procedure's name in a SqlCommand, set its CommandType to StoredProcedure, and add each parameter by name. Then read the results as from any query.

User-Defined Functions in T-SQL

  1. User-Defined Functions in T-SQL (Journeyman): Write your own functions. A scalar function returns one value and works inside any query; a table-valued function returns rows and works like a table that takes parameters.

Exercise: Views, Procedures and Functions

  1. Exercise: Views, Procedures and Functions (Journeyman): Ten problems that create views, stored procedures and functions in Northwind and then use them, each with the result to expect.