Question: calling stored procedure which returns values

[Radamonas]

Reviewed documentation on calling stored procedures and SQL commands, and I would like to know:

1. is it possible to retrieve and pass further stored procedure output into the pipeline? Based on the documentation: ToSqlCommand always returns the input events as is.;
2. if script used in ToSqlCommand has variable declarations like:

DECLARE @typeVar Char(1) = 'A';
SELECT * FROM dbo.myTable WHERE type = @typeVar;

How we could use @ within, isn't it reserved for parameter injection from upper stream? Checked the code, and its a bit strange to use @ for params, as @ is used in MS SQL for variable definitions and @@ for system variables as well.

[paillave]

This code works for SqlConnection and such. With SqlConnection, Sql Drivers are directly used, so it supports native SQL Server queries, by using variables (they start with a @). Drivers transmit a command providing the value of these variables to Sql Server. SqlServer extensions for ETL.NET observe all the variables that exist in the given query, and set the value from the corresponding property of the payload.

At the moment, this works only for SqlConnection. I am working on OleDb, ODBC and Oracle Connection here: #423. At the end of the day, SQL server extensions will work for nearly anything. The only exception will be for the save operator that won't work for anything else than Sql Server (at least for now)

[Radamonas]

@paillave, thank you.

What about the first part of the question. Is there a way to get output of the stored procedure into output stream if that stored procedure returns anything?

[paillave]

@paillave, thank you.

What about the first part of the question. Is there a way to get output of the stored procedure into output stream if that stored procedure returns anything?

Normaly, using SQL server connection, an exec statement should do it from a CrossApplySqlServerQuery operator. It is not the case?

[...]
contextStream
    .CrossApplySqlServerQuery("get people", o => o
        .FromQuery("exec sp_getPeople @value1, @value2")
        .WithMapping(i => new
            {
                Name = i.ToColumn("FirstName"),
                Birthday = i.ToDateColumn("DateOfBirth")
            }));
[...]

[Radamonas]

@paillave thanks for explaining this, I assume this could be as a part of documentation as based on example provided there, it looks like ToSqlCommand is the only way to use it.

I will close this.

[paillave]

I will reopen it as a documentation ticket. Like most of opened ticket here, I will close them once their content is in the documentation.