Algunas veces necesitamos en un store procedure recorrer un conjunto de resultados, vamos a usar tablas temporales para este ejemplo. / Sometimes we need to iterate over a resultset, let’s use some temporary tables in this example:
create table #tmpartic(codigo varchar(10), nombre varchar(50), price int)
insert into #tmpartic values('001', 'KEYBOARD / TECLADO', 70)
insert into #tmpartic values('002', 'MOUSE / RATON', 90)
insert into #tmpartic values('003', 'PEN / LAPICERO', 45)
insert into #tmpartic values('004', 'DISPLAY / MONITOR', 150)
insert into #tmpartic values('005', 'HARD DISK / DISCO DURO', 200)
create table #tmpcli(client varchar(10), City varchar(30), limit int)
insert into #tmpartic values('5551010', 'Bogota', 100)
insert into #tmpartic values('5156225', 'Bangladesh', 300)
insert into #tmpartic values('6859852', 'Pekin', 500)
insert into #tmpartic values('7851215', 'Tokio', 500)
insert into #tmpartic values('8989822', 'Sidney', 350)
create table #tmpped(client varchar(10), article varchar(10), price int)
Ahora necesitamos crear registros en la tabla tmpcli para cada cliente todos los artículos mientras no se pase de su límite. /
Now we need to create records in tmpcli table for each client every article without being over limit.
Para esto creamos 2 tablas temporales #tmpartic con cada artículo y su precio y #tmpcli con cada cliente y su límite. El ciclo principal es sobre la tabla temporal de clientes entonces obtenemos su límite y mientras tenga límite disponible agregamos un registro en la tabla #tmpped, por cada registro agregado vamos disminuyendo el precio del artículo para que no se vaya a pasar. El ciclo while se controla mediante la columna kctrl que empieza en null y tras cada iteración cambia a 1 así ya no se procesa más. Para los artículos se debe volver a actualizar en null cuando se cambia de cliente para garantizar que se procesan de nuevo todos los artículos para cada cliente. /
For this we create 2 temporary tables #tmpartic with each article and price y #tmpcli for each client and limit. Main cycle runs on clients temporary table, we read his limit and while limit is available we add a new record in #tmpped, for each record added we subtract the price of the article preventing to add more than expected. The cycle is controled with the column kctrl that starts in null and after every iteration it changes to 1 that way is no longer used. For the articles this column must be updated to null when client is changed to warranty all the articles are processed for each client.
DECLARE @nLIMIT int, @nRECNUM int, @cCLI varchar(10);
DECLARE @nRECNP int, @cART varchar(10), @nPRIC int;
SELECT null kctrl, codigo, price, ROW_NUMBER() OVER(ORDER BY codigo) as recnum into #selpro FROM #tmpartic;
SELECT null kctrl, client, limit, ROW_NUMBER() OVER(ORDER BY limit) as recnum into #selcli FROM #tmpcli;
WHILE (SELECT count(*) FROM #selcli WHERE isnull(kctrl, 0) = 0) > 0
BEGIN
SET @nRECNUM=(SELECT MIN(recnum) FROM #selcli WHERE ISNULL(kctrl, 0) = 0)
SELECT @nLIMIT=limit, @cCLI=client FROM #selcli WHERE recnum=@nRECNUM
UPDATE #selpro SET kctrl=null
WHILE (SELECT count(*) FROM #selpro WHERE isnull(kctrl, 0) = 0) > 0
BEGIN
SET @nRECNP=(SELECT MIN(recnum) FROM #selpro WHERE ISNULL(kctrl, 0) = 0)
SELECT @cART=codigo, @nPRIC=price FROM #selpro WHERE recnum=@nRECNP
IF @nLIMIT>=@nPRIC
BEGIN
INSERT INTO #tmpped(client, article, price) VALUES(@cCLI, @cART,@nPRIC)
SET @nLIMIT=@nLIMIT-@nPRIC
END
ELSE BREAK
UPDATE #selpro SET kctrl=1 WHERE recnum=@nRECNP
END
UPDATE #selcli SET kctrl=1 WHERE recnum=@nRECNUM
END
Ahora vamos a validar / let’s now validate:
SELECT * from #selcli
SELECT * from #selpro
SELECT * from #tmpped
Verificación de resultados / Verification of results
| Client | $Limit | $Total Peds | ok? |
|---|---|---|---|
| 5551010 | 100 | 70 | Yes |
| 5156225 | 300 | 205 | Yes |
| 8989822 | 350 | 205 | Yes |
| 6859852 | 500 | 355 | Yes |
| 7851215 | 500 | 355 | Yes |
Ahora usted podría cambiar los límites en los clientes y reiniciar el proceso para validar contra diferentes valores /
Now you can change the limits in client and restart the process to verify against different values.
Para borrar todas las tablas / to delete all the tables:
DROP TABLE #tmpartic DROP TABLE #tmpcli DROP TABLE #tmpped DROP TABLE #selpro DROP TABLE #selcli
Preguntas? / Questions?
John Harold Belalcazar Lozano 