Repository: chanzuckerberg/terraform-provider-snowflake
Pull request #619 (2021-07-24 17:40:33)

feat: Procedures
<!-- Feel free to delete comments as you fill this in -->

<!-- summary of changes -->
* New resource **snowflake_procedure**
* As a user using Standard edition of Snowflake, had to add environment variables to prevent the creation of Materialized views and Masking policies.


## Test Plan
<!-- detail ways in which this PR has been tested or needs to be tested -->
* [x] acceptance tests
<!-- add more below if you think they are relevant -->
* [x] unit tests

## References
<!-- issues documentation links, etc  -->
* Implements part of https://github.com/chanzuckerberg/terraform-provider-snowflake/issues/532 and https://github.com/chanzuckerberg/terraform-provider-snowflake/issues/273
* https://docs.snowflake.com/en/sql-reference/sql/create-procedure.html



alldoami: /ok-to-test sha=6b08ca9
github-actions[bot]: Integration tests success for [6b08ca9](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/1068618016)
ChrisIsidora: @funes79 Can you take a look at the comments of @alldoami so this can be merged? I have a dependency on this feature. Thanks in advance
ChrisIsidora: @funes79 Can you also run the `make docs` so that the .md (markdown) is created? (optionally an example tf in the example folder could be nice) Thanks in advance
funes79: @ChrisIsidora  docs and examples added.
funes79: @alldoami is it ok to merge?
alldoami: /ok-to-test sha=a736b4d
alldoami: If you run `make docs` do you get a diff?
github-actions[bot]: Integration tests failure for [a736b4d](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/1121523362)
ChrisIsidora: @alldoami Added SKIP_SCIM_INTEGRATION_TESTS in #635 to fix the SCIM integration issue as it doesn't clean up properly if another test previously fails at some point or if Integrations tests are ran in parallel for different PRs.
ChrisIsidora: > If you run `make docs` do you get a diff?

@alldoami I think @funes79  needs to remove the new docs for the procedure. But after merge there needs to be a new PR created after running `make docs`  because the new docs for resources/datasources are not automatically created after commit.
funes79: I do not understand. On my local `make docs` runs fine. But I see that it failed in the Github actions. Is the any info why it causing the failure? 
funes79: > If you run `make docs` do you get a diff?

Yes I got a diff, so now I pushed a new commit to update the docs for procedure.
ChrisIsidora: > I do not understand. On my local `make docs` runs fine. But I see that it failed in the Github actions. Is the any info why it causing the failure?

@funes79 I don't know exactly why but I had similar issues some time ago when I also introduced a new resources. I suspect that the `make docs` checks against the `main` branch and compares the docs in the PR branch and if the comparison yields a difference then it fails, but in this case for new resources it's logical that there is a difference because the resource is not there yet in the `main` branch. So I think the use case for the `make docs` check is primarily for flagging existing resources docs that needs to be updated if that resource was changed in the PR branch.

So fastest solution for now is to remove the new .md and after the merge, open a PR with only the new .md.

(Related to #632)
alldoami: /ok-to-test sha=40aca4d
github-actions[bot]: Integration tests success for [40aca4d](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/1124834968)

Patch:
diff --git a/docs/resources/procedure.md b/docs/resources/procedure.md
new file mode 100644
index 00000000..b67f8c85
--- /dev/null
+++ b/docs/resources/procedure.md
@@ -0,0 +1,86 @@
+---
+# generated by https://github.com/hashicorp/terraform-plugin-docs
+page_title: "snowflake_procedure Resource - terraform-provider-snowflake"
+subcategory: ""
+description: |-
+  
+---
+
+# snowflake_procedure (Resource)
+
+
+
+## Example Usage
+
+```terraform
+resource "snowflake_schema" "db" {
+  name                = "MYDB"
+  data_retention_days = 1
+}
+
+resource "snowflake_schema" "schema" {
+  database            = snowflake_database.db.name
+  name                = "MYSCHEMA"
+  data_retention_days = 1
+}
+
+resource "snowflake_procedure" "proc" {
+  name     = "SAMPLEPROC"
+  database = snowflake_database.db.name
+  schema   = snowflake_schema.schema.name
+  arguments {
+    name = "arg1"
+    type = "varchar"
+  }
+  arguments {
+    name = "arg2"
+    type = "DATE"
+  }
+  comment             = "Procedure with 2 arguments"
+  return_type         = "VARCHAR"
+  execute_as          = "CALLER"
+  return_behavior     = "IMMUTABLE"
+  null_input_behavior = "RETURNS NULL ON NULL INPUT"
+  statement           = <<EOT
+var X=1
+return X
+EOT
+}
+```
+
+<!-- schema generated by tfplugindocs -->
+## Schema
+
+### Required
+
+- **database** (String) The database in which to create the procedure. Don't use the | character.
+- **name** (String) Specifies the identifier for the procedure; does not have to be unique for the schema in which the procedure is created. Don't use the | character.
+- **return_type** (String) The return type of the procedure
+- **schema** (String) The schema in which to create the procedure. Don't use the | character.
+- **statement** (String) Specifies the javascript code used to create the procedure.
+
+### Optional
+
+- **arguments** (Block List) List of the arguments for the procedure (see [below for nested schema](#nestedblock--arguments))
+- **comment** (String) Specifies a comment for the procedure.
+- **execute_as** (String) Sets execute context - see caller's rights and owner's rights
+- **id** (String) The ID of this resource.
+- **null_input_behavior** (String) Specifies the behavior of the procedure when called with null inputs.
+- **return_behavior** (String) Specifies the behavior of the function when returning results
+
+<a id="nestedblock--arguments"></a>
+### Nested Schema for `arguments`
+
+Required:
+
+- **name** (String) The argument name
+- **type** (String) The argument type
+
+## Import
+
+Import is supported using the following syntax:
+
+```shell
+# format is database name | schema name | stored procedure name | <list of arg types, separated with '-'>
+terraform import snowflake_procedure.example 'dbName|schemaName|procedureName|varchar-varchar-varchar'
+```
diff --git a/examples/resources/snowflake_procedure/import.sh b/examples/resources/snowflake_procedure/import.sh
new file mode 100644
index 00000000..9d0a343f
--- /dev/null
+++ b/examples/resources/snowflake_procedure/import.sh
@@ -0,0 +1,2 @@
+# format is database name | schema name | stored procedure name | <list of arg types, separated with '-'>
+terraform import snowflake_procedure.example 'dbName|schemaName|procedureName|varchar-varchar-varchar'
diff --git a/examples/resources/snowflake_procedure/resource.tf b/examples/resources/snowflake_procedure/resource.tf
new file mode 100644
index 00000000..d728e902
--- /dev/null
+++ b/examples/resources/snowflake_procedure/resource.tf
@@ -0,0 +1,33 @@
+resource "snowflake_schema" "db" {
+  name                = "MYDB"
+  data_retention_days = 1
+}
+
+resource "snowflake_schema" "schema" {
+  database            = snowflake_database.db.name
+  name                = "MYSCHEMA"
+  data_retention_days = 1
+}
+
+resource "snowflake_procedure" "proc" {
+  name     = "SAMPLEPROC"
+  database = snowflake_database.db.name
+  schema   = snowflake_schema.schema.name
+  arguments {
+    name = "arg1"
+    type = "varchar"
+  }
+  arguments {
+    name = "arg2"
+    type = "DATE"
+  }
+  comment             = "Procedure with 2 arguments"
+  return_type         = "VARCHAR"
+  execute_as          = "CALLER"
+  return_behavior     = "IMMUTABLE"
+  null_input_behavior = "RETURNS NULL ON NULL INPUT"
+  statement           = <<EOT
+var X=1
+return X
+EOT
+}
diff --git a/pkg/provider/provider.go b/pkg/provider/provider.go
index beba2b5e..9fa02168 100644
--- a/pkg/provider/provider.go
+++ b/pkg/provider/provider.go
@@ -167,6 +167,7 @@ func getResources() map[string]*schema.Resource {
 		"snowflake_network_policy_attachment": resources.NetworkPolicyAttachment(),
 		"snowflake_network_policy":            resources.NetworkPolicy(),
 		"snowflake_pipe":                      resources.Pipe(),
+		"snowflake_procedure":                 resources.Procedure(),
 		"snowflake_resource_monitor":          resources.ResourceMonitor(),
 		"snowflake_role":                      resources.Role(),
 		"snowflake_role_grants":               resources.RoleGrants(),
diff --git a/pkg/resources/procedure.go b/pkg/resources/procedure.go
new file mode 100644
index 00000000..28765df6
--- /dev/null
+++ b/pkg/resources/procedure.go
@@ -0,0 +1,463 @@
+package resources
+
+import (
+	"database/sql"
+	"fmt"
+	"log"
+	"regexp"
+	"strings"
+
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/snowflake"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/validation"
+	"github.com/pkg/errors"
+)
+
+var procedureSchema = map[string]*schema.Schema{
+	"name": {
+		Type:        schema.TypeString,
+		Required:    true,
+		Description: "Specifies the identifier for the procedure; does not have to be unique for the schema in which the procedure is created. Don't use the | character.",
+	},
+	"database": {
+		Type:        schema.TypeString,
+		Required:    true,
+		Description: "The database in which to create the procedure. Don't use the | character.",
+		ForceNew:    true,
+	},
+	"schema": {
+		Type:        schema.TypeString,
+		Required:    true,
+		Description: "The schema in which to create the procedure. Don't use the | character.",
+		ForceNew:    true,
+	},
+	"arguments": {
+		Type: schema.TypeList,
+		Elem: &schema.Resource{
+			Schema: map[string]*schema.Schema{
+				"name": {
+					Type:     schema.TypeString,
+					Required: true,
+					// Suppress the diff shown if the values are equal when both compared in lower case.
+					DiffSuppressFunc: DiffTypes,
+					Description:      "The argument name",
+				},
+				"type": {
+					Type:     schema.TypeString,
+					Required: true,
+					// Suppress the diff shown if the values are equal when both compared in lower case.
+					DiffSuppressFunc: DiffTypes,
+					Description:      "The argument type",
+				},
+			},
+		},
+		Optional:    true,
+		Description: "List of the arguments for the procedure",
+		ForceNew:    true,
+	},
+	"return_type": {
+		Type:        schema.TypeString,
+		Description: "The return type of the procedure",
+		// Suppress the diff shown if the values are equal when both compared in lower case.
+		DiffSuppressFunc: DiffTypes,
+		Required:         true,
+		ForceNew:         true,
+	},
+	"statement": {
+		Type:             schema.TypeString,
+		Required:         true,
+		Description:      "Specifies the javascript code used to create the procedure.",
+		ForceNew:         true,
+		DiffSuppressFunc: DiffSuppressStatement,
+	},
+	"execute_as": {
+		Type:        schema.TypeString,
+		Optional:    true,
+		Default:     "OWNER",
+		Description: "Sets execute context - see caller's rights and owner's rights",
+	},
+	"null_input_behavior": {
+		Type:     schema.TypeString,
+		Optional: true,
+		Default:  "CALLED ON NULL INPUT",
+		ForceNew: true,
+		// We do not use STRICT, because Snowflake then in the Read phase returns RETURNS NULL ON NULL INPUT
+		ValidateFunc: validation.StringInSlice([]string{"CALLED ON NULL INPUT", "RETURNS NULL ON NULL INPUT"}, false),
+		Description:  "Specifies the behavior of the procedure when called with null inputs.",
+	},
+	"return_behavior": {
+		Type:         schema.TypeString,
+		Optional:     true,
+		Default:      "VOLATILE",
+		ForceNew:     true,
+		ValidateFunc: validation.StringInSlice([]string{"VOLATILE", "IMMUTABLE"}, false),
+		Description:  "Specifies the behavior of the function when returning results",
+	},
+	"comment": {
+		Type:        schema.TypeString,
+		Optional:    true,
+		Default:     "user-defined function",
+		Description: "Specifies a comment for the procedure.",
+	},
+}
+
+func DiffTypes(k, old, new string, d *schema.ResourceData) bool {
+	return strings.EqualFold(strings.ToUpper(old), strings.ToUpper(new))
+}
+
+// Procedure returns a pointer to the resource representing a stored procedure
+func Procedure() *schema.Resource {
+	return &schema.Resource{
+		Create: CreateProcedure,
+		Read:   ReadProcedure,
+		Update: UpdateProcedure,
+		Delete: DeleteProcedure,
+
+		Schema: procedureSchema,
+		Importer: &schema.ResourceImporter{
+			StateContext: schema.ImportStatePassthroughContext,
+		},
+	}
+}
+
+// CreateProcedure implements schema.CreateFunc
+func CreateProcedure(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	name := d.Get("name").(string)
+	schema := d.Get("schema").(string)
+	database := d.Get("database").(string)
+	s := d.Get("statement").(string)
+	ret := d.Get("return_type").(string)
+
+	builder := snowflake.Procedure(database, schema, name, []string{}).WithStatement(s).WithReturnType(ret)
+
+	// Set optionals, args
+	if _, ok := d.GetOk("arguments"); ok {
+		args := []map[string]string{}
+		for _, arg := range d.Get("arguments").([]interface{}) {
+			argDef := map[string]string{}
+			for key, val := range arg.(map[string]interface{}) {
+				argDef[key] = val.(string)
+			}
+			args = append(args, argDef)
+		}
+		builder.WithArgs(args)
+	}
+
+	// Set optionals, default is false
+	if v, ok := d.GetOk("return_behavior"); ok {
+		builder.WithReturnBehavior(v.(string))
+	}
+
+	// Set optionals, default is false
+	if v, ok := d.GetOk("null_input_behavior"); ok {
+		builder.WithNullInputBehavior(v.(string))
+	}
+
+	// Set optionals, default is OWNER
+	if v, ok := d.GetOk("execute_as"); ok {
+		builder.WithExecuteAs(v.(string))
+	}
+
+	if v, ok := d.GetOk("comment"); ok {
+		builder.WithComment(v.(string))
+	}
+
+	q, err := builder.Create()
+	if err != nil {
+		return err
+	}
+	err = snowflake.Exec(db, q)
+	if err != nil {
+		return errors.Wrapf(err, "error creating procedure %v", name)
+	}
+
+	procedureID := &procedureID{
+		DatabaseName:  database,
+		SchemaName:    schema,
+		ProcedureName: name,
+		ArgTypes:      builder.ArgTypes(),
+	}
+
+	d.SetId(procedureID.String())
+
+	return ReadProcedure(d, meta)
+}
+
+// ReadProcedure implements schema.ReadFunc
+func ReadProcedure(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	procedureID, err := splitProcedureID(d.Id())
+	if err != nil {
+		return err
+	}
+	proc := snowflake.Procedure(
+		procedureID.DatabaseName,
+		procedureID.SchemaName,
+		procedureID.ProcedureName,
+		procedureID.ArgTypes,
+	)
+
+	// some atributes can be retrieved only by Describe and some only by Show
+	stmt, err := proc.Describe()
+	if err != nil {
+		return err
+	}
+	rows, err := snowflake.Query(db, stmt)
+	if err != nil {
+		return err
+	}
+	defer rows.Close()
+	descPropValues, err := snowflake.ScanProcedureDescription(rows)
+	if err != nil {
+		return err
+	}
+	for _, desc := range descPropValues {
+		switch desc.Property.String {
+		case "signature":
+			// Format in Snowflake DB is: (argName argType, argName argType, ...)
+			args := strings.ReplaceAll(strings.ReplaceAll(desc.Value.String, "(", ""), ")", "")
+
+			if args != "" { // Do nothing for functions without arguments
+				argPairs := strings.Split(args, ", ")
+				args := []interface{}{}
+
+				for _, argPair := range argPairs {
+					argItem := strings.Split(argPair, " ")
+
+					arg := map[string]interface{}{}
+					arg["name"] = argItem[0]
+					arg["type"] = argItem[1]
+					args = append(args, arg)
+				}
+
+				if err = d.Set("arguments", args); err != nil {
+					return err
+				}
+			}
+		case "null handling":
+			if err = d.Set("null_input_behavior", desc.Value.String); err != nil {
+				return err
+			}
+		case "volatility":
+			if err = d.Set("return_behavior", desc.Value.String); err != nil {
+				return err
+			}
+		case "body":
+			if err = d.Set("statement", desc.Value.String); err != nil {
+				return err
+			}
+		case "execute as":
+			if err = d.Set("execute_as", desc.Value.String); err != nil {
+				return err
+			}
+		case "returns":
+			// Format in Snowflake DB is returnType(<some number>)
+			re := regexp.MustCompile(`^(.*)\([0-9]*\)$`)
+			match := re.FindStringSubmatch(desc.Value.String)
+			if err = d.Set("return_type", match[1]); err != nil {
+				return err
+			}
+		case "language":
+			// To ignore
+		default:
+			log.Printf("[WARN] unexpected procedure property %v returned from Snowflake", desc.Property.String)
+		}
+	}
+
+	q := proc.Show()
+	showRows, err := snowflake.Query(db, q)
+	if err == sql.ErrNoRows {
+		// If not found, mark resource to be removed from statefile during apply or refresh
+		log.Printf("[DEBUG] procedure (%s) not found", d.Id())
+		d.SetId("")
+		return nil
+	}
+	if err != nil {
+		return err
+	}
+	defer showRows.Close()
+
+	foundProcedures, err := snowflake.ScanProcedures(showRows)
+	if err != nil {
+		return err
+	}
+	// procedure names can be overloaded with different argument types so we
+	// iterate over and find the correct one
+	argSig, _ := proc.ArgumentsSignature()
+
+	for _, v := range foundProcedures {
+		if v.Arguments.String == argSig {
+			err = d.Set("comment", v.Comment.String)
+			if err != nil {
+				return err
+			}
+		}
+	}
+
+	return nil
+}
+
+// UpdateProcedure implements schema.UpdateProcedure
+func UpdateProcedure(d *schema.ResourceData, meta interface{}) error {
+	pID, err := splitProcedureID(d.Id())
+	if err != nil {
+		return err
+	}
+	builder := snowflake.Procedure(
+		pID.DatabaseName,
+		pID.SchemaName,
+		pID.ProcedureName,
+		pID.ArgTypes,
+	)
+
+	db := meta.(*sql.DB)
+	if d.HasChange("name") {
+		name := d.Get("name")
+		q, err := builder.Rename(name.(string))
+		if err != nil {
+			return err
+		}
+		err = snowflake.Exec(db, q)
+		if err != nil {
+			return errors.Wrapf(err, "error renaming procedure %v", d.Id())
+		}
+		newID := &procedureID{
+			DatabaseName:  pID.DatabaseName,
+			SchemaName:    pID.SchemaName,
+			ProcedureName: name.(string),
+			ArgTypes:      pID.ArgTypes,
+		}
+		d.SetId(newID.String())
+	}
+
+	if d.HasChange("comment") {
+		comment := d.Get("comment")
+
+		if c := comment.(string); c == "" {
+			q, err := builder.RemoveComment()
+			if err != nil {
+				return err
+			}
+			err = snowflake.Exec(db, q)
+			if err != nil {
+				return errors.Wrapf(err, "error unsetting comment for procedure %v", d.Id())
+			}
+		} else {
+			q, err := builder.ChangeComment(c)
+			if err != nil {
+				return err
+			}
+			err = snowflake.Exec(db, q)
+			if err != nil {
+				return errors.Wrapf(err, "error updating comment for procedure %v", d.Id())
+			}
+		}
+	}
+	if d.HasChange("execute_as") {
+		executeAs := d.Get("execute_as")
+
+		q, err := builder.ChangeExecuteAs(executeAs.(string))
+		if err != nil {
+			return err
+		}
+		err = snowflake.Exec(db, q)
+		if err != nil {
+			return errors.Wrapf(err, "error changing execute as for procedure %v", d.Id())
+		}
+	}
+
+	return ReadProcedure(d, meta)
+}
+
+// DeleteProcedure implements schema.DeleteFunc
+func DeleteProcedure(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	pID, err := splitProcedureID(d.Id())
+	if err != nil {
+		return err
+	}
+	builder := snowflake.Procedure(
+		pID.DatabaseName,
+		pID.SchemaName,
+		pID.ProcedureName,
+		pID.ArgTypes,
+	)
+
+	q, err := builder.Drop()
+	if err != nil {
+		return err
+	}
+
+	err = snowflake.Exec(db, q)
+	if err != nil {
+		return errors.Wrapf(err, "error deleting procedure %v", d.Id())
+	}
+
+	d.SetId("")
+
+	return nil
+}
+
+// ProcedureExists implements schema.ExistsFunc
+func ProcedureExists(d *schema.ResourceData, meta interface{}) (bool, error) {
+	db := meta.(*sql.DB)
+	pID, err := splitProcedureID(d.Id())
+	if err != nil {
+		return false, err
+	}
+	builder := snowflake.Procedure(
+		pID.DatabaseName,
+		pID.SchemaName,
+		pID.ProcedureName,
+		pID.ArgTypes,
+	)
+
+	q := builder.Show()
+	showRows, err := snowflake.Query(db, q)
+	if err != nil {
+		return false, err
+	}
+	defer showRows.Close()
+	if showRows.Next() {
+		return true, nil
+	}
+	return false, nil
+}
+
+type procedureID struct {
+	DatabaseName  string
+	SchemaName    string
+	ProcedureName string
+	ArgTypes      []string
+}
+
+// splitProcedureID takes the <database_name>|<schema_name>|<view_name>|<argtypes> ID and returns
+// the procedureID struct, for example MYDB|PUBLIC|PROC1|VARCHAR-DATE-VARCHAR
+// returns struct
+//         DatabaseName: MYDB
+//         SchemaName: PUBLIC
+//         ProcedureName: PROC1
+//         ArgTypes: [VARCHAR, DATE, VARCHAR]
+func splitProcedureID(v string) (*procedureID, error) {
+	arr := strings.Split(v, "|")
+	if len(arr) != 4 {
+		return nil, fmt.Errorf("ID %v is invalid", v)
+	}
+
+	return &procedureID{
+		DatabaseName:  arr[0],
+		SchemaName:    arr[1],
+		ProcedureName: arr[2],
+		ArgTypes:      strings.Split(arr[3], "-"),
+	}, nil
+}
+
+// the opposite of splitProcedureID
+func (pi *procedureID) String() string {
+	return fmt.Sprintf("%v|%v|%v|%v",
+		pi.DatabaseName,
+		pi.SchemaName,
+		pi.ProcedureName,
+		strings.Join(pi.ArgTypes, "-"))
+}
diff --git a/pkg/snowflake/procedure.go b/pkg/snowflake/procedure.go
new file mode 100644
index 00000000..4d6383c9
--- /dev/null
+++ b/pkg/snowflake/procedure.go
@@ -0,0 +1,268 @@
+package snowflake
+
+import (
+	"database/sql"
+	"errors"
+	"fmt"
+	"strings"
+
+	"github.com/jmoiron/sqlx"
+)
+
+// ProcedureBuilder abstracts the creation of Stored Procedure
+type ProcedureBuilder struct {
+	name              string
+	schema            string
+	db                string
+	argumentTypes     []string // (VARCHAR, VARCHAR)
+	args              []map[string]string
+	returnBehavior    string // VOLATILE, IMMUTABLE
+	nullInputBehavior string // "CALLED ON NULL INPUT" or "RETURNS NULL ON NULL INPUT"
+	returnType        string
+	executeAs         string
+	comment           string
+	statement         string
+}
+
+// QualifiedName prepends the db and schema and appends argument types
+func (pb *ProcedureBuilder) QualifiedName() (string, error) {
+	if pb.db == "" || pb.schema == "" || pb.name == "" {
+		return "", errors.New("Procedures must specify a database a schema and a name")
+	}
+	return fmt.Sprintf(`"%v"."%v"."%v"(%v)`, pb.db, pb.schema, pb.name, strings.Join(pb.argumentTypes, ", ")), nil
+}
+
+// QualifiedNameWithoutArguments prepends the db and schema if set
+func (pb *ProcedureBuilder) QualifiedNameWithoutArguments() (string, error) {
+	if pb.db == "" || pb.schema == "" || pb.name == "" {
+		return "", errors.New("Procedures must specify a database a schema and a name")
+	}
+	return fmt.Sprintf(`"%v"."%v"."%v"`, pb.db, pb.schema, pb.name), nil
+}
+
+// Returns the arguments signature of the procedure in a form <procedure>(<type>, <type>, ..) RETURN <type>
+func (pb *ProcedureBuilder) ArgumentsSignature() (string, error) {
+	return fmt.Sprintf(`%v(%v) RETURN %v`, pb.name, strings.Join(pb.argumentTypes, ", "), pb.returnType), nil
+}
+
+// WithArgs sets the args and argumentTypes on the ProcedureBuilder
+func (pb *ProcedureBuilder) WithArgs(args []map[string]string) *ProcedureBuilder {
+	pb.args = []map[string]string{}
+	for _, arg := range args {
+		argName := arg["name"]
+		argType := strings.ToUpper(arg["type"])
+		pb.args = append(pb.args, map[string]string{"name": argName, "type": argType})
+		pb.argumentTypes = append(pb.argumentTypes, argType)
+	}
+	return pb
+}
+
+// WithReturnBehavior
+func (pb *ProcedureBuilder) WithReturnBehavior(s string) *ProcedureBuilder {
+	pb.returnBehavior = s
+	return pb
+}
+
+// WithNullInputBehavior
+func (pb *ProcedureBuilder) WithNullInputBehavior(s string) *ProcedureBuilder {
+	pb.nullInputBehavior = s
+	return pb
+}
+
+// WithReturnType adds the data type of the return type to the ProcedureBuilder
+func (pb *ProcedureBuilder) WithReturnType(s string) *ProcedureBuilder {
+	pb.returnType = strings.ToUpper(s)
+	return pb
+}
+
+// WithExecuteAs sets the execute to OWNER or CALLER
+func (pb *ProcedureBuilder) WithExecuteAs(s string) *ProcedureBuilder {
+	pb.executeAs = s
+	return pb
+}
+
+// WithComment adds a comment to the ProcedureBuilder
+func (pb *ProcedureBuilder) WithComment(c string) *ProcedureBuilder {
+	pb.comment = c
+	return pb
+}
+
+// WithStatement adds the SQL statement to be used for the procedure
+func (pb *ProcedureBuilder) WithStatement(s string) *ProcedureBuilder {
+	pb.statement = s
+	return pb
+}
+
+// Returns the argument types
+func (pb *ProcedureBuilder) ArgTypes() []string {
+	return pb.argumentTypes
+}
+
+// Procedure returns a pointer to a Builder that abstracts the DDL operations for a stored procedure.
+//
+// Supported DDL operations are:
+//   - CREATE PROCEDURE
+//   - ALTER PROCEDURE
+//   - DROP PROCEDURE
+//   - SHOW PROCEDURE
+//   - DESCRIBE
+//
+// [Snowflake Reference](https://docs.snowflake.com/en/sql-reference/stored-procedures.html)
+func Procedure(db, schema, name string, argTypes []string) *ProcedureBuilder {
+	return &ProcedureBuilder{
+		name:          name,
+		db:            db,
+		schema:        schema,
+		argumentTypes: argTypes,
+	}
+}
+
+// Create returns the SQL query that will create a new procedure.
+func (pb *ProcedureBuilder) Create() (string, error) {
+	var q strings.Builder
+
+	q.WriteString("CREATE OR REPLACE")
+
+	qn, err := pb.QualifiedNameWithoutArguments()
+	if err != nil {
+		return "", err
+	}
+
+	q.WriteString(fmt.Sprintf(" PROCEDURE %v", qn))
+
+	q.WriteString(`(`)
+	args := []string{}
+	for _, arg := range pb.args {
+		args = append(args, fmt.Sprintf(`%v %v`, EscapeString(arg["name"]), EscapeString(arg["type"])))
+	}
+	q.WriteString(strings.Join(args, ", "))
+	q.WriteString(`)`)
+
+	q.WriteString(fmt.Sprintf(" RETURNS %v", pb.returnType))
+	q.WriteString(" LANGUAGE javascript")
+	if pb.nullInputBehavior != "" {
+		q.WriteString(fmt.Sprintf(` %v`, EscapeString(pb.nullInputBehavior)))
+	}
+	if pb.returnBehavior != "" {
+		q.WriteString(fmt.Sprintf(` %v`, EscapeString(pb.returnBehavior)))
+	}
+	if pb.comment != "" {
+		q.WriteString(fmt.Sprintf(" COMMENT = '%v'", EscapeString(pb.comment)))
+	}
+	q.WriteString(fmt.Sprintf(" EXECUTE AS %v", pb.executeAs))
+	q.WriteString(fmt.Sprintf(" AS $$%v$$", pb.statement))
+	return q.String(), nil
+}
+
+// Rename returns the SQL query that will rename the procedure.
+func (pb *ProcedureBuilder) Rename(newName string) (string, error) {
+	oldName, err := pb.QualifiedName()
+	if err != nil {
+		return "", err
+	}
+	pb.name = newName
+
+	qn, err := pb.QualifiedNameWithoutArguments()
+	if err != nil {
+		return "", err
+	}
+	return fmt.Sprintf(`ALTER PROCEDURE %v RENAME TO %v`, oldName, qn), nil
+}
+
+// ChangeComment returns the SQL query that will update the comment on the procedure.
+func (vb *ProcedureBuilder) ChangeComment(c string) (string, error) {
+	qn, err := vb.QualifiedName()
+	if err != nil {
+		return "", err
+	}
+
+	return fmt.Sprintf(`ALTER PROCEDURE %v SET COMMENT = '%v'`, qn, EscapeString(c)), nil
+}
+
+// RemoveComment returns the SQL query that will remove the comment on the procedure.
+func (vb *ProcedureBuilder) RemoveComment() (string, error) {
+	qn, err := vb.QualifiedName()
+	if err != nil {
+		return "", err
+	}
+	return fmt.Sprintf(`ALTER PROCEDURE %v UNSET COMMENT`, qn), nil
+}
+
+// ChangeExecuteAs returns the SQL query that will update the call mode on the procedure.
+func (vb *ProcedureBuilder) ChangeExecuteAs(c string) (string, error) {
+	qn, err := vb.QualifiedName()
+	if err != nil {
+		return "", err
+	}
+	return fmt.Sprintf(`ALTER PROCEDURE %v EXECUTE AS %v`, qn, c), nil
+}
+
+// Show returns the SQL query that will show the row representing this procedure.
+// This show statement returns all procedures with the given name (overloaded ones)
+func (pb *ProcedureBuilder) Show() string {
+	return fmt.Sprintf(`SHOW PROCEDURES LIKE '%v' IN SCHEMA "%v"."%v"`, pb.name, pb.db, pb.schema)
+}
+
+// To describe the procedure the name must be specified as fully qualified name
+// including argument types
+func (pb *ProcedureBuilder) Describe() (string, error) {
+	qn, err := pb.QualifiedName()
+	if err != nil {
+		return "", err
+	}
+	return fmt.Sprintf(`DESCRIBE PROCEDURE %v`, qn), nil
+}
+
+// Drop returns the SQL query that will drop the procedure.
+func (pb *ProcedureBuilder) Drop() (string, error) {
+	qn, err := pb.QualifiedName()
+	if err != nil {
+		return "", err
+	}
+	return fmt.Sprintf(`DROP PROCEDURE %v`, qn), nil
+}
+
+type procedure struct {
+	Comment sql.NullString `db:"description"`
+	// Snowflake returns is_secure in the show procedure output, but it is irrelevant
+	Name         sql.NullString `db:"name"`
+	SchemaName   sql.NullString `db:"schema_name"`
+	Text         sql.NullString `db:"text"`
+	DatabaseName sql.NullString `db:"database_name"`
+	Arguments    sql.NullString `db:"arguments"`
+}
+
+type procedureDescription struct {
+	Property sql.NullString `db:"property"`
+	Value    sql.NullString `db:"value"`
+}
+
+// ScanProcedureDescription reads through the rows with property and value columns
+// and returns a slice of procedureDescription structs
+func ScanProcedureDescription(rows *sqlx.Rows) ([]procedureDescription, error) {
+	pdsl := []procedureDescription{}
+	for rows.Next() {
+		pd := procedureDescription{}
+		err := rows.StructScan(&pd)
+		if err != nil {
+			return nil, err
+		}
+		pdsl = append(pdsl, pd)
+	}
+	return pdsl, rows.Err()
+}
+
+// SHOW PROCEDURE can return more than one item because of procedure names overloading
+// https://docs.snowflake.com/en/sql-reference/sql/show-procedures.html
+func ScanProcedures(rows *sqlx.Rows) ([]*procedure, error) {
+	var pcs []*procedure
+	for rows.Next() {
+		r := &procedure{}
+		err := rows.StructScan(r)
+		if err != nil {
+			return nil, err
+		}
+		pcs = append(pcs, r)
+	}
+	return pcs, rows.Err()
+}


Test patch:
diff --git a/pkg/resources/helpers_test.go b/pkg/resources/helpers_test.go
index 69a13c0c..86ab7571 100644
--- a/pkg/resources/helpers_test.go
+++ b/pkg/resources/helpers_test.go
@@ -208,6 +208,14 @@ func externalFunction(t *testing.T, id string, params map[string]interface{}) *s
 	return d
 }
 
+func procedure(t *testing.T, id string, params map[string]interface{}) *schema.ResourceData {
+	r := require.New(t)
+	d := schema.TestResourceDataRaw(t, resources.Procedure().Schema, params)
+	r.NotNil(d)
+	d.SetId(id)
+	return d
+}
+
 func storageIntegration(t *testing.T, id string, params map[string]interface{}) *schema.ResourceData {
 	r := require.New(t)
 	d := schema.TestResourceDataRaw(t, resources.StorageIntegration().Schema, params)
diff --git a/pkg/resources/masking_policy_grant_acceptance_test.go b/pkg/resources/masking_policy_grant_acceptance_test.go
index 80fd1620..d2c0fea0 100644
--- a/pkg/resources/masking_policy_grant_acceptance_test.go
+++ b/pkg/resources/masking_policy_grant_acceptance_test.go
@@ -2,6 +2,7 @@ package resources_test
 
 import (
 	"fmt"
+	"os"
 	"strings"
 	"testing"
 
@@ -10,6 +11,9 @@ import (
 )
 
 func TestAcc_MaskingPolicyGrant(t *testing.T) {
+	if _, ok := os.LookupEnv("SKIP_MASKING_POLICY_TESTS"); ok {
+		t.Skip("Skipping TestAccMaskingPolicy")
+	}
 	accName := strings.ToUpper(acctest.RandStringFromCharSet(10, acctest.CharSetAlpha))
 
 	resource.Test(t, resource.TestCase{
diff --git a/pkg/resources/materialized_view_acceptance_test.go b/pkg/resources/materialized_view_acceptance_test.go
index 251cf8e1..7f4c26cf 100644
--- a/pkg/resources/materialized_view_acceptance_test.go
+++ b/pkg/resources/materialized_view_acceptance_test.go
@@ -2,6 +2,7 @@ package resources_test
 
 import (
 	"fmt"
+	"os"
 	"strings"
 	"testing"
 
@@ -10,6 +11,9 @@ import (
 )
 
 func TestAcc_MaterializedView(t *testing.T) {
+	if _, ok := os.LookupEnv("SKIP_MATERIALIZED_VIEW_TESTS"); ok {
+		t.Skip("Skipping TestAcc_MaterializedView")
+	}
 	dbName := strings.ToUpper(acctest.RandStringFromCharSet(10, acctest.CharSetAlpha))
 	schemaName := strings.ToUpper(acctest.RandStringFromCharSet(10, acctest.CharSetAlpha))
 	tableName := strings.ToUpper(acctest.RandStringFromCharSet(10, acctest.CharSetAlpha))
@@ -35,6 +39,9 @@ func TestAcc_MaterializedView(t *testing.T) {
 }
 
 func TestAcc_MaterializedView2(t *testing.T) {
+	if _, ok := os.LookupEnv("SKIP_MATERIALIZED_VIEW_TESTS"); ok {
+		t.Skip("Skipping TestAcc_MaterializedView2")
+	}
 	dbName := strings.ToUpper(acctest.RandStringFromCharSet(10, acctest.CharSetAlpha))
 	schemaName := strings.ToUpper(acctest.RandStringFromCharSet(10, acctest.CharSetAlpha))
 	tableName := strings.ToUpper(acctest.RandStringFromCharSet(10, acctest.CharSetAlpha))
diff --git a/pkg/resources/procedure_acceptance_test.go b/pkg/resources/procedure_acceptance_test.go
new file mode 100644
index 00000000..08ac5c0b
--- /dev/null
+++ b/pkg/resources/procedure_acceptance_test.go
@@ -0,0 +1,115 @@
+package resources_test
+
+import (
+	"fmt"
+	"os"
+	"strings"
+	"testing"
+
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/acctest"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/resource"
+)
+
+func TestAcc_Procedure(t *testing.T) {
+	if _, ok := os.LookupEnv("SKIP_PROCEDURE_TESTS"); ok {
+		t.Skip("Skipping TestAcc_Procedure")
+	}
+
+	dbName := strings.ToUpper(acctest.RandStringFromCharSet(10, acctest.CharSetAlpha))
+	schemaName := strings.ToUpper(acctest.RandStringFromCharSet(10, acctest.CharSetAlpha))
+	procName := strings.ToUpper(acctest.RandStringFromCharSet(10, acctest.CharSetAlpha))
+	expBody1 := "return \"Hi\""
+	expBody2 := "var X=3\nreturn X"
+	expBody3 := "var X=1\nreturn X\n"
+
+	resource.Test(t, resource.TestCase{
+		Providers: providers(),
+		Steps: []resource.TestStep{
+			{
+				Config: procedureConfig(dbName, schemaName, procName),
+				Check: resource.ComposeTestCheckFunc(
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc", "name", procName),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc", "comment", "Terraform acceptance test"),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc", "statement", expBody2),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc", "arguments.#", "1"),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc", "arguments.0.name", "ARG1"),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc", "arguments.0.type", "VARCHAR"),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc", "execute_as", "OWNER"),
+
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc_simple", "name", procName),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc_simple", "comment", "user-defined function"),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc_simple", "statement", expBody1),
+
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc_complex", "name", procName),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc_complex", "comment", "Proc with 2 args"),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc_complex", "statement", expBody3),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc_complex", "execute_as", "CALLER"),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc_complex", "arguments.#", "2"),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc_complex", "arguments.1.name", "ARG2"),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc_complex", "arguments.1.type", "DATE"),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc_complex", "return_behavior", "IMMUTABLE"),
+					resource.TestCheckResourceAttr("snowflake_procedure.test_proc_complex", "null_input_behavior", "RETURNS NULL ON NULL INPUT"),
+				),
+			},
+		},
+	})
+}
+
+func procedureConfig(db, schema, name string) string {
+	return fmt.Sprintf(`
+	resource "snowflake_database" "test_database" {
+		name    = "%s"
+		comment = "Terraform acceptance test"
+	}
+	
+	resource "snowflake_schema" "test_schema" {
+		name     = "%s"
+		database = snowflake_database.test_database.name
+		comment  = "Terraform acceptance test"
+	}
+
+	resource "snowflake_procedure" "test_proc_simple" {
+		name = "%s"
+		database = snowflake_database.test_database.name
+		schema   = snowflake_schema.test_schema.name
+		return_type = "varchar"
+		statement = "return \"Hi\""
+	}
+
+	resource "snowflake_procedure" "test_proc" {
+		name = "%s"
+		database = snowflake_database.test_database.name
+		schema   = snowflake_schema.test_schema.name
+		arguments {
+			name = "arg1"
+			type = "varchar"
+		}
+		comment = "Terraform acceptance test"
+		return_type = "varchar"
+		statement = "var X=3\nreturn X"
+	}
+
+	resource "snowflake_procedure" "test_proc_complex" {
+		name = "%s"
+		database = snowflake_database.test_database.name
+		schema   = snowflake_schema.test_schema.name
+		arguments {
+			name = "arg1"
+			type = "varchar"
+		}
+		arguments {
+			name = "arg2"
+			type = "DATE"
+		}		
+		comment = "Proc with 2 args"
+		return_type = "VARCHAR"
+		execute_as = "CALLER"
+		return_behavior = "IMMUTABLE"
+		null_input_behavior = "RETURNS NULL ON NULL INPUT"
+		statement = <<EOT
+var X=1
+return X
+EOT
+	}
+	`, db, schema, name, name, name)
+}
diff --git a/pkg/resources/procedure_test.go b/pkg/resources/procedure_test.go
new file mode 100644
index 00000000..4d788f23
--- /dev/null
+++ b/pkg/resources/procedure_test.go
@@ -0,0 +1,110 @@
+package resources_test
+
+import (
+	"database/sql"
+	"testing"
+
+	sqlmock "github.com/DATA-DOG/go-sqlmock"
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/provider"
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/resources"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema"
+
+	. "github.com/chanzuckerberg/terraform-provider-snowflake/pkg/testhelpers"
+	"github.com/stretchr/testify/require"
+)
+
+const procedureBody string = "hi"
+
+func prepDummyProcedureResource(t *testing.T) *schema.ResourceData {
+	argument1 := map[string]interface{}{"name": "data", "type": "varchar"}
+	argument2 := map[string]interface{}{"name": "event_dt", "type": "date"}
+	in := map[string]interface{}{
+		"name":            "my_proc",
+		"database":        "my_db",
+		"schema":          "my_schema",
+		"arguments":       []interface{}{argument1, argument2},
+		"return_type":     "varchar",
+		"return_behavior": "IMMUTABLE",
+		"statement":       procedureBody, //var message = DATA + DATA;return message
+	}
+	d := procedure(t, "my_db|my_schema|my_proc|VARCHAR-DATE", in)
+	return d
+}
+
+func TestProcedure(t *testing.T) {
+	r := require.New(t)
+	err := resources.Procedure().InternalValidate(provider.Provider().Schema, true)
+	r.NoError(err)
+}
+
+func TestProcedureCreate(t *testing.T) {
+	r := require.New(t)
+	d := prepDummyProcedureResource(t)
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		mock.ExpectExec(`CREATE OR REPLACE PROCEDURE "my_db"."my_schema"."my_proc"\(data VARCHAR, event_dt DATE\) RETURNS VARCHAR LANGUAGE javascript CALLED ON NULL INPUT IMMUTABLE COMMENT = 'user-defined function' EXECUTE AS OWNER AS \$\$hi\$\$`).WillReturnResult(sqlmock.NewResult(1, 1))
+		expectProcedureRead(mock)
+		err := resources.CreateProcedure(d, db)
+		r.NoError(err)
+		r.Equal("my_proc", d.Get("name").(string))
+		r.Equal("VARCHAR", d.Get("return_type").(string))
+		r.Equal("user-defined function", d.Get("comment").(string))
+	})
+}
+
+func expectProcedureRead(mock sqlmock.Sqlmock) {
+	rows := sqlmock.NewRows([]string{"created_on", "name", "schema_name", "is_builtin", "is_aggregate", "is_ansi", "min_num_arguments", "max_num_arguments", "arguments", "description", "catalog_name", "is_table_function", "valid_for_clustering", "is_secure"}).
+		AddRow("now", "my_proc", "my_schema", "N", "N", "N", "1", "1", "MY_TEST_FUNCTION(VARCHAR) RETURN VARCHAR", "mock comment", "my_db", "N", "N", "N")
+	mock.ExpectQuery(`SHOW PROCEDURES LIKE 'my_proc' IN SCHEMA "my_db"."my_schema"`).WillReturnRows(rows)
+
+	describeRows := sqlmock.NewRows([]string{"property", "value"}).
+		AddRow("signature", "(data VARCHAR, event_dt DATE)").
+		AddRow("returns", "VARCHAR(123456789)"). // This is how return type is stored in Snowflake DB
+		AddRow("language", "JAVASCRIPT").
+		AddRow("null handling", "CALLED ON NULL INPUT").
+		AddRow("volatility", "IMMUTABLE").
+		AddRow("execute as", "CALLER").
+		AddRow("body", procedureBody)
+
+	mock.ExpectQuery(`DESCRIBE PROCEDURE "my_db"."my_schema"."my_proc"\(VARCHAR, DATE\)`).WillReturnRows(describeRows)
+}
+
+func TestProcedureRead(t *testing.T) {
+	r := require.New(t)
+
+	d := prepDummyProcedureResource(t)
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		expectProcedureRead(mock)
+
+		err := resources.ReadProcedure(d, db)
+		r.NoError(err)
+		r.Equal("my_proc", d.Get("name").(string))
+		r.Equal("user-defined function", d.Get("comment").(string))
+		r.Equal("VARCHAR", d.Get("return_type").(string))
+		r.Equal(procedureBody, d.Get("statement").(string))
+
+		args := d.Get("arguments").([]interface{})
+		r.Len(args, 2)
+		test_proc_arg1 := args[0].(map[string]interface{})
+		test_proc_arg2 := args[1].(map[string]interface{})
+		r.Len(test_proc_arg1, 2)
+		r.Len(test_proc_arg2, 2)
+		r.Equal("data", test_proc_arg1["name"].(string))
+		r.Equal("VARCHAR", test_proc_arg1["type"].(string))
+		r.Equal("event_dt", test_proc_arg2["name"].(string))
+		r.Equal("DATE", test_proc_arg2["type"].(string))
+	})
+}
+
+func TestProcedureDelete(t *testing.T) {
+	r := require.New(t)
+
+	d := prepDummyProcedureResource(t)
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		mock.ExpectExec(`DROP PROCEDURE "my_db"."my_schema"."my_proc"\(VARCHAR, DATE\)`).WillReturnResult(sqlmock.NewResult(1, 1))
+		err := resources.DeleteProcedure(d, db)
+		r.NoError(err)
+	})
+}
diff --git a/pkg/snowflake/procedure_test.go b/pkg/snowflake/procedure_test.go
new file mode 100644
index 00000000..636f9c0c
--- /dev/null
+++ b/pkg/snowflake/procedure_test.go
@@ -0,0 +1,131 @@
+package snowflake
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/require"
+)
+
+func getProcedure(withArgs bool) *ProcedureBuilder {
+	s := Procedure("test_db", "test_schema", "test_proc", []string{})
+	s.WithStatement(`var message = "Hi"` + "\n" + `return message`)
+	s.WithReturnType("varchar")
+	s.WithExecuteAs("CALLER")
+	if withArgs {
+		s.WithArgs([]map[string]string{
+			{"name": "user", "type": "varchar"},
+			{"name": "eventdt", "type": "date"}})
+	}
+	return s
+}
+
+func TestProcedureQualifiedName(t *testing.T) {
+	r := require.New(t)
+	s := getProcedure(true)
+	qn, _ := s.QualifiedName()
+	r.Equal(`"test_db"."test_schema"."test_proc"(VARCHAR, DATE)`, qn)
+	qna, _ := s.QualifiedNameWithoutArguments()
+	r.Equal(`"test_db"."test_schema"."test_proc"`, qna)
+}
+
+func TestProcedureCreate(t *testing.T) {
+	r := require.New(t)
+	s := getProcedure(true)
+
+	r.Equal([]string{"VARCHAR", "DATE"}, s.ArgTypes())
+	createStmnt, _ := s.Create()
+	expected := `CREATE OR REPLACE PROCEDURE "test_db"."test_schema"."test_proc"` +
+		`(user VARCHAR, eventdt DATE) RETURNS VARCHAR LANGUAGE javascript EXECUTE AS CALLER AS $$` +
+		`var message = "Hi"` + "\nreturn message$$"
+	r.Equal(expected, createStmnt)
+}
+
+func TestProcedureCreateWithOptionalParams(t *testing.T) {
+	r := require.New(t)
+	s := getProcedure(true)
+	s.WithNullInputBehavior("RETURNS NULL ON NULL INPUT")
+	s.WithReturnBehavior("IMMUTABLE")
+	s.WithComment("this is cool proc!")
+	createStmnt, _ := s.Create()
+	expected := `CREATE OR REPLACE PROCEDURE "test_db"."test_schema"."test_proc"` +
+		`(user VARCHAR, eventdt DATE) RETURNS VARCHAR LANGUAGE javascript RETURNS NULL ON NULL INPUT` +
+		` IMMUTABLE COMMENT = 'this is cool proc!' EXECUTE AS CALLER AS $$` +
+		`var message = "Hi"` + "\nreturn message$$"
+	r.Equal(expected, createStmnt)
+}
+
+func TestProcedureDrop(t *testing.T) {
+	r := require.New(t)
+
+	// Without arg
+	s := getProcedure(false)
+	stmnt, _ := s.Drop()
+	r.Equal(stmnt, `DROP PROCEDURE "test_db"."test_schema"."test_proc"()`)
+
+	// With arg
+	ss := getProcedure(true)
+	stmnt, _ = ss.Drop()
+	r.Equal(`DROP PROCEDURE "test_db"."test_schema"."test_proc"(VARCHAR, DATE)`, stmnt)
+}
+
+func TestProcedureShow(t *testing.T) {
+	r := require.New(t)
+	s := getProcedure(false)
+	stmnt := s.Show()
+	r.Equal(stmnt, `SHOW PROCEDURES LIKE 'test_proc' IN SCHEMA "test_db"."test_schema"`)
+}
+
+func TestProcedureRename(t *testing.T) {
+	r := require.New(t)
+	s := getProcedure(false)
+
+	stmnt, _ := s.Rename("new_proc")
+	expected := `ALTER PROCEDURE "test_db"."test_schema"."test_proc"() RENAME TO "test_db"."test_schema"."new_proc"`
+	r.Equal(expected, stmnt)
+}
+
+func TestProcedureChangeComment(t *testing.T) {
+	r := require.New(t)
+	s := getProcedure(true)
+
+	stmnt, _ := s.ChangeComment("not used")
+	expected := `ALTER PROCEDURE "test_db"."test_schema"."test_proc"(VARCHAR, DATE) SET COMMENT = 'not used'`
+	r.Equal(expected, stmnt)
+}
+
+func TestProcedureRemoveComment(t *testing.T) {
+	r := require.New(t)
+	s := getProcedure(false)
+
+	stmnt, _ := s.RemoveComment()
+	expected := `ALTER PROCEDURE "test_db"."test_schema"."test_proc"() UNSET COMMENT`
+	r.Equal(expected, stmnt)
+}
+
+func TestProcedureChangeExecuteAs(t *testing.T) {
+	r := require.New(t)
+	s := getProcedure(false)
+
+	stmnt, _ := s.ChangeExecuteAs("OWNER")
+	expected := `ALTER PROCEDURE "test_db"."test_schema"."test_proc"() EXECUTE AS OWNER`
+	r.Equal(expected, stmnt)
+}
+
+func TestProcedureDescribe(t *testing.T) {
+	r := require.New(t)
+	s := getProcedure(false)
+
+	stmnt, _ := s.Describe()
+	expected := `DESCRIBE PROCEDURE "test_db"."test_schema"."test_proc"()`
+	r.Equal(expected, stmnt)
+}
+
+func TestProcedureArgumentsSignature(t *testing.T) {
+	r := require.New(t)
+	s := getProcedure(false)
+	sign, _ := s.ArgumentsSignature()
+	r.Equal("test_proc() RETURN VARCHAR", sign)
+	s = getProcedure(true)
+	sign, _ = s.ArgumentsSignature()
+	r.Equal("test_proc(VARCHAR, DATE) RETURN VARCHAR", sign)
+}