mrh_peru
05-12-2006, 01:28:38
Foro como migrara mis procedimientos almacenados hechos en interbase a ms sql server 2000 y a la vez como llamarlos y extraer los paramtrso de salida desde delphi.
1. Procedeimiento Interbase q me devuelve la fecha del server
CREATE PROCEDURE SIS_FECHA_SERVER
RETURNS (
FECHA_SERVER DATE)
AS
BEGIN
/* Procedure body */
Select CURRENT_DATE From rdb$Database Into :Fecha_Server;
END
2. Procedeimiento Interbase q me devuelve la hora del server
CREATE PROCEDURE SIS_HORA_SERVER
RETURNS (
HORA_SERVER TIME)
AS
BEGIN
SELECT CAST('now' AS TIME) FROM RDB$DATABASE Into :Hora_Server;
/*
SELECT CURRENT_TIME FROM RDB$DATABASE Into :Hora_Server;
Select CURRENT_TIME From rdb$Database Into :Hora_Server;
*/
END
3. Prodemimto que procesa mi stock
CREATE PROCEDURE SP_AL_ACTUALIZAR_STOCK (
PCOD_AGE CHAR(3),
PCOD_ALM CHAR(3),
PCOD_ART CHAR(8),
PSUMA CHAR(1),
PCANTIDAD NUMERIC(8,2),
PSERIE CHAR(20))
RETURNS (
ROK CHAR(1))
AS
DECLARE VARIABLE VSTOCK NUMERIC(8,2);
DECLARE VARIABLE VSERIE CHAR(1);
begin
VSTOCK = 0 ;
ROK = 'S' ;
EXECUTE PROCEDURE SP_AL_VER_STOCK(:PCOD_AGE,:PCOD_ALM,:PCOD_ART)
RETURNING_VALUES (:VSTOCK);
IF ( :PSUMA = 'S' ) THEN
BEGIN
IF (:VSTOCK = -1) THEN
BEGIN
VSTOCK = :PCANTIDAD ;
INSERT INTO AL_STOCK
(COD_AGE,COD_ALM,COD_ART,STOCK)
VALUES
(:PCOD_AGE,:PCOD_ALM,:PCOD_ART,:VSTOCK) ;
END
ELSE
BEGIN
VSTOCK = :VSTOCK + :PCANTIDAD ;
UPDATE AL_STOCK STK
SET STK.STOCK = :VSTOCK
WHERE STK.cod_age = :PCOD_AGE AND STK.cod_alm = :PCOD_ALM AND STK.cod_art = :pcod_art ;
END
/* SERIES */
vserie='N' ;
SELECT ART.SERIE
FROM al_articulos ART
WHERE ART.cod_age = :PCOD_AGE AND ART.cod_art = :pcod_art
INTO :VSERIE ;
if (:vserie='S') then
begin
INSERT INTO AL_STOCK_SERIE
(COD_AGE,COD_ALM,COD_ART,STOCK,SERIE)
VALUES
(:PCOD_AGE,:PCOD_ALM,:PCOD_ART,1,:PSERIE) ;
end
END
IF ( :PSUMA = 'N' ) THEN
BEGIN
IF (:VSTOCK >= :PCANTIDAD ) THEN
BEGIN
IF (:VSTOCK > 0) THEN
BEGIN
VSTOCK = :VSTOCK - :PCANTIDAD ;
UPDATE AL_STOCK STK
SET STK.STOCK = :VSTOCK
WHERE STK.cod_age = :PCOD_AGE AND STK.cod_alm = :PCOD_ALM AND STK.cod_art = :pcod_art ;
/* SERIES */
vserie='N' ;
SELECT ART.SERIE
FROM al_articulos ART
WHERE ART.cod_age = :PCOD_AGE AND ART.cod_art = :pcod_art
INTO :VSERIE ;
if (:vserie='S') then
begin
UPDATE AL_STOCK_SERIE STS
SET STS.stock = STS.stock - :PCANTIDAD
WHERE STS.cod_age = :PCOD_AGE AND
STS.cod_alm = :PCOD_ALM AND
STS.cod_art = :pcod_art AND
STS.SERIE = :pserie ;
end
END
ELSE
BEGIN
ROK = 'N' ;
END
END
ELSE
BEGIN
ROK = 'N' ;
END
END
end
4.- Procemimiento que me devuelve mi stock
CREATE PROCEDURE SP_AL_VER_STOCK (
PCOD_AGE CHAR(3),
PCOD_ALM CHAR(3),
PCOD_ART CHAR(8))
RETURNS (
RSTOCK NUMERIC(8,2))
AS
begin
SELECT STK.stock
FROM AL_STOCK STK
WHERE STK.cod_age = :PCOD_AGE AND STK.cod_alm = :PCOD_ALM AND STK.cod_art = :PCOD_ART
INTO :RSTOCK ;
IF (:RSTOCK IS NULL ) THEN
BEGIN
RSTOCK = -1 ;
END
end
Por favot si no fuera mucha molestia, a la como los llamaria y recuperaria los valores de retorno.
Gracias por su atencion....
1. Procedeimiento Interbase q me devuelve la fecha del server
CREATE PROCEDURE SIS_FECHA_SERVER
RETURNS (
FECHA_SERVER DATE)
AS
BEGIN
/* Procedure body */
Select CURRENT_DATE From rdb$Database Into :Fecha_Server;
END
2. Procedeimiento Interbase q me devuelve la hora del server
CREATE PROCEDURE SIS_HORA_SERVER
RETURNS (
HORA_SERVER TIME)
AS
BEGIN
SELECT CAST('now' AS TIME) FROM RDB$DATABASE Into :Hora_Server;
/*
SELECT CURRENT_TIME FROM RDB$DATABASE Into :Hora_Server;
Select CURRENT_TIME From rdb$Database Into :Hora_Server;
*/
END
3. Prodemimto que procesa mi stock
CREATE PROCEDURE SP_AL_ACTUALIZAR_STOCK (
PCOD_AGE CHAR(3),
PCOD_ALM CHAR(3),
PCOD_ART CHAR(8),
PSUMA CHAR(1),
PCANTIDAD NUMERIC(8,2),
PSERIE CHAR(20))
RETURNS (
ROK CHAR(1))
AS
DECLARE VARIABLE VSTOCK NUMERIC(8,2);
DECLARE VARIABLE VSERIE CHAR(1);
begin
VSTOCK = 0 ;
ROK = 'S' ;
EXECUTE PROCEDURE SP_AL_VER_STOCK(:PCOD_AGE,:PCOD_ALM,:PCOD_ART)
RETURNING_VALUES (:VSTOCK);
IF ( :PSUMA = 'S' ) THEN
BEGIN
IF (:VSTOCK = -1) THEN
BEGIN
VSTOCK = :PCANTIDAD ;
INSERT INTO AL_STOCK
(COD_AGE,COD_ALM,COD_ART,STOCK)
VALUES
(:PCOD_AGE,:PCOD_ALM,:PCOD_ART,:VSTOCK) ;
END
ELSE
BEGIN
VSTOCK = :VSTOCK + :PCANTIDAD ;
UPDATE AL_STOCK STK
SET STK.STOCK = :VSTOCK
WHERE STK.cod_age = :PCOD_AGE AND STK.cod_alm = :PCOD_ALM AND STK.cod_art = :pcod_art ;
END
/* SERIES */
vserie='N' ;
SELECT ART.SERIE
FROM al_articulos ART
WHERE ART.cod_age = :PCOD_AGE AND ART.cod_art = :pcod_art
INTO :VSERIE ;
if (:vserie='S') then
begin
INSERT INTO AL_STOCK_SERIE
(COD_AGE,COD_ALM,COD_ART,STOCK,SERIE)
VALUES
(:PCOD_AGE,:PCOD_ALM,:PCOD_ART,1,:PSERIE) ;
end
END
IF ( :PSUMA = 'N' ) THEN
BEGIN
IF (:VSTOCK >= :PCANTIDAD ) THEN
BEGIN
IF (:VSTOCK > 0) THEN
BEGIN
VSTOCK = :VSTOCK - :PCANTIDAD ;
UPDATE AL_STOCK STK
SET STK.STOCK = :VSTOCK
WHERE STK.cod_age = :PCOD_AGE AND STK.cod_alm = :PCOD_ALM AND STK.cod_art = :pcod_art ;
/* SERIES */
vserie='N' ;
SELECT ART.SERIE
FROM al_articulos ART
WHERE ART.cod_age = :PCOD_AGE AND ART.cod_art = :pcod_art
INTO :VSERIE ;
if (:vserie='S') then
begin
UPDATE AL_STOCK_SERIE STS
SET STS.stock = STS.stock - :PCANTIDAD
WHERE STS.cod_age = :PCOD_AGE AND
STS.cod_alm = :PCOD_ALM AND
STS.cod_art = :pcod_art AND
STS.SERIE = :pserie ;
end
END
ELSE
BEGIN
ROK = 'N' ;
END
END
ELSE
BEGIN
ROK = 'N' ;
END
END
end
4.- Procemimiento que me devuelve mi stock
CREATE PROCEDURE SP_AL_VER_STOCK (
PCOD_AGE CHAR(3),
PCOD_ALM CHAR(3),
PCOD_ART CHAR(8))
RETURNS (
RSTOCK NUMERIC(8,2))
AS
begin
SELECT STK.stock
FROM AL_STOCK STK
WHERE STK.cod_age = :PCOD_AGE AND STK.cod_alm = :PCOD_ALM AND STK.cod_art = :PCOD_ART
INTO :RSTOCK ;
IF (:RSTOCK IS NULL ) THEN
BEGIN
RSTOCK = -1 ;
END
end
Por favot si no fuera mucha molestia, a la como los llamaria y recuperaria los valores de retorno.
Gracias por su atencion....