How to capture the result set of a stored procedure with a dynamic Select (sp_executesql) in a RDLC report

Question

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!

Try adding a "USE DataBaseName" in the query. the server can have more than one database and Report Manager doe not know which database to query. The RDLC (is text and can open with Notepad) file has a tag with CommandText and you can put the command text into SSMS to test and make sure it returns results. The greater than sign and less than sign are html > and < and need to get replace in SSMS. — jdweng, Jan 02 '19 at 17:03
Good! In the CommandText tag as you indicated me the same figure the following: / * Local Query * / CommandText> With respect to the signs greater than and less than, I can not understand what you indicate. — NHCives, Jan 02 '19 at 17:47
html contains the characters '<' and '>' around the element names to the innertext cannot contain these two characters. So the innertext of the html you use '>' instead of the greater than sign and '<' instead of the less than sign. The innertext should be what you put in comments a "/* Local Query */ I assume there is a real SELECT statement instead of just the comments. — jdweng, Jan 02 '19 at 17:52
There add the full code of the SP that I am using to bring me the result set. As I indicated, the inconvenience is when I add a data set to the rdlc report, it is not giving me anything back. — NHCives, Jan 03 '19 at 15:11
I would add a "USE" into the query to make sure it is using the right database. I would check log files to see if database is reporting any errors. I would also would try to determine if the procedures are being called. You could create a view for debugging and add writes to view so you can tell if the procedures ran and how far the code executed. — jdweng, Jan 03 '19 at 17:47

score 0 · Answer 1 · answered Jan 02 '19 at 18:27

0

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)
  )
)

answered Jan 02 '19 at 18:27

userfl89

4,610
1
9
17

@NHCives I didn't quite understand your comment. Did this solve your problem? – userfl89 Jan 04 '19 at 18:42
Good afternoon! I do not think the problem is in the data type of each column that composes the ResultSet, but rather that it is not recognizing the Stored Procedure as an Object that returns rows, as if the case of the SP that has a "Select" – NHCives Jan 04 '19 at 18:50
The WITH RESULT SETS option defines the columns that are returned. The data types are included to associate the proper metadata. This is similar to defining what columns the "Select" will consist of. – userfl89 Jan 04 '19 at 18:54

score 0 · Answer 2 · answered Jan 07 '19 at 13:00

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();

How to capture the result set of a stored procedure with a dynamic Select (sp_executesql) in a RDLC report

2 Answers2