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
Página desenvolvida por João Carlos
Lauriano.
Atualizada em: 04/06/2000