ASP executando Stored Procedures
© 2000 João Carlos Lauriano

Procedures dos Exemplos

Compile essas Stored Procedure como explicado no tópico: Criar Stored Procedures no banco de
de dados Northwind.

1 - Stored Procedure: MostraCategorias - Seleciona as colunas CategoryID e CategoryName
      da tabela Categories.

use Northwind
go
Create procedure MostraCategorias
as
select CategoryID, CategoryName from Categories
go
grant execute on MostraCategorias to public
go

2 - Stored Procedure: ListaProdutos - Seleciona as colunas ProductName, SupplierID e UnitsInStock,
     da tabela Products cuja coluna CategoryID seja igual ao parâmetro @CategoryID.

use Northwind
go
create proc ListaProdutos( 
@CategoryID int ) 
as 
select ProductName, SupplierID, UnitsInStock
from Products
where CategoryID = @CategoryID and
Discontinued = 0
go
grant execute on ListaProdutos to public
go

3 - Stored Procedure: DetalhaFornecedor - Seleciona as colunas da tabela Suppliers, cuja coluna
     SupplierID seja igual ao parâmetro @SupplierID.

use Northwind
go
create proc DetalhaFornecedor( 
@SupplierID int ) 
as 
select CompanyName ,
ContactName ,
ContactTitle,
Address ,
City ,
Region ,
PostalCode ,
Country ,
Phone ,
Fax ,
HomePage
from Suppliers
where SupplierID = @SupplierID
go
grant execute on DetalhaFornecedor to public
go


4 - Stored Procedure: i_suppliers_000 - Inclui dados recebidos como parâmetro na tabela Suppliers.

use Northwind
go
create proc i_suppliers_000(
@CompanyName nvarchar(80) ,
@ContactName nvarchar(60) ,
@ContactTitle nvarchar(60) ,
@Address nvarchar(120) ,
@City nvarchar(30) ,
@Region nvarchar(30) ,
@PostalCode nvarchar(20) ,
@Country nvarchar(30) ,
@Phone nvarchar(48) ,
@Fax nvarchar(48) ,
@HomePage ntext )
as 
insert Suppliers( 
CompanyName ,
ContactName ,
ContactTitle ,
Address ,
City ,
Region ,
PostalCode ,
Country ,
Phone ,
Fax ,
HomePage )
values(
@CompanyName ,
@ContactName ,
@ContactTitle ,
@Address ,
@City ,
@Region ,
@PostalCode ,
@Country ,
@Phone ,
@Fax ,
@HomePage )
go
grant execute on i_suppliers_000 to public
go


5 - Stored Procedure: s_suppliers_000 - Seleciona os dados da tabela Suppliers, cuja coluna SuppliersID,
     seja igual ao parâmetro @SupplierID.

use Northwind
go
create proc s_suppliers_000(
@SupplierID int )
as 
select CompanyName ,
ContactName ,
ContactTitle ,
Address ,
City ,
Region ,
PostalCode ,
Country ,
Phone ,
Fax ,
HomePage
from Suppliers
where SupplierID = @SupplierID
go
grant execute on s_suppliers_000 to public
go


6 - Stored Procedure: u_suppliers_000 - Efetura alteração na tabela Suppliers,  cuja coluna SupplierID
    seja igual ao parâmetro @SupplierID.

use Northwind
go
create proc u_suppliers_000 (
@SupplierID int ,
@CompanyName nvarchar(80) ,
@ContactName nvarchar(60) ,
@ContactTitle nvarchar(60) ,
@Address nvarchar(120) ,
@City nvarchar(30) ,
@Region nvarchar(30) ,
@PostalCode nvarchar(20) ,
@Country nvarchar(30) ,
@Phone nvarchar(48) ,
@Fax nvarchar(48) ,
@HomePage ntext )
as 
update Suppliers
set CompanyName = @CompanyName ,
ContactName = @ContactName ,
ContactTitle = @ContactTitle ,
Address = @Address ,
City = @City ,
Region = @Region ,
PostalCode = @PostalCode ,
Country = @Country ,
Phone = @Phone ,
Fax = @Fax ,
HomePage = @HomePage
where SupplierID = @SupplierID
go
grant execute on u_suppliers_000 to public
go

[Volta]   [Início]


Página desenvolvida por
João Carlos Lauriano.
Atualizada em: 04/06/2000