Friday, February 27, 2009

Sql Server Stored Procedures

Stored Procedures and block of code which does one or more actions on a database. 


CREATE PROCEDURE spHello as
 PRINT 'Hello welcome to T-Sql'
GO
Executing a stored Procedure
EXEC spHello
Stored Procedure with varchar parameter and string concatenation
create procedure spHelloName (@Name varchar(20))
as 
 print 'Hello ' + @Name
go
Procedure with integer Parameter and addition
Memory variables must preceded with @ symbol
create procedure spAdd(@p1 int, @p2 int)
as
 print @p1 + @p2
go


If

create procedure spIf (@pCity varchar(40))
as
if @pCity = 'Kochi'
 print 'Industrial capital  of Kerala'
go

While Loop
create or alter procedure spWhile
as
DECLARE @cnt INT = 0;

WHILE @cnt < 10
BEGIN
   PRINT 'Hello '
   SET @cnt = @cnt + 1;
END;
GO

While Loop with Cast

create or alter procedure spWhileCounter
as
DECLARE @cnt INT = 0;

WHILE @cnt < 10
BEGIN
   PRINT 'Hello ' + cast(@cnt as varchar(3))
   SET @cnt = @cnt + 1;
END;
GO 


Stored Procedure for Selecting values from a Table

CREATE PROCEDURE spAllCategories
AS
 select * from Categories
GO
spAllCategories


Stored Procedure with Joins
create procedure spAllProductsCategory
as
SELECT C.CategoryName, P.ProductName, P.UnitPrice FROM Categories C , Products P WHERE C.CategoryId = P.CategoryId
go


Stored Procedure with Where Clause
create procedure spProductsCategorySearch(@pCategoryId int)
as
SELECT C.CategoryName, P.ProductName, P.UnitPrice FROM Categories C , Products P WHERE C.CategoryId = P.CategoryId and C.CategoryId = @pCategoryId
go
spProductsCategorySearch 1
spProductsCategorySearch 2


Stored Procedure for Inserting values into a Table
create procedure spInsertCategories (@pCategoryName varchar(40), @pDescription varchar(60))
as
insert into Categories (CategoryName, Description) values (@pCategoryName, @pDescription)
GO

EXEC spInsertCategories @pCategoryName = 'Condiments', @pDescription = 'Sweets and savory sauces, relishes, spreads, and seasonings'spInsertCategories 'Confections', 'Desserts, candies, and sweet breads'


User Managament Scripts
create database UserManagement

use UserManagement

create table Roles(RoleId int identity(1,1) primary key, Role varchar(40))

create table Users (UserId int identity(1,1) primary key,
RoleId int References Roles(RoleId), UserName varchar(40),
Password varchar(40))

create procedure InsertRole(@pRoleName varchar(40))
as
insert into Roles values (@pRoleName)
go

insertRole 'Admin'

create procedure spAllRoles as
select * from Roles
go

create procedure spInsertUser(@pGroupId int,
@pUserName varchar(40), @pPassword varchar(40))
as
insert into Users values (@pGroupId, @pUserName, @pPassword )
go

spInsertUser 1, 'Greorge', 'george'
spAllRoles

create procedure spAllUsers
as
select * from Users
go

spAllUsers

create procedure spUserCount (@pUserName varchar(40), @pPassword varchar(40), @userCount int output)
as
select @userCount = COUNT(*) from Users where UserName = @pUserName and Password =@pPassword
return
go

spUserCount 'George', 'george', ''

create procedure GetUserRole (@pUserName varchar(40))
as
select R.Role from Roles R, Users U where R.RoleId = U.RoleId and U.UserName = @pUserName
go

GetUserRole 'Shalvin'

GetUserRole 'Manu'

Wednesday, February 25, 2009

Be Exalted O God Chords

A D A
I will give thanks to Thee
A D A
O Lord, among the people
(E) F#m E D A Bm E
I will sing praises to Thee among the nations
A D A
For Thy steadfast love is great
A D A
Is great to the heavens
(E) F#m
And Thy faithfulness,
B7 E D C#m7
Thy faithfulness to the sky

D E A A D A E B7 E
Be ex--alted, O -- God -- above the heavens
D E A F#7/A# Bm
Let Thy glory be over all the earth
D/E E A A D A E B7 E
Be ex--alted, O -- God -- above the heavens
Bm E A D A
Let Thy glory be over all the earth!

Ending:
D E A C# F#m
Let Thy glo--ry, let thy glo--ry
Bm7 E7 A D A
Let thy glory be over all the earth!

Awesome God Chords

F#m
Oh when He rolls up his sleeves
F#m
He ain't just puttin' on the ritz,
Bm7 C#m7 F#m
Our God is an awesome God.
F#m
There is thunder in his footsteps
F#m
And lightning in his fists,
Bm7 C#m7 F#m
Our God is an awesome God.

Bm7
And the Lord wasn't joking when He kicked 'em out of Eden.
C#m7
It wasn't for no reason that He shed his blood.
D
His return is very soon
D/B D/F#
And so you'd better be believin' that
Bm7 C#m7 F#m
Our God is an awesome God.

D A
Our God is an awesome God.
E F#m
He reigns from heaven above
D A
with wisdom, pow'r and love
Bm7 C#m7 F#m
Our God is an awesome God

And when the sky was starless in the void of the night,
Our God is an awesome God.
He spoke into the darkness and created the light,
Our God is an awesome God.
And judgment and wrath He poured out on Sodom.
Mercy and grace He gave us at the cross.
I hope that we have not too quickly forgotten that
Our God is an awesome God