Title: Understanding SQL Server 2014 Programmability Training Videos for Beginner
1Understanding SQL Server 2014 Programmability
Training Videos for Beginner by
2Highlights
- Stored Procedures
- Creation of Stored Procedures
- Built-in Functions
- Scalar Functions
3Stored 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.
4Creation 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.
5Creation 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
6Creation 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.
7Creation 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
8Creation 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.
9Built-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.
10Scalar 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
11Feedback
Like It ? Hate It?
We would love to hear from you whatever you think
about the course.
Click HERE to share what you think!
12Thank you
Thank You for Watching Understanding SQL Server
2014 Programmability Training Videos for
Beginner by
http//www.courseing.com