Support for Stored Procecures

[TwinbrotherPro]

First of all, thank you for this great provider! We actually are using most of the resources implemented.

Right now we are working on also implementing our Stored Procedures with that provider.

First we thought it would be possible to use the azuresql_function resource, but this does not seem to be intended to support stored procedures, right?

What are your thoughts on supporting Stored Procedures, as well? Would it make sense to extend the azuresql_function or will there be the need to implement a separate resource?

Happy to contribute on this functionality!

[jonascrevecoeur]

Hi @TwinbrotherPro

I'm happy to hear you are using azuresql and appreciate the suggestion!

At the moment, creating stored procedures with azuresql_function isn't supported because

1. the provider requires the definition to contain create function <schema>.<name> (<param>) as. This guarantees that the resource is read correctly across Terraform runs.
2. azuresql_function contains a mandatory field return_type which doesn't make sense in the context of a procedure.

Instead of extending the azuresql_function resource, I believe it is clearer to create a new resource azuresql_procedure. The implementation will be fairly similar to azuresql_function which should save a lot of time to develop it.

The syntax could look like:

resource "azuresql_procedure" "sum_raw" {
    database    = data.azuresql_database.database.id
    name        = "sum2"
    schema      = data.azuresql_schema.dbo.id
    raw         = <<-EOT
        create procedure dbo.sum2 @a int, @b int
        as 
        BEGIN
          select @a + @b as sum
        END
    EOT
}

resource "azuresql_procedure" "sum" {
  database    = data.azuresql_database.database.id
  name        = "sum"
  schema      = data.azuresql_schema.dbo.id
  properties  = {
    arguments = [
      {
        name  = "a"
        type  = "int"
      },
      {
        name  = "b"
        type  = "int"
      }
    ]
    executor      = "self"
    schemabinding = true
    definition    = "@a + @b"
  }
}

What do you think? I have some time later this week to do some more analysis and try a first implementation. If you are interested, I can tag you on the PR.

[TwinbrotherPro]

Sounds good :)

I am not sure if the properties attribute will work the same as with functions as Stored Procedures can be much more complex and a series of SQL Queries. I guess the benefit added by this abstraction will not rule out the complexity added by it.

I will sneakily add a data source sample to this thread as this would actually be the way how we would use it:

data "azuresql_procedure" "sum" {
    database    = data.azuresql_database.database.id
    name        = "sum"
    schema      = data.azuresql_schema.dbo.id
}

Our use case would look like the following:

1. Load stored procedure which has been added with migrations to terraform as data resource
2. Grant execute permissions for users which have been created with the azuresql_user resource

Thank you for your fast reply! Feel free to add me on the PR. I will have some available time during the weekend. If you run short on time I can also try a first draft.

[jonascrevecoeur]

Implemented in version 5.0.0 of the provider

[TwinbrotherPro]

Thank you very much for your implementation.

Just trying it out and so far no issues detected.

Thank you!