Understanding SQL Server 2014 Programmability Training Videos for Beginner

1
Understanding SQL Server 2014 Programmability
Training Videos for Beginner by
2
Highlights

• Stored Procedures
• Creation of Stored Procedures
• Built-in Functions
• Scalar Functions

3
Stored Procedures

• Set of SQL statements compiled and saved into
  memory location.
• Very powerful than views.
• Includes data validation and access control
  mechanisms.
• Required to save time, storage, extensive or
  complex processing group of SQL statements.
• Resembles like user defined functions but should
  be invoke by call statements and condition
  handlers or a database trigger.

4
Creation of Stored Procedures

• Stored procedures can be created in two ways, one
  is by using management studio and another way is
  using Transact-SQL.
• Lets check out procedure of creating stored
  procedures using management studio.
• connect to an instance of Database Engine and
  then expand that instance in object explorer.
• Expand the databases, adventure works
  2012 database, and then expand program ability.

5
Creation of Stored Procedures

• Give a right click on Stored Procedures, and then
  click New Stored Procedure.
• In Query menu, click Specify Values for Template
  Parameters.
• In the Specify Values for Template
  Parameters dialog box and Click OK.
• Now In the Query Editor, replace the SELECT
  statement with the following statement
• SELECT FirstName, LastName, Department FROM
  HumanResources.vEmployeeDepartmentHistory WHERE
  FirstName _at_FirstName AND LastName _at_LastName
  AND EndDate IS NULL

6
Creation of Stored Procedures

• Click Parse to test the syntax on the Query menu.
  If an error occurred, compare the statements with
  the information and correct as needed.
• Click Execute to create the procedure, from
  the Query menu.
• Give a right click on Stored Procedures and
  select Refresh to check the procedure listed in
  Object Explorer.
• Give a right click on the stored procedure
  name HumanResources.uspGetEmployees Test and
  select Execute Stored Procedure to run the
  procedure in Object Explorer.

7
Creation of Stored Procedures

• In the Execute Procedure window, enter Abner as
  the value for the parameter _at_LastName and enter
  the value Michael as the value for the parameter
  _at_FirstName.
• Creation of stored procedures using Transact-SQL
  is as follows
• connect to an instance of Database Engine in
  object explorer.
• click on New Query from the file menu.
• Copy and paste the following example code into
  the query window and click Execute.
• Code USE AdventureWorks2012 Go

8
Creation of Stored Procedures

• CREATE PROCEDURE HumanResources.uspGetEmployeesTe
  st2
• _at_LastName nvarchar(100), _at_FirstName
  nvarchar(100)
• AS
• SET NOCOUNT ON SELECT FirstName, LastName,
  Department FROM HumanResources.vEmployeeDepartment
  History WHERE FirstName _at_FirstName AND LastName
  _at_LastName AND EndDate IS NULL GO
• Stored procedure using Transact-SQL has been
  created.

9
Built-in Functions

• Defined as small programming codes, takes zero or
  more inputs and returns a value. 
• Allows users to create User Defined
  Functions(UDF).
• SQL Server provides many built-in functions they
  are 1. scalar functions and
• 2. Rowset functions returns an object that can be
  used like table references in an SQL statement.
• 3. Aggregate functions operates on a collection
  of values but return a single value.
• 4. Ranking functions returns a ranking value for
  each row in a partition.

10
Scalar Functions

• Scalar functions operates on a single value and
  then return a single value. Scalar functions can
  be used wherever an expression is valid. Some of
  scalar functions are as follows

Source https//msdn.microsoft.com
11
Feedback
Like It ? Hate It?
We would love to hear from you whatever you think
about the course.
Click HERE to share what you think!
12
Thank you
Thank You for Watching Understanding SQL Server
2014 Programmability Training Videos for
Beginner by
http//www.courseing.com