Classes starting soon, register asap

This class is going to start as advertised and you are encourage to sign up as the seats for this hands-on course are limited. Hurry up!
Summary:

Databases, organized repositories of information, have become indispensable in today's world. In this introductory course you will learn about databases and the basics of Structured Query Language (SQL) including sorting, distinct, and aggregate functions and grouping of data. All SQL statements will initially be written from one table. Most practical, modern and relational databases will include a large number of tables. SQL queries have to access information from several tables. This course will then introduce you to querying more than one table. With this skill you will be able to query two or more tables in a database. This is a hands-on course.


New in 2015: You will also get an introduction to Power Shell.

Dates: (Section 1) Section 1 - Feb 17 - Mar 12, 2015; TTh; 5:30 pm - 7:30 pm; 10 mtgs.
Venue: Honolulu Community College;

Note: SQL Server 2012 Express will be used and a knowledge of SQL Server is not assumed. Students will be using the free version.

For details : Access http://pcatt.org/index.php/search and look up the course or write to:
Hodentek@live.com with course name in the Subject line.

Creating a scalar function in SQL Server 2012

General:
Consider the following equation:

y = x + x*x + x*x*x
where * is a symbol for multiplication

Here y is a function of x (the value of y depends on the value assigned to x) written symbolically as y=f(x)

A Scalar function in general can depend on more than one variable such as y=f(x,z,t) and return a single value.

How do you create a function in SSMS?

The following function named TestMultiply takes two parameters @param1 and @param2 and returns their product, a single value.

Create function [dbo].[TestMultiply]
( @param1 int,
  @param2 int
)
Returns INT
AS

BEGIN

RETURN @param1 * @param2
END
GO

This is implemented in SQL Server (in SQL Server Management Studio) by opening a query window and typing in the above code and executing the query as shown:


How do we evaluate the function?

First of all we should call the function in a query window by selecting it and providing the two parameters.

Here is how it is evaluated for @param1=5 and @param2=10



How can you create a function that uses values in the database tables?

Let us go to the AdventureWorks2012 database and review the Sales.SalesOrderDetail table. The following shows the structure of the table:

In this table we focus on two columns, UnitPrice and OrderQty. We plan to write a function that multiplies OrderQty with UnitPrice and return a value in the function we call TotalOrder. The function we create is as follows:

Create Function [dbo].[TotalOrder]
(
 @OrderQty int,
 @UnitPrice money
)
Returns MONEY

as

BEGIN
Return @OrderQty * @UnitPrice
END
GO

When you execute the query with the above a scalar function called TotalOrder will be created as shown:



In order to use this function you can call it as shown(shown with some results):

There is a lot more to understand. This just Basic stuff.

Learn Querying SQL Server 2012 using LinqPad - Part 4 Stored Procedures_2

In Learn Querying SQL Server 2012 using LinqPad - Part 3 we considered a stored procedure, albeit simple which did not have any output parameters.  LinqPad can also be used for evaluating stored procedures with an out put parameter. Make sure you review Parts 1, 2, and 3.

Let us modify the stored procedure in Part 3 so that the FullName of a Person is returned from the stored procedure as shown here.

Create Procedure PPFull
@fname nvarchar(10),
@lname nvarchar(20),
@fullname nvarchar(25) OUTPUT

as

Select @fullname=FirstName+','+Lastname from Person.person
Where FirstName=@fname and LastName=@lname
go

Note that @fullname varible is declared as an OUTPUT Parameter.

This can be executed in SQL Server Management Studio as shown.

 

The same can be executed in LinqPad as shown here.

Learn Querying SQL Server 2012 using LinqPad - Part 3 Stored Procedures

You can execute stored procedures with LinqPad. In order to do so you need to know the details of the stored procedure which can reviewed in SQL Server Management Studio as shown here for a stored procedure in AdventureWorks2012. Review Learn Querying SQL Server 2012 using LinqPad parts 1 and 2.

Create a simple test stored procedure as shown:

Create Procedure dbo_PP
@fname as nvarchar(10),
@lname as nvarchar(20)
as
Select * from Person.person
Where FirstName=@fname and LastName=@lname
go


This stored procedure requires two arguments, @fname and @lname for its evaluation.

Let us examine and execute this procedure first in SSMS and then in LinqPad.
In SSMS:
You find this stored procedure in the Programmability folder as shown.

 

This can be executed in SSMS as shown. Two paramters are provided

 

Once this procedure is created you can access it in LinqPad (review posts Part1 and Part2 )

 

It can be executed two ways in LinqPad; as a C# expression or SQL 

Evaluated as SQL:

 

Now evaluted as C# Expression:

Now as SQL:

This is how you get the return value: