0

In the stored procedure shown below, I have five cursors with which I scroll data in several tables to select the information I need while I go through one of the tables, using as parameters of selection various columns of the initial query shown in the first cursor.

At the end I execute a query with the result of all the cursors. This final query shows the result split in tables divided by the code of the record identified as Id. Although the records belong to the same entity the procedure shows it divided into tables and what I need is for the impression to be shown as a single table to be able To pass the result to an asp.net GridView with C#. The procedure is created in SQL Server 2012. I await your comments and thank you.

ALTER PROC [dbo].[sp_PresIng]
    @IdEnt INT
AS
   SET NOCOUNT ON

DECLARE mi_cursor CURSOR  FOR
    SELECT p.IdLinea, p.IdEntidad, n.Capitulo AS Cap, n.Nombre, p.Anio, p.IdIngreso, c.Tipo, c.ConceptoObj, c.Cuenta, c.SubCuenta, 
           c.Auxiliar, c.Descripcion DescAux, p.IdOtorgante, ISNULL(e.Nombre, '') AS Otorg, ISNULL(e.Capitulo, '') AS CapOtor, p.IdFuente, 
           f.Fuente, p.IdEspecifica, t.FuenteEspecifica AS ClaEspecifica, t.Descripcion AS Espec, p.IdOrgFin, o.Descripcion AS OrgFin, 
           o.OrganismoFin AS ClaOrgFin, p.PresOriginal AS Original, p.EjecucionEst AS Ejecutado, p.Proyectado
    FROM dbo.PRE_Pres_Ingresos AS p 
    LEFT OUTER JOIN dbo.PRE_Cla_Ing_Gas_Fin AS c ON p.IdIngreso = c.IdLinea 
    LEFT OUTER JOIN dbo.PRE_Cla_FuenteFinanciamiento AS f ON p.IdFuente = f.IdLinea 
    LEFT OUTER JOIN dbo.PRE_Cla_FuenteFinanciamiento AS t ON t.IdLinea = p.IdEspecifica 
    LEFT OUTER JOIN dbo.PRE_Cla_OrgFinanciadores AS o ON p.IdOrgFin = o.IdLinea 
    LEFT OUTER JOIN Entidades.dbo.ENT_TbT_Entidades AS e ON p.IdOtorgante = e.IdEntidad 
    LEFT OUTER JOIN Entidades.dbo.ENT_TbT_Entidades AS n ON p.IdEntidad = n.IdEntidad
    WHERE p.IdEntidad = @IdEnt
OPEN mi_cursor
DECLARE @IdLinea int, @IdEntidad int, @Capitulo char(4), @Nombre varchar(250), @Anio char(4), @ing int, @Tipo char(1), 
        @Concepto char(1), @Cuenta char(1), @Subcuenta char(1), @Auxiliar char(2), @DescAux varchar(250), @IdOtorgante int, 
        @Otorg varchar(250), @CapOtor char(4), @IdFuente int, @Fuente char(2), @IdEspecifica int, @ClaEspecifica char(4), 
        @Espec varchar(250), @IdOrgFin int, @OrgFin varchar(250), @ClaOrgFin char(4), @Original decimal(18,2), @Ejecutado decimal(18,2), 
        @Proyectado decimal(18,2)

FETCH NEXT FROM mi_cursor INTO @IdLinea, @IdEntidad, @Capitulo, @Nombre, @Anio, @ing, @Tipo, @Concepto, @Cuenta, @Subcuenta, 
                               @Auxiliar, @DescAux, @IdOtorgante, @Otorg, @CapOtor, @IdFuente, @Fuente, @IdEspecifica, @ClaEspecifica, 
                               @Espec, @IdOrgFin, @OrgFin, @ClaOrgFin, @Original, @Ejecutado, @Proyectado

WHILE @@FETCH_STATUS = 0
BEGIN   
    DECLARE cTipo CURSOR FOR
        SELECT Tipo, Descripcion FROM PRE_Cla_Ing_Gas_Fin WHERE Tipo = @Tipo AND ConceptoObj=''
        OPEN cTipo
        DECLARE @Tipo2 char(1), @desc varchar(200)
        FETCH NEXT FROM cTipo INTO @Tipo2, @desc
        WHILE @@FETCH_STATUS=0
        BEGIN
            FETCH NEXT FROM cTipo INTO @Tipo2, @desc
        END 
    CLOSE cTipo
    DEALLOCATE cTipo

DECLARE cConcepto CURSOR FOR
    SELECT ConceptoObj, Descripcion FROM PRE_Cla_Ing_Gas_Fin WHERE Tipo = @Tipo AND ConceptoObj=@Concepto AND Cuenta=''
    OPEN cConcepto
    DECLARE @Concepto2 char(1), @desc2 varchar(200)
    FETCH NEXT FROM cConcepto INTO @Concepto2, @desc2
    WHILE @@FETCH_STATUS=0
    BEGIN
        FETCH NEXT FROM cConcepto INTO @Concepto2, @desc2
    END 
CLOSE cConcepto
DEALLOCATE cConcepto

    DECLARE cCuenta CURSOR FOR
    SELECT Cuenta, Descripcion FROM PRE_Cla_Ing_Gas_Fin WHERE Tipo = @Tipo AND ConceptoObj=@Concepto AND Cuenta=@Cuenta AND SubCuenta=''
    OPEN cCuenta
    DECLARE @Cuenta2 char(1), @desc3 varchar(200)
    FETCH NEXT FROM cCuenta INTO @Cuenta2, @desc3
    WHILE @@FETCH_STATUS=0
    BEGIN
        FETCH NEXT FROM cCuenta INTO @Cuenta2, @desc3
    END 
CLOSE cCuenta
DEALLOCATE cCuenta
DECLARE cSubcuenta CURSOR FOR
    SELECT SubCuenta, Descripcion FROM PRE_Cla_Ing_Gas_Fin 
        WHERE Tipo = @Tipo AND ConceptoObj=@Concepto AND Cuenta=@Cuenta AND SubCuenta=@SubCuenta AND Auxiliar=''
    OPEN cSubcuenta
    DECLARE @SubCuenta2 char(1), @desc4 varchar(200)
    FETCH NEXT FROM cSubcuenta INTO @SubCuenta2, @desc4
    WHILE @@FETCH_STATUS=0
    BEGIN
        FETCH NEXT FROM cSubcuenta INTO @SubCuenta2, @desc4
    END 
CLOSE cSubcuenta
DEALLOCATE cSubcuenta

FETCH NEXT FROM mi_cursor INTO @IdLinea, @IdEntidad, @Capitulo, @Nombre, @Anio, @ing, @Tipo, @Concepto, @Cuenta, @Subcuenta, @Auxiliar, 
                @DescAux, @IdOtorgante, @Otorg, @CapOtor, @IdFuente, @Fuente, @IdEspecifica, @ClaEspecifica, @Espec, @IdOrgFin, @OrgFin, 
                @ClaOrgFin, @Original, @Ejecutado, @Proyectado

    SELECT @IdLinea Id, @IdEntidad IdEntidad, @Capitulo Capitulo, @Nombre Entidad, @Anio Anio, @ing IdIngreso, @Tipo Tipo, @Concepto Concepto, 
        @Cuenta Cuenta, @Subcuenta Subcuenta, @Auxiliar Auxiliar, @DescAux DescCuentaAux, @IdOtorgante IdOtorgante, @Otorg DesOtor, @CapOtor CapOtor,
        @IdFuente IdFuente, @Fuente CapFuente, @IdEspecifica IdEspe, @ClaEspecifica ClaEsp, @Espec DesEspe, @IdOrgFin IdOrgFin, @OrgFin DesOrgFin, 
        @ClaOrgFin ClaOrgFin, @Original Original, @Ejecutado Ejecutado, @Proyectado Proyectado, @Tipo2 TIPO2, @desc DescTIPO2, @Concepto2 CONCEP2, 
        @desc2 DescCONCEP2, @Cuenta2 CUENTA2, @desc3 DescCUENTA2, @SubCuenta2 SUB2, @desc4 DESCSUB2
END
CLOSE mi_cursor
DEALLOCATE mi_cursor[enter image description here][1]
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Edison
  • 1
  • 3
  • 1
    Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Jul 25 '17 at 17:24
  • Technical jargon: " shows the result split in tables divided by the code" - these are not tables, these are called record sets. To return all of your results as a single recordset you need to insert them into a temporary table. See this: https://stackoverflow.com/questions/11778952/inserting-data-into-a-temporary-table – Alex Jul 25 '17 at 22:12
  • Thanks Alex. I resolve my problem with your recommendation. – Edison Jul 26 '17 at 21:48

0 Answers0