Thursday, January 17

Stored Procedures continued..


Here we use AdventureWorksDW2008 database as we had used in earlier SQL-Topics. Suppose we need full-name of the customers along with their gender from DimCustomer Table, We use below query.

select FirstName + ' ' + LastName as [Full Name], Gender from DimCustomer

Now we will create a Stored Procedure for this Select query.

CREATE PROCEDURE Customer_Details
as
begin
select FirstName + ' ' + LastName as [Full Name], Gender from DimCustomer
end

Now call this SP by- EXEC Customer_Details

Result:


Now if we want to specifically select the Gender we can add Where Clause in the select statement as show below.


ALTER PROCEDURE Customer_Details
as
begin
select FirstName + ' ' + LastName as [Full Name], Gender from DimCustomer WHERE Gender = 'F'
end

EXEC Customer_Details

Result:

You could see that only female customers List is produced.

Stored Procedures using Variables

Now if we want to do same thing by declaring variables, See the below queries.


ALTER PROCEDURE  Customer_Details
as
begin
declare @gender nvarchar(1)
set @gender = 'F'
select FirstName + ' ' + LastName as [Full Name],Gender from DimCustomer where Gender = @gender
end


EXEC Customer_Details

Result: Will be same as above.

Stored Procedures using Parameters

Now the same query can be written in another way such that the user can decide the output, for eg. Male or Female in this case.


ALTER PROCEDURE   Customer_Details (@gender nvarchar(1)) 
as
begin

select FirstName + ' ' + LastName as [Full Name],Gender from DimCustomer where Gender = @gender
end

EXEC Customer_Details 'F'

Result: Will be same as above.



** Related Topics on Stored Procrdures




Wednesday, January 16

Stored Procedure for Inserting values into a Table


Stored Procedure for Inserting values into Employee table.

Consider that i already created a table called EMPLOYEE, which has Empid, Empname, Salary and Place as columns. Now i want to insert values into these columns using Stored Procedure, So that i don't need to use INSERT statements everytime. See the below Stored Procedure.


CREATE PROCEDURE [dbo].[sp_insertvalues]       

     (
       @Empid int,
     @Empname varchar(50),
@Salary money,
@Place varchar(50)
     )
As
Begin
INSERT into Employee (Empid, Empname, Salary, Placevalues (@Empid, @Empname, @Salary,@Place)
End

Now if you want to add another column or modify the created Stored Procedure, write the  query as shown below.


ALTER PROCEDURE [dbo].[sp_insertvalues]       

     (
       @Empid int,
     @Empname varchar(50),
@Salary money,
@Place varchar(50)
@Gender Varchar(5)
@Address Varchar(100)
     )
As
Begin
INSERT into Employee (Empid, Empname, Salary, Place, Gender, Addressvalues (@Empid, @Empname, @Salary, @Place, @Gender, @Address )
End


To call/Execute this Stored Procedure, see the query below.

EXEC sp_insertvalues 12'John'20000'ZZZ''M''YYY'
EXEC sp_insertvalues 14'Benn'25000'ZZZ''M''KKK'

** These values will be inserted into Employee table


** Related Topics on Stored Procrdures

Stored Procedure-SQL

Stored Procedure

stored procedure is a group of Transact-SQL statements compiled into a single execution plan. Or, it can be defined as pre-compiled database queries that improve the security, efficiency and usability of database client/server applications. Its a piece of SQL code that is physically stored in the database and it can be executed (like query) again and again for different purposes. It can be as simple as simple SQL selection query and as complex as doing multiple tasks (insertion, selection, joins, deletion) at the same time. 

How can it add to the security? By encapsulation of code, i.e the user just calls the procedure and executes it, the full code is not displayed.

Syntax


CREATE PROCEDURE Procedure_Name       

 --Instead of Procedure you can also use Proc 

/*  

( @parameter1 datatype = default value,
      @parameter2 datatype OUTPUT
 ) 
*/

As

Begin

-- YOUR QUERY

End

For Example, Consider that i already created a table called EMPLOYEE, which has Empid, Empname, Salary and Place as columns. Now i want to insert values into these columns using Stored Procedure, So that i don't need to use INSERT statements everytime. See the below Stored Procedure.

Stored Procedure for Inserting values into Employee table.


CREATE PROCEDURE [dbo].[sp_insertvalues]       

     (
       @Empid int,
     @Empname varchar(50),
@Salary money,
@Place varchar(50)
     )
As
Begin
INSERT into Employee (Empid, Empname, Salary, Place) values (@Empid, @Empname, @Salary,@Place)
End

Now if you want to  modify and insert values into some more columns, write the  query as shown below.


ALTER PROCEDURE [dbo].[sp_insertvalues]       

     (
       @Empid int,
     @Empname varchar(50),
@Salary money,
@Place varchar(50)
@Gender Varchar(5)
@Address Varchar(100)
     )
As
Begin
INSERT into Employee (Empid, Empname, Salary, Place, Gender, Addressvalues (@Empid, @Empname, @Salary, @Place, @Gender, @Address )
End

To call/Execute this Stored Procedure, see the query below.

EXEC sp_insertvalues 12, 'John', 20000, 'ZZZ', 'M', 'YYY'

** These values will be inserted into Employee table

** Related Topics on Stored Procrdures


Tuesday, January 8

Amazing China -1(Bridges)







Hangzhou Bay Bridge


China’s Hangzhou Bay Trans-oceanic Bridge is one of the masterpieces of modern architecture. It is the longest sea-crossing bridge in the world - 36 kilometres - and comes with a price tag of 11.8 billion yuan (US$1.70 billion).

Opened in May 2008, some 600 Chinese engineers, draftsmen, architects and designers spent 9 years on planning, preparation and design of the bridge. It is an S-shaped stayed-cable bridge linking Ningbo's Cixi county in the south to Jiaxing in the north. The Bridge has a 6-lane motorway which shaves 120 kms off the driving journey between Shanghai and Ningbo. There  are also 2 extra lanes reserved for backup and emergency use.  














Hotel & Observatory tower in middle of Hangzhou Bay Bridge


What is so unique about the Hangzhou Bay Bridge is that it has a 10,000 sq. metre  service centre right in its middle. The centre has a restaurant, petrol kiosk, hotel,  conference rooms, and a lookout tower from where visitors  can observe the extraordinary tide and waves of Hangzhou Bay.

The service centre is like an artificial island which rests on piers to avoid obstructing the sea current in the Bay. Two public parks are also being planned on either side of the bridge.
Hangzhou Bay is known for it’s tides and waves -  a natural wonder and a major tourist attraction.  The waves move at speeds reaching 30 kph and at times, sound like the roar of thunder.

The S-shaped bridge was  specially designed and built to withstand  typhoon and hurricane weather.