Bdtjtk

I need to be able to obtain the result set that will generate a stored procedure that has a dynamic query (sp_executesql), the problem is that when defining the DataSource, which in this case would be the stored procedure, it does not return any column as a result by the same from the DataSource wizard.

I have another report (rdlc) that the stored procedure that has a DataSource is not a dynamic query that returns the result set.

I was thinking about being able to capture the instruction that solves the sp_executesql in a variable (@instruction) and then take it and execute it again as Select from the query configuration wizard of TableAdapter.

ALTER PROCEDURE dbo.StoredProcedureA 

    @xmlParameter XML 

AS

BEGIN

    SET NOCOUNT ON 



    DECLARE @DespachoR  VARCHAR(100),

    @DespachoRBEsta TINYINT,

    @PaisOrigenR VARCHAR(100),

    @PaisOrigenBEsta TINYINT,

    @Estampilla INTEGER,

    @DesdeFecha VARCHAR(10),

    @HastaFecha VARCHAR(10),

    @desfecha datetime,

    @hasfecha datetime,

    @strWhereRan VARCHAR(MAX),

    @strWhere VARCHAR(MAX),





    @seteos nvarchar(200),

    @instruccion nvarchar(max),

    @instruccion2 nvarchar(max),

    @texto varchar(max),

    @columna varchar(100),

    @tipodato varchar(50)





    If @xmlParametros is not null

    begin



DECLARE @xml TABLE ( identidad integer IDENTITY (1,1) not null, tipo char(1) not null, columna varchar(30) not null, campoxml varchar(1000) not null, 

    tipodato varchar(50) not null, nulo tinyint null)



INSERT @xml (columna, tipo, campoxml, tipodato, nulo)





SELECT '@DespachoR', 'E', '(/Parameters/DESPACHOR/text())[1]', 'varchar(100)', null 

UNION ALL

SELECT '@DespachoRBEsta', 'E', '(/Parameters/DESPACHORBESTA/text())[1]', 'tinyint', null 

UNION ALL

SELECT '@PaisOrigenR', 'E', '(/Parameters/PAISORIGENR/text())[1]', 'varchar(100)', null 

UNION ALL

SELECT '@PaisOrigenBEsta', 'E', '(/Parameters/PAISORIGENRBESTA/text())[1]', 'tinyint', null 

UNION ALL

SELECT '@Estampilla', 'E', '(/Parameters/ESTAMPILLA/text())[1]', 'integer', null 

UNION ALL

SELECT '@DesdeFecha', 'E', '(/Parameters/DESDEFECHA/text())[1]', 'varchar(10)', null 

UNION ALL

SELECT '@HastaFecha', 'E', '(/Parameters/HASTAFECHA/text())[1]', 'varchar(10)', null 





SELECT @seteos = 'SET ANSI_NULLS ON

        SET QUOTED_IDENTIFIER ON

        SET CONCAT_NULL_YIELDS_NULL ON

        SET ANSI_PADDING ON

        SET ANSI_WARNINGS ON

        SET ARITHABORT ON

        SET NUMERIC_ROUNDABORT OFF '

select @instruccion =null, @instruccion2 = N'@xmlParametros xml'



DECLARE INSTRUCCION INSENSITIVE CURSOR FOR

SELECT columna, "@xmlParametros.value('" + campoxml + "', '" + tipodato + "')", tipodato

FROM @xml

WHERE tipo='E'

ORDER BY identidad  



OPEN INSTRUCCION

FETCH NEXT FROM INSTRUCCION INTO @columna, @texto, @tipodato



WHILE (@@FETCH_STATUS <> -1)

 begin

    If @instruccion is not null

        SELECT @instruccion  = @instruccion + ', '



    SELECT @instruccion  = @instruccion + @columna + '=' + @texto, @instruccion2 = @instruccion2 + ', ' + @columna + ' ' + @tipodato + ' OUTPUT'



    FETCH NEXT FROM INSTRUCCION INTO @columna, @texto, @tipodato

 end



CLOSE INSTRUCCION

DEALLOCATE INSTRUCCION



SELECT @instruccion = @seteos + N'SELECT ' + @instruccion 



--end

                    --@instruccion: SETEOS

exec sp_executesql  @instruccion, @instruccion2,@xmlParametros=@xmlParametros, @DespachoR=@DespachoR OUTPUT,

                    @DespachoRBEsta=@DespachoRBEsta OUTPUT, @PaisOrigenR=@PaisOrigenR OUTPUT, @PaisOrigenBEsta=@PaisOrigenBEsta OUTPUT, @Estampilla=@Estampilla OUTPUT,

                    @DesdeFecha=@DesdeFecha OUTPUT, @HastaFecha=@HastaFecha OUTPUT



IF @DespachoR is not null

 begin

    EXEC sp_armo_rango @DespachoR, 'D.Despacho', @DespachoRBEsta, 0, @strWhereRan OUTPUT



    IF @strWhereRan is not null

        Set @strWhere = @strWhere + " AND " + rtrim(@strWhereRan)





 end



IF @PaisOrigenR is not null

begin

     EXEC sp_armo_rango @PaisOrigenR, 'D.Paisorigen', @PaisOrigenBEsta, 0, @strWhereRan OUTPUT

     IF @strWhereRan is not null

          Set  @strWhere  =  @strWhere  + " AND " + rtrim(@strWhereRan)

end



IF @Estampilla is not null

     --Esto determina que se listen los despachos que tienen la estampilla indicada, los despachos así determinados se listan completos, 

     --es decir, con todos los rangos de estampillas.

     Set @strWhere = @strWhere + ' AND 0<>(SELECT count(*) FROM Despachosestampillas De1 (nolock)

                          WHERE     De1.Despacho=D.Despacho AND 

                               De1.Hasta >=@estampilla AND De1.Desde <= @estampilla )'



IF @DesdeFecha is not null

begin

    select @desfecha=convert(datetime, @desdefecha, 103)



     Set  @strWhere  =  @strWhere  + ' AND  D.Fecha >=@desfecha'

 end



IF @HastaFecha is not NULL

    begin

    select @hasfecha=convert(datetime, @HastaFecha, 103)



     Set @strWhere = @strWhere + ' AND  D.Fecha <=@hasfecha'

 end





Set @instruccion  = N'SELECT D.Despacho, D.Fecha as FechaD, D.Aduana, D.Paisorigen as Pais, D.Garantia, 

                CASE WHEN D.TipoGarantia="E" THEN "Efectivo" 

                     WHEN D.TipoGarantia="C" THEN "Caución" 

                     WHEN D.TipoGarantia="M" THEN "Mixto" 

                     ELSE null END as Tipo, 

                D.Efectivo, D.FechaGarantia as Fecha, D.Compania, D.Poliza, D.FechavtoGarantia as Fechavto, D.Monto as Importe, 

                De.Desde, De.Hasta, convert(varchar(254), D.Observacion) as Observacion 

          FROM Despachos D (nolock)

                LEFT JOIN Despachosestampillas De (nolock) ON D.Despacho=De.Despacho

          WHERE 1=1 ' +  @strWhere + '

          ORDER BY D.Despacho, De.Desde'





          exec sp_executesql    @instruccion, N'@estampilla integer, @desfecha datetime, @hasfecha datetime', @estampilla=@estampilla, @desfecha=@desfecha, @hasfecha=@hasfecha





    end







    END

ALTER PROCEDURE dbo.StoredProcedureB    

    @xmlParametros XML

AS

BEGIN

    SET NOCOUNT ON;

    SET CONCAT_NULL_YIELDS_NULL OFF

    SET ANSI_NULLS OFF

    SET QUOTED_IDENTIFIER OFF

    SET NOCOUNT ON



    DECLARE @Sistema CHAR(1),

@Caratula tinyint,

@FormatoQRP VARCHAR(12),



@seteos NVARCHAR(200),

@instruccion NVARCHAR(max),

@instruccion2 NVARCHAR(max),

@texto VARCHAR(max),

@columna VARCHAR(100),

@tipodato VARCHAR(50)



IF @xmlParametros is not null

 BEGIN



    DECLARE @xml TABLE ( identidad integer IDENTITY (1,1) not null, tipo char(1) not null, columna varchar(30) not null, campoxml varchar(1000) not null, 

        tipodato varchar(50) not null, nulo tinyint null)



    INSERT @xml (columna, tipo, campoxml, tipodato, nulo)



    --      PARAMETRO    DIF      RUTA_DEL_NODO                         TIPO_DATO_NODO  NULO



    SELECT '@Sistema', 'E', '(/Parameters/SISTEMA)[1]', 'char(1)', null 

    UNION ALL

    SELECT '@Caratula', 'E', '(/Parameters/CARATULA)[1]', 'tinyint', null 

    UNION ALL

    SELECT '@FormatoQRP', 'E', '(/Parameters/FORMATOQRP)[1]', 'varchar(12)', null 

    --SELECT*FROM @xml



    --SETEOS SUMADOS A LA INSTRUCCION GRAL.

    SELECT @seteos = 'SET ANSI_NULLS ON

            SET QUOTED_IDENTIFIER ON

            SET CONCAT_NULL_YIELDS_NULL ON

            SET ANSI_PADDING ON

            SET ANSI_WARNINGS ON

            SET ARITHABORT ON

            SET NUMERIC_ROUNDABORT OFF '

    select @instruccion =null, @instruccion2 = N'@xmlParametros xml'



    --DECLARA CURSOR PARA RECORRER TABLA DECLARADA

    DECLARE INSTRUCCION INSENSITIVE CURSOR FOR

    SELECT columna, "@xmlParametros.value('" + campoxml + "', '" + tipodato + "')", tipodato

    FROM @xml

    WHERE tipo='E'

    ORDER BY identidad  



    OPEN INSTRUCCION

    FETCH NEXT FROM INSTRUCCION INTO @columna, @texto, @tipodato



    WHILE (@@FETCH_STATUS <> -1)

     BEGIN

        If @instruccion is not null

            SELECT @instruccion  = @instruccion + ', '



        SELECT @instruccion  = @instruccion + @columna + '=' + @texto, @instruccion2 = @instruccion2 + ', ' + @columna + ' ' + @tipodato + ' OUTPUT'



        FETCH NEXT FROM INSTRUCCION INTO @columna, @texto, @tipodato

     END



    CLOSE INSTRUCCION

    DEALLOCATE INSTRUCCION



    SELECT @instruccion = @seteos + N'SELECT ' + @instruccion 





    exec sp_executesql  @instruccion, @instruccion2,@xmlParametros=@xmlParametros,

                        @Sistema=@Sistema OUTPUT, @Caratula=@Caratula OUTPUT,

                        @FormatoQRP=@FormatoQRP OUTPUT

 END





IF @Sistema != 'C'

    SELECT CENPREFI, CODCEN, NOMBRE, ADMNEGOCIOS

    FROM dbo.CENTROSAP  (NOLOCK)

    ORDER BY CENPREFI

ELSE

    SELECT CENPREFI, CODCEN, NOMBRE

    FROM dbo.CENTROSAP  (NOLOCK)

    ORDER BY CENPREFI

END

It is necessary to obtain in both cases the result set to provide the DataSource defined in the RDLC report.

Thank you so much!

The WITH RESULT SETS option can be used to define metadata for a stored procedure when no results are returned by the input parameter(s). By doing this, you will define columns that will be used for the results and there will be columns in work with in the report regardless of the parameter that's used. An example of this follows. Also, is the NOLOCK hint necessary? This can lead to inconsistent results among other things.

EXEC YourDatabase.YourSchema.YourSP @ParameterName

WITH RESULT SETS 

(

  (

    ColumnA INT,

    ColumnB VARCHAR(100),

    ColumnC VARCHAR(100)

  )

)

I just generated the connection, the sql command and the data adapter to get the resultset. Could you tell me if it's okay:

SqlConnection conexionSQL = new SqlConnection("Data Source=SRVDESARROLLO7\BASCS;Initial Catalog=BASCSREPORT;Integrated Security=True");

conexionSQL.Open();

string stringCommand = "dbo.XXX_SP_REPORTING_SERVICES_DESPACHOS";

SqlCommand comandoSQL = new SqlCommand(stringCommand, conexionSQL);

comandoSQL.CommandType = CommandType.StoredProcedure;

comandoSQL.Parameters.AddWithValue("@xmlParametros", SqlDbType.Xml).Value = parametrosXML.ToString();

SqlDataAdapter da = new SqlDataAdapter(comandoSQL.ToString(), conexionSQL);

da.SelectCommand = comandoSQL;

BASCSREPORTDataSet dataSet1 = new BASCSREPORTDataSet();

da.Fill(dataSet1, dataSet1.Tables[0].TableName);

ReportDataSource rds = new ReportDataSource("XXX_SP_REPORTING_SERVICES_DESPACHOS", dataSet1.Tables[0]);

this.reportViewer1.LocalReport.DataSources.Clear();

this.reportViewer1.LocalReport.DataSources.Add(rds);

this.reportViewer1.LocalReport.Refresh();

this.reportViewer1.RefreshReport();