SQL Server - Funciones y Procedimientos

CREATE TABLE tblArticulo

(idArti CHAR(4) PRIMARY KEY,

DescripArti VARCHAR(25),

stockArti DECIMAL(2,0),

claseArti VARCHAR(20),

almaArti VARCHAR(1),

precioUArti DECIMAL(7,2));

CREATE TABLE tblCliente

(idClien CHAR(3) PRIMARY KEY,

NombreClien  VARCHAR(35),

direcClien VARCHAR(35),

CiudadClien VARCHAR(20),

balanClien DECIMAL(8,2),

limiCreClien DECIMAL(8,2),

idVende CHAR(2),

FOREIGN KEY (idVende) REFERENCES tblVendedor(idVende));

INSERT INTO tblArticulo

(idArti,descripArti,stockArti, claseArti, almaArti, precioUArti)

VALUES

('AT94','Iron', 50,'HW','3',24.95),

('BV06','Home Gym',45,'SG','2',794.95),

('CD52','Microwave', 32,'AP','1', 165.00),

('DL71','Cordless Drill', 21,'HW', '3', 129.95),

('DR93','Gas Rang',8,'AP', '2',495.00),

('DW11', 'Washer',12,'AP','3', 399.99),

('FD21','Stand Mixer',22,'HW','3',159.95),

('KL62','Dryer', 12, 'AP','1',349.95),

('KT03', 'Dishwasher', 8,'AP', '3', 595.00),

('KV29', 'Treadmill', 9,'SG', '2',1390.00);

INSERT INTO tblCliente

(idClien, NombreClien, direcClien, CiudadClien, balanClien, limiCreClien, idVende)

 VALUES

('148','Als Apppliance and Sport','2837 Greenway','Fillmore',6550.00, 7500.00 , '20'),

('282','Brookings Direct','3827 Devon','Grove',431.50, 10000.00 , '35'),

('356','Fergurson’s','382 Wildwood','Northfield',5785.00, 7500.00,'65'),

('408','The Everything Shop','1828 Raven','Crystal', 5285.25 , 5000.00, '35'),

('462','Bargains Galore','3829 Central','Grove', 3412.00 , 10000.00 , '65'),

('524','Kline’s','838 Ridgeland','Fillmore',12762.00 , 15000.00 ,'20'),

('608','Jhonson’s Department Store','372 Oxford','Sheldon',2106.00 , 10000.00 ,'65'),

('687','Lee’s Sport and Appliance','282 Evergreen','Atonville',2851.00 , 5000.00 , '35'),

('725','Deerfield’s Four Seasons','282 Columbia','Sheldon',248.00 , 7500.00 , '35'),

('842','All Season','28 Lakeview','Grove',8221.00,7500.00, '20');

CREATE PROCEDURE ObtenerNombre(

    @idClien int)

AS

BEGIN

    SELECT NombreClien

    FROM tblCliente

    WHERE idClien=@idClien

END

CREATE FUNCTION Nombre (@idClien CHAR(3))

RETURNS @cliente TABLE

(NombreClien VARCHAR(50))

AS

BEGIN

INSERT @cliente SELECT NombreClien

    FROM tblCliente

    WHERE idClien=@idClien

RETURN

END

select * from dbo.Nombre('148')

select * from dbo.Nombre('282')

select * from dbo.Nombre('356')

select * from dbo.Nombre('408')

select * from dbo.Nombre('462')