How to use stored procedures?

[thaigertech]

I would love some help/example with the following.

Many SQL programmers write large stored procedures and probably will continue to do so for some time. ORM is nice but moving data away from SQL has its issues. So... how to work with a stored procedure, feed it some stuff and get a result in Tiberius?

As example a simple MS SQL script as follows

go
if object_id('dbo.some_complex_proc') is not null drop proc dbo.some_complex_proc;
go
create proc dbo.some_complex_proc
    @id     int
,   @some_number    int      = null output
,   @some_varchar   varchar(100)     = null output
as
    set nocount on;

    -- checking on input simulating early exit if some input is outside the norm
    if @id < -42 return 50000;
    if @id > 42  return 50001;

    -- all great, so do output
    select  @some_number = 42 + @id
    ,   @some_varchar = 'You provided ' + cast(@id as varchar(10));
go
    /*
        -- run this to test in SQL
    declare @ret int, @id int = 5, @some_number int, @some_varchar varchar(max)
    exec @ret = dbo.some_complex_proc @id, @some_number output, @some_varchar output
    select @ret, @some_number, @some_varchar
    */

What I would like is some way to do something as var ret = client. execute("dbo.some_complex_proc", id, mut some_number, mut some_varchar).await?

Notice that packaging the stored proc in yet another exec_stored proc is probably not required. SQL programmers test commonly and hence should not rely on Rust or Tiberius to figure out what goes wrong if that so happens.

[pimeys]

So you want to support out variables, that mutate the variables in Rust? No, we don't have any fancy apis for that. Could you maybe link to an implementation in other ecosystems, how they are done? It might be a good-first-issue for somebody to do in Tiberius.

[lemalcs]

Hi @pimeys , please correct me If I'm wrong, there is not support for output parameters of stored procedures that can be retrieved from Tiberius?

[olback]

We also use a lot of stored procedures. I've made a convenience wrapper for stored procedures specifically. It does not solve the out-parameter problem though.

#[derive(Debug)]
pub struct MyConnectionWrapper(Client<Compat<TcpStream>>);

impl MyConnectionWrapper {
    #[tracing::instrument(skip(params))]
    pub async fn stored_procedure<'a>(
        &'a mut self,
        name: &str,
        params: &[&dyn tiberius::ToSql],
    ) -> tiberius::Result<tiberius::QueryStream<'a>> {
        let args = (1..=params.len())
            .map(|v| format!("@P{}", v))
            .collect::<Vec<String>>()
            .join(",");

        let query_parts: &[Cow<str>] = &[
            "set nocount on".into(),
            "declare @__RC int".into(),
            format!("execute @__RC = {name} {args}").into(),
            "select @__RC as __RC".into(),
        ];

        self.0.query(query_parts.join("\n"), params).await
    }

}

impl std::ops::Deref for MyConnectionWrapper {
    type Target = Client<Compat<TcpStream>>;

    fn deref(&self) -> &Self::Target {
        &self.0
    }
}

impl std::ops::DerefMut for MyConnectionWrapper {
    fn deref_mut(&mut self) -> &mut Self::Target {
        &mut self.0
    }
}

I have not tested the following but I think something like this might work. I might have gotten the SQL syntax wrong for the out parameter.

con.query(
  "declare @MyVar int; execute MyProcedure @P1, @P2, @MyVar = @MyVar output; select @MyVar as MyVar",
  &[arg1, arg2]
)

[lemalcs]

Hi @olback, it seems that currently the workaround of creating a variable to store the value of the output parameter, all inside SQL code, is the only way to make this to work.

I have tested the SQL code of your last snippet and works nice. Thanks!