Friday, February 27, 2009

Sql Server Stored Procedures

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

exec spAdd @p1 = 56, @p2 = 65
spAdd @p1 = 86, @p2 = 65
spAdd 45, 23

If

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


Stored Procedure for Selecting values from a Table
CREATE PROCEDURE spAllCategories
AS
select * from Categories
GO
spAllCategories
Stored Procedure that encapsulated complex queries
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'

1 comment:

  1. Have a doubt.
    In 'C' main() call by the compiler of C.Similarly,
    Who call the 'system' namespace in ASP.Net?

    ReplyDelete