Ayuda con este procedimiento. Por favor!!!
Publicado por rojobad (1 intervención) el 07/06/2009 14:56:11
Hola. Soy nuevo en este foro, aunque lo he estado revisando y parece muy bueno. Tengo un problema. Estoy estudiando Ing. de Sistemas, voy terminando el 5to Semestre y aún no he llevado Base de Datos.
Sin embargo tengo que hacer un proyecto, para estructuras de datos, es sobre seguros, y un amigo me pasó un programa similar que él había hecho, para que me sirviera de guia. Lo he estado mirando, pero en un momento hace referencia a este procedimiento, y como ya dije, no se mucho de Base de Datos, He estado investigando y tengo una idea general de qué hace un JOIN y un SELECT....FROM.....WHERE, sin embargo no tengo NI IDEA de cómo funcian un procedimiento.
Si alguien Pudiera explicarme como para un niño, jejeje, qué hace este procedimiento, estaría muy agradecido. Sino al menos necesitaría que me explicaran qué hacen las siguientes líneas:
1.-
INSERT INTO #TEMP
SELECT cli.nTarget, (SELECT CASE ph.nType_hist
WHEN 31 THEN "ACTIVACION AUTOMATICA "
WHEN 29 THEN "BLOQUEO AUTOMATICO MORA"
WHEN 30 THEN "BLOQUEO ANULACION CERTIF"
END ) DES,
(SELECT CASE ph.nType_hist
WHEN 31 THEN "A"
WHEN 29 THEN "B"
WHEN 30 THEN "B"
END ) obs,
ph.dEffecdate,r.sClient ,r.npolicy, r.ncertif, r.nRole, ph.dCompdate
FROM insudb.policy_his ph (NOLOCK)
2.-
DECLARE CC CURSOR FOR
SELECT nTarget , sDescript, sType, dEffecdate ,sClient , nPolicy,
nCertif,nRole
FROM #TEMP
ORDER BY dcompdate
FOR READ ONLY
OPEN CC
FETCH NEXT FROM CC INTO @nTarget, @sDescript, @sState, @dEffecdat, @sclient, @nPolicy, @nCertif, @nRole
WHILE @@FETCH_STATUS > - 1
BEGIN
...
END
3.-
SELECT @sCard = CONVERT(varchar(16), ISNULL( @nTarget,0))
SELECT @sCard =
CASE LEN(@sCard)
WHEN 1 THEN "000000000000000" + @sCard
WHEN 2 THEN "00000000000000" + @sCard
WHEN 3 THEN "0000000000000" + @sCard
WHEN 4 THEN "000000000000" + @sCard
WHEN 5 THEN "00000000000" + @sCard
WHEN 6 THEN "0000000000" + @sCard
WHEN 7 THEN "000000000" + @sCard
WHEN 8 THEN "00000000" + @sCard
WHEN 9 THEN "0000000" + @sCard
WHEN 10 THEN "000000" + @sCard
WHEN 11 THEN "00000" + @sCard
WHEN 12 THEN "0000" + @sCard
WHEN 13 THEN "000" + @sCard
WHEN 14 THEN "00" + @sCard
WHEN 15 THEN "0" + @sCard
WHEN 16 THEN @sCard
4.-
FETCH NEXT FROM CC INTO @nTarget, @sDescript, @sState, @dEffecdat, @sClient, @nPolicy, @nCertif, @nRole
Esas son mis principales dudas, pero como ya dije, si alguien me puede explicar qué hace todo el procedimiento, Estaría mucho mejor, igual se agradece cualquier ayuda que me puedan dar. Gracias.
Aquí esta el procedimiento completo:
sp_helptext 'insudb.targetblock_active'
-- EXEC insudb.targetblock_active '20071026','20071026',1
CREATE PROCEDURE insudb.targetblock_active @dBegin VARCHAR(10),
@dEnd VARCHAR(10),
@nOffice INTEGER AS
DECLARE @nTarget NUMERIC,
@sDescript VARCHAR(24),
@sState VARCHAR(1),
@dEffecdat DATETIME,
@sCadena VARCHAR(500),
@sClient VARCHAR(14),
@sClientcont VARCHAR(14),
@sClienamecont VARCHAR(60),
@nPolicy INTEGER,
@nCertif INTEGER,
@nRole INTEGER,
@sCertif VARCHAR(4),
@sRole VARCHAR(4),
@sCard VARCHAR(16)
SET NOCOUNT ON
DECLARE @dEndate DATETIME
SET @dEndate = @dEnd + ' 23:59:59'
IF @nOffice <= 0
SET @nOffice = NULL
DECLARE @TEMP TABLE (sCadena VARCHAR(500), id INTEGER IDENTITY (1, 1) NOT NULL )
CREATE TABLE #TEMP (nTarget NUMERIC,
sDescript VARCHAR(35),
sType VARCHAR(1),
dEffecdate DATETIME,
sClient VARCHAR(14),
nPolicy INTEGER,
nCertif INTEGER,
nRole INTEGER,
dCompdate DATETIME )
INSERT INTO #TEMP
SELECT cli.nTarget, (SELECT CASE ph.nType_hist
WHEN 31 THEN "ACTIVACION AUTOMATICA "
WHEN 29 THEN "BLOQUEO AUTOMATICO MORA"
WHEN 30 THEN "BLOQUEO ANULACION CERTIF"
END ) DES,
(SELECT CASE ph.nType_hist
WHEN 31 THEN "A"
WHEN 29 THEN "B"
WHEN 30 THEN "B"
END ) obs,
ph.dEffecdate,r.sClient ,r.npolicy, r.ncertif, r.nRole, ph.dCompdate
FROM insudb.policy_his ph (NOLOCK)
INNER JOIN insudb.policy p (NOLOCK)
ON ph.sCertype = p.sCertype
AND ph.nBranch = p.nBranch
AND ph.nProduct = p.nProduct
AND ph.nPolicy = p.nPolicy
AND p.nOffice = @nOffice
INNER JOIN insudb.roles r (NOLOCK)
ON p.sCertype = r.sCertype
AND p.nBranch = r.nBranch
AND p.nProduct = r.nProduct
AND p.nPolicy = r.nPolicy
AND ph.nCertif = r.nCertif
AND r.nRole NOT IN (1,13,62)
INNER JOIN insudb.prodmaster pm (NOLOCK)
ON p.nBranch = pm.nBranch
AND p.nProduct = pm.nProduct
AND pm.sBranchT = "7"
INNER JOIN insudb.client cli (NOLOCK)
ON r.sclient = cli.sclient
WHERE ph.sCertype = "2"
AND ph.nBranch in (3,53,30)
AND ph.nType_hist IN(31,29,30)
AND ph.dCompdate BETWEEN @dBegin AND @dEndate
UNION ALL
SELECT cli.nTarget,"VENCIMIENTO DE POLIZA" DES,"B" obs,
p.dExpirdat,r.sClient ,r.npolicy, r.ncertif, r.nRole, 0
FROM insudb.policy p (NOLOCK)
INNER JOIN insudb.roles r (NOLOCK)
ON p.sCertype = r.sCertype
AND p.nBranch = r.nBranch
AND p.nProduct = r.nProduct
AND p.nPolicy = r.nPolicy
AND r.nRole NOT IN (1,13,62)
INNER JOIN insudb.prodmaster pm (NOLOCK)
ON p.nBranch = pm.nBranch
AND p.nProduct = pm.nProduct
AND pm.sBranchT = "7"
INNER JOIN insudb.client cli (NOLOCK)
ON r.sclient = cli.sclient
WHERE p.sCertype = "2"
AND p.nBranch in (3,53,30)
AND p.dExpirdat BETWEEN @dBegin AND @dEndate
AND p.nOffice = @nOffice
AND p.sPolitype = "1"
UNION ALL
SELECT cli.nTarget, "BLOQUEO ANULACION POLIZ", "B" obs,
ph.dEffecdate,r.sClient ,r.npolicy, r.ncertif, r.nRole, ph.dCompdate
FROM insudb.policy_his ph (NOLOCK)
INNER JOIN insudb.policy p (NOLOCK)
ON ph.sCertype = p.sCertype
AND ph.nBranch = p.nBranch
AND ph.nProduct = p.nProduct
AND ph.nPolicy = p.nPolicy
AND p.nOffice = @nOffice
AND p.sPolitype = '2'
INNER JOIN insudb.roles r (NOLOCK)
ON p.sCertype = r.sCertype
AND p.nBranch = r.nBranch
AND p.nProduct = r.nProduct
AND p.nPolicy = r.nPolicy
AND r.nRole NOT IN (1,13,62)
INNER JOIN insudb.prodmaster pm (NOLOCK)
ON p.nBranch = pm.nBranch
AND p.nProduct = pm.nProduct
AND pm.sBranchT = "7"
INNER JOIN insudb.client cli (NOLOCK)
ON r.sclient = cli.sclient
WHERE ph.sCertype = "2"
AND ph.nBranch in (3,53,30)
AND ph.nType_hist = 29
AND ph.dCompdate BETWEEN @dBegin AND @dEndate
DECLARE CC CURSOR FOR
SELECT nTarget , sDescript, sType, dEffecdate ,sClient , nPolicy,
nCertif,nRole
FROM #TEMP
ORDER BY dcompdate
FOR READ ONLY
OPEN CC
FETCH NEXT FROM CC INTO @nTarget, @sDescript, @sState, @dEffecdat, @sclient, @nPolicy, @nCertif, @nRole
WHILE @@FETCH_STATUS > - 1
BEGIN
SELECT @sClientcont = r.sClient,
@sClienamecont = ci.scliename
FROM insudb.roles r (NOLOCK)
INNER JOIN insudb.client ci (NOLOCK)
ON r.sclient = ci.sclient
WHERE r.nPolicy = @nPolicy
AND r.nCertif = 0
AND r.nRole = 1
SELECT @sCertif = CONVERT(varchar(4), @nCertif)
SELECT @sCertif =
CASE LEN(@sCertif)
WHEN 1 THEN "000" + @sCertif
WHEN 2 THEN "00" + @sCertif
WHEN 3 THEN "0" + @sCertif
END
SELECT @sRole = CONVERT(varchar(4), @nRole)
SELECT @sRole =
CASE LEN(@sRole)
WHEN 1 THEN "000" + @sRole
WHEN 2 THEN "00" + @sRole
WHEN 3 THEN "0" + @sRole
END
SELECT @sCard = CONVERT(varchar(16), ISNULL( @nTarget,0))
SELECT @sCard =
CASE LEN(@sCard)
WHEN 1 THEN "000000000000000" + @sCard
WHEN 2 THEN "00000000000000" + @sCard
WHEN 3 THEN "0000000000000" + @sCard
WHEN 4 THEN "000000000000" + @sCard
WHEN 5 THEN "00000000000" + @sCard
WHEN 6 THEN "0000000000" + @sCard
WHEN 7 THEN "000000000" + @sCard
WHEN 8 THEN "00000000" + @sCard
WHEN 9 THEN "0000000" + @sCard
WHEN 10 THEN "000000" + @sCard
WHEN 11 THEN "00000" + @sCard
WHEN 12 THEN "0000" + @sCard
WHEN 13 THEN "000" + @sCard
WHEN 14 THEN "00" + @sCard
WHEN 15 THEN "0" + @sCard
WHEN 16 THEN @sCard
END
SELECT @sCadena = @sCard+ "|" + @sDescript + "|" + @sState + "|" +
CONVERT(VARCHAR(8),@dEffecdat,112) + "|" + CONVERT(VARCHAR(8), @npolicy) + "|" + @sCertif + "|" + @sRole +
"|" + @sClient + "|" + @sClientcont + "|"
INSERT INTO @TEMP VALUES (@sCadena)
FETCH NEXT FROM CC INTO @nTarget, @sDescript, @sState, @dEffecdat, @sClient, @nPolicy, @nCertif, @nRole
END
CLOSE CC
DEALLOCATE CC
SELECT sCadena FROM @TEMP
ORDER BY id
SET NOCOUNT OFF
Sin embargo tengo que hacer un proyecto, para estructuras de datos, es sobre seguros, y un amigo me pasó un programa similar que él había hecho, para que me sirviera de guia. Lo he estado mirando, pero en un momento hace referencia a este procedimiento, y como ya dije, no se mucho de Base de Datos, He estado investigando y tengo una idea general de qué hace un JOIN y un SELECT....FROM.....WHERE, sin embargo no tengo NI IDEA de cómo funcian un procedimiento.
Si alguien Pudiera explicarme como para un niño, jejeje, qué hace este procedimiento, estaría muy agradecido. Sino al menos necesitaría que me explicaran qué hacen las siguientes líneas:
1.-
INSERT INTO #TEMP
SELECT cli.nTarget, (SELECT CASE ph.nType_hist
WHEN 31 THEN "ACTIVACION AUTOMATICA "
WHEN 29 THEN "BLOQUEO AUTOMATICO MORA"
WHEN 30 THEN "BLOQUEO ANULACION CERTIF"
END ) DES,
(SELECT CASE ph.nType_hist
WHEN 31 THEN "A"
WHEN 29 THEN "B"
WHEN 30 THEN "B"
END ) obs,
ph.dEffecdate,r.sClient ,r.npolicy, r.ncertif, r.nRole, ph.dCompdate
FROM insudb.policy_his ph (NOLOCK)
2.-
DECLARE CC CURSOR FOR
SELECT nTarget , sDescript, sType, dEffecdate ,sClient , nPolicy,
nCertif,nRole
FROM #TEMP
ORDER BY dcompdate
FOR READ ONLY
OPEN CC
FETCH NEXT FROM CC INTO @nTarget, @sDescript, @sState, @dEffecdat, @sclient, @nPolicy, @nCertif, @nRole
WHILE @@FETCH_STATUS > - 1
BEGIN
...
END
3.-
SELECT @sCard = CONVERT(varchar(16), ISNULL( @nTarget,0))
SELECT @sCard =
CASE LEN(@sCard)
WHEN 1 THEN "000000000000000" + @sCard
WHEN 2 THEN "00000000000000" + @sCard
WHEN 3 THEN "0000000000000" + @sCard
WHEN 4 THEN "000000000000" + @sCard
WHEN 5 THEN "00000000000" + @sCard
WHEN 6 THEN "0000000000" + @sCard
WHEN 7 THEN "000000000" + @sCard
WHEN 8 THEN "00000000" + @sCard
WHEN 9 THEN "0000000" + @sCard
WHEN 10 THEN "000000" + @sCard
WHEN 11 THEN "00000" + @sCard
WHEN 12 THEN "0000" + @sCard
WHEN 13 THEN "000" + @sCard
WHEN 14 THEN "00" + @sCard
WHEN 15 THEN "0" + @sCard
WHEN 16 THEN @sCard
4.-
FETCH NEXT FROM CC INTO @nTarget, @sDescript, @sState, @dEffecdat, @sClient, @nPolicy, @nCertif, @nRole
Esas son mis principales dudas, pero como ya dije, si alguien me puede explicar qué hace todo el procedimiento, Estaría mucho mejor, igual se agradece cualquier ayuda que me puedan dar. Gracias.
Aquí esta el procedimiento completo:
sp_helptext 'insudb.targetblock_active'
-- EXEC insudb.targetblock_active '20071026','20071026',1
CREATE PROCEDURE insudb.targetblock_active @dBegin VARCHAR(10),
@dEnd VARCHAR(10),
@nOffice INTEGER AS
DECLARE @nTarget NUMERIC,
@sDescript VARCHAR(24),
@sState VARCHAR(1),
@dEffecdat DATETIME,
@sCadena VARCHAR(500),
@sClient VARCHAR(14),
@sClientcont VARCHAR(14),
@sClienamecont VARCHAR(60),
@nPolicy INTEGER,
@nCertif INTEGER,
@nRole INTEGER,
@sCertif VARCHAR(4),
@sRole VARCHAR(4),
@sCard VARCHAR(16)
SET NOCOUNT ON
DECLARE @dEndate DATETIME
SET @dEndate = @dEnd + ' 23:59:59'
IF @nOffice <= 0
SET @nOffice = NULL
DECLARE @TEMP TABLE (sCadena VARCHAR(500), id INTEGER IDENTITY (1, 1) NOT NULL )
CREATE TABLE #TEMP (nTarget NUMERIC,
sDescript VARCHAR(35),
sType VARCHAR(1),
dEffecdate DATETIME,
sClient VARCHAR(14),
nPolicy INTEGER,
nCertif INTEGER,
nRole INTEGER,
dCompdate DATETIME )
INSERT INTO #TEMP
SELECT cli.nTarget, (SELECT CASE ph.nType_hist
WHEN 31 THEN "ACTIVACION AUTOMATICA "
WHEN 29 THEN "BLOQUEO AUTOMATICO MORA"
WHEN 30 THEN "BLOQUEO ANULACION CERTIF"
END ) DES,
(SELECT CASE ph.nType_hist
WHEN 31 THEN "A"
WHEN 29 THEN "B"
WHEN 30 THEN "B"
END ) obs,
ph.dEffecdate,r.sClient ,r.npolicy, r.ncertif, r.nRole, ph.dCompdate
FROM insudb.policy_his ph (NOLOCK)
INNER JOIN insudb.policy p (NOLOCK)
ON ph.sCertype = p.sCertype
AND ph.nBranch = p.nBranch
AND ph.nProduct = p.nProduct
AND ph.nPolicy = p.nPolicy
AND p.nOffice = @nOffice
INNER JOIN insudb.roles r (NOLOCK)
ON p.sCertype = r.sCertype
AND p.nBranch = r.nBranch
AND p.nProduct = r.nProduct
AND p.nPolicy = r.nPolicy
AND ph.nCertif = r.nCertif
AND r.nRole NOT IN (1,13,62)
INNER JOIN insudb.prodmaster pm (NOLOCK)
ON p.nBranch = pm.nBranch
AND p.nProduct = pm.nProduct
AND pm.sBranchT = "7"
INNER JOIN insudb.client cli (NOLOCK)
ON r.sclient = cli.sclient
WHERE ph.sCertype = "2"
AND ph.nBranch in (3,53,30)
AND ph.nType_hist IN(31,29,30)
AND ph.dCompdate BETWEEN @dBegin AND @dEndate
UNION ALL
SELECT cli.nTarget,"VENCIMIENTO DE POLIZA" DES,"B" obs,
p.dExpirdat,r.sClient ,r.npolicy, r.ncertif, r.nRole, 0
FROM insudb.policy p (NOLOCK)
INNER JOIN insudb.roles r (NOLOCK)
ON p.sCertype = r.sCertype
AND p.nBranch = r.nBranch
AND p.nProduct = r.nProduct
AND p.nPolicy = r.nPolicy
AND r.nRole NOT IN (1,13,62)
INNER JOIN insudb.prodmaster pm (NOLOCK)
ON p.nBranch = pm.nBranch
AND p.nProduct = pm.nProduct
AND pm.sBranchT = "7"
INNER JOIN insudb.client cli (NOLOCK)
ON r.sclient = cli.sclient
WHERE p.sCertype = "2"
AND p.nBranch in (3,53,30)
AND p.dExpirdat BETWEEN @dBegin AND @dEndate
AND p.nOffice = @nOffice
AND p.sPolitype = "1"
UNION ALL
SELECT cli.nTarget, "BLOQUEO ANULACION POLIZ", "B" obs,
ph.dEffecdate,r.sClient ,r.npolicy, r.ncertif, r.nRole, ph.dCompdate
FROM insudb.policy_his ph (NOLOCK)
INNER JOIN insudb.policy p (NOLOCK)
ON ph.sCertype = p.sCertype
AND ph.nBranch = p.nBranch
AND ph.nProduct = p.nProduct
AND ph.nPolicy = p.nPolicy
AND p.nOffice = @nOffice
AND p.sPolitype = '2'
INNER JOIN insudb.roles r (NOLOCK)
ON p.sCertype = r.sCertype
AND p.nBranch = r.nBranch
AND p.nProduct = r.nProduct
AND p.nPolicy = r.nPolicy
AND r.nRole NOT IN (1,13,62)
INNER JOIN insudb.prodmaster pm (NOLOCK)
ON p.nBranch = pm.nBranch
AND p.nProduct = pm.nProduct
AND pm.sBranchT = "7"
INNER JOIN insudb.client cli (NOLOCK)
ON r.sclient = cli.sclient
WHERE ph.sCertype = "2"
AND ph.nBranch in (3,53,30)
AND ph.nType_hist = 29
AND ph.dCompdate BETWEEN @dBegin AND @dEndate
DECLARE CC CURSOR FOR
SELECT nTarget , sDescript, sType, dEffecdate ,sClient , nPolicy,
nCertif,nRole
FROM #TEMP
ORDER BY dcompdate
FOR READ ONLY
OPEN CC
FETCH NEXT FROM CC INTO @nTarget, @sDescript, @sState, @dEffecdat, @sclient, @nPolicy, @nCertif, @nRole
WHILE @@FETCH_STATUS > - 1
BEGIN
SELECT @sClientcont = r.sClient,
@sClienamecont = ci.scliename
FROM insudb.roles r (NOLOCK)
INNER JOIN insudb.client ci (NOLOCK)
ON r.sclient = ci.sclient
WHERE r.nPolicy = @nPolicy
AND r.nCertif = 0
AND r.nRole = 1
SELECT @sCertif = CONVERT(varchar(4), @nCertif)
SELECT @sCertif =
CASE LEN(@sCertif)
WHEN 1 THEN "000" + @sCertif
WHEN 2 THEN "00" + @sCertif
WHEN 3 THEN "0" + @sCertif
END
SELECT @sRole = CONVERT(varchar(4), @nRole)
SELECT @sRole =
CASE LEN(@sRole)
WHEN 1 THEN "000" + @sRole
WHEN 2 THEN "00" + @sRole
WHEN 3 THEN "0" + @sRole
END
SELECT @sCard = CONVERT(varchar(16), ISNULL( @nTarget,0))
SELECT @sCard =
CASE LEN(@sCard)
WHEN 1 THEN "000000000000000" + @sCard
WHEN 2 THEN "00000000000000" + @sCard
WHEN 3 THEN "0000000000000" + @sCard
WHEN 4 THEN "000000000000" + @sCard
WHEN 5 THEN "00000000000" + @sCard
WHEN 6 THEN "0000000000" + @sCard
WHEN 7 THEN "000000000" + @sCard
WHEN 8 THEN "00000000" + @sCard
WHEN 9 THEN "0000000" + @sCard
WHEN 10 THEN "000000" + @sCard
WHEN 11 THEN "00000" + @sCard
WHEN 12 THEN "0000" + @sCard
WHEN 13 THEN "000" + @sCard
WHEN 14 THEN "00" + @sCard
WHEN 15 THEN "0" + @sCard
WHEN 16 THEN @sCard
END
SELECT @sCadena = @sCard+ "|" + @sDescript + "|" + @sState + "|" +
CONVERT(VARCHAR(8),@dEffecdat,112) + "|" + CONVERT(VARCHAR(8), @npolicy) + "|" + @sCertif + "|" + @sRole +
"|" + @sClient + "|" + @sClientcont + "|"
INSERT INTO @TEMP VALUES (@sCadena)
FETCH NEXT FROM CC INTO @nTarget, @sDescript, @sState, @dEffecdat, @sClient, @nPolicy, @nCertif, @nRole
END
CLOSE CC
DEALLOCATE CC
SELECT sCadena FROM @TEMP
ORDER BY id
SET NOCOUNT OFF
Valora esta pregunta
0