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