Sql stored procedure multiple where parameters

Stored procedures enable users to create modular code that can include complex business logic by combining multiple SQL statements with procedural logic.

In this Topic:

Creating a Stored Procedure¶

Naming Conventions for Stored Procedures¶

Stored procedures are database objects, meaning that they are created in a specified database and schema. They have a fully-qualified name defined by their namespace, in the form of db.schema.procedure_name, for example:

CALL temporary_db_qualified_names_test.temporary_schema_1.stproc_pi();

When called without their fully-qualified name, stored procedures are resolved according to the database and schema in use for the session.

Overloading of Names¶

Snowflake supports overloading of stored procedure names. Multiple stored procedures in the same schema can have the same name, as long as their signatures differ, either by the number of arguments or the argument types. When an overloaded stored procedure is called, Snowflake checks the arguments and calls the correct stored procedure.

An example is included in Overloading Stored Procedure Names.

Be careful when using overloading. The combination of automatic type conversion and overloading makes it easy for minor user errors to cause unexpected results. For an example, see Overloading Stored Procedure Names.

Potential Conflicts with System-defined Functions and User-defined Functions¶

Stored procedures and user-defined functions can have the same names if they have different numbers of arguments or different data types for arguments.

However, Snowflake does not allow creating stored procedures with the same name as any of the system-defined functions.

Transaction Management¶

Stored procedures are not atomic; if one statement in a stored procedure fails, the other statements in the stored procedure are not necessarily rolled back.

You can use stored procedures with transactions to make a group of statements atomic. For details, see Stored Procedures and Transactions.

General Tips¶

Symmetric Code¶

If you are familiar with programming in assembly language, you might find the following analogy helpful. In assembly language, functions often create and undo their environments in a symmetric way. For example:

-- Set up.
push a;
push b;
...
-- Clean up in the reverse order that you set up.
pop b;
pop a;

You might want to use this approach in your stored procedures:

• If a stored procedure makes temporary changes to your session, then that procedure should undo those changes before returning.

• If a stored procedure utilizes exception handling or branching, or other logic that might impact which statements are executed, you need to clean up whatever you created, regardless of which branches you take during a particular invocation.

For example your code might look similar to the pseudo-code shown below:

create procedure f() ...
    $$
    set x;
    set y;
    try  {
       set z;
       -- Do something interesting...
       ...
       unset z;
       }
    catch  {
       -- Give error message...
       ...
       unset z;
       }
    unset y;
    unset x;
    $$
    ;

Calling a Stored Procedure¶

To execute a stored procedure, use a CALL statement. For example:

call stproc1(5.14::FLOAT);

Each argument to a stored procedure can be a general expression:

CALL stproc1(2 * 5.14::FLOAT);

An argument can be a subquery:

CALL stproc1(SELECT COUNT(*) FROM stproc_test_table1);

You can call only one stored procedure per CALL statement. For example, the following statement fails:

call proc1(1), proc2(2);                          -- Not allowed

Also, you cannot use a stored procedure CALL as part of an expression. For example, all the following statements fail:

call proc1(1) + proc1(2);                         -- Not allowed
call proc1(1) + 1;                                -- Not allowed
call proc1(proc2(x));                             -- Not allowed
select * from (call proc1(1));                    -- Not allowed

However, inside a stored procedure, the stored procedure can call another stored procedure, or call itself recursively.

Caution

Nested calls can exceed the maximum allowed stack depth, so be careful when nesting calls, especially when using recursion.

Privileges¶

Stored Procedures utilize two types of privileges:

• Privileges directly on the stored procedure itself.

• Privileges on the database objects (e.g. tables) that the stored procedure accesses.

Privileges on Stored Procedures¶

Similar to other database objects (tables, views, UDFs, etc.), stored procedures are owned by a role and have one or more privileges that can be granted to other roles.

Currently, the following privileges apply to stored procedures:

• USAGE

• OWNERSHIP

For a role to use a stored procedure, the role must either be the owner or have been granted USAGE privilege on the stored procedure.

Stored Procedure Considerations¶

• Although stored procedures allow nesting and recursion, the current maximum stack depth of nested calls for user-defined stored procedures is 5 (including the top-level stored procedure), and can be less if individual stored procedures in the call chain consume large amounts of resources.

• In rare cases, calling too many stored procedures at the same time can cause a deadlock.

SQL Injection¶

Stored procedures can dynamically create a SQL statement and execute it. However, this can allow SQL injection attacks, particularly if you create the SQL statement using input from a public or untrusted source.

You can minimize the risk of SQL injection attacks by binding parameters rather than concatenating text. For an example of binding variables, see Binding Variables.

If you choose to use concatenation, you should check inputs carefully when constructing SQL dynamically using input from public sources. You might also want to take other precautions, such as querying using a role that has limited privileges (e.g. read-only access, or access to only certain tables or views).

For more information about SQL injection attacks, see SQL injection (in Wikipedia).

Design Tips for Stored Procedures¶

Here are some tips for designing a stored procedure:

• What resources, for example, tables, does this stored procedure need?

• What privileges are needed?

  Think about which database objects will be accessed, and which roles will run your stored procedure, and which privileges those roles will need.

  If the procedure should be a caller’s rights stored procedure, then you might want to create a role to run that specific procedure, or any of a group of related procedures. You can then grant any required privileges to that role, and then grant that role to appropriate users.

• Should the stored procedure run with caller’s rights or owner’s rights? For more information on this topic, see Understanding Caller’s Rights and Owner’s Rights Stored Procedures.

• How should the procedure handle errors, for example what should the procedure do if a required table is missing, or if an argument is invalid?

• Should the stored procedure log its activities or errors, for example by writing to a log table?

• See also the discussion about when to use a stored procedure vs. when to use a UDF: Choosing to Create a Stored Procedure or a UDF.

Documenting Stored Procedures¶

Stored procedures are usually written to be re-used, and often to be shared. Documenting stored procedures can make stored procedures easier to use and easier to maintain.

Below are some general recommendations for documenting stored procedures.

Typically, there are at least two audiences who want to know about a stored procedure:

• Users/callers.

• Programmers/authors.

For users (and programmers), document each of the following:

• The name of the stored procedure.

• The “location” of the stored procedure (database and schema).

• The purpose of the stored procedure.

• The name, data type, and meaning of each input parameter.

• The name, data type, and meaning of the return value. If the return value is a complex type, such as a VARIANT that contains sub-fields, document those sub-fields.

• If the stored procedure relies on information from its environment, for example session variables or session parameters, document the names, purposes, and valid values of those.

• Errors returned, exceptions thrown, etc.

• Roles or privileges required in order to run the procedure. (For more on this topic, see the discussion of roles in Design Tips for Stored Procedures.)

• Whether the procedure is a caller’s rights procedure or an owner’s rights procedure.

• Any prerequisites, for example tables that must exist before the procedure is called.

• Any outputs (besides the return value), for example new tables that are created.

• Any “side-effects”, for example changes in privileges, deletions of old data, etc. Most stored procedures (unlike functions) are called specifically for their side effects, not their return values, so make sure to document those effects.

• If cleanup is required after running the stored procedure, document that cleanup.

• Whether the procedure can be called as part of a multi-statement transaction (with AUTOCOMMIT=FALSE), or whether it should be run outside a transaction (with AUTOCOMMIT=TRUE).

• An example of a call and an example of what is returned.

• Limitations (if applicable). For example, suppose that the procedure reads a table and returns a VARIANT that contains information from each row of the table. It is possible for the VARIANT to grow larger than the maximum legal size of a VARIANT, so you might need to give the caller some idea of the maximum number of rows in the table that the procedure accesses.

• Warnings (if applicable).

• Troubleshooting tips.

For programmers:

• The author(s).

• Explain why the procedure was created as a caller’s rights procedure or an owner’s rights procedure – the reason might not be obvious.

• Stored procedures can be nested, but there is a limit to the depth of the nesting. If your stored procedure calls other stored procedures, and is itself likely to be called by other stored procedures, then you might want to specify the maximum known depth of your stored procedure’s call stack so that callers have some idea of whether calling your stored procedure might exceed the maximum call stack depth.

• Debugging tips.

The location and format of this information are up to you. You might store the information in HTML format in an internal web site, for example. Before deciding where to store it, think about where your organization stores similar information for other products, or similar information for other Snowflake features, such as views, user-defined functions, etc.

Other tips:

• Include comments in the source code, as you should for almost any piece of source code.

  • Remember that reverse engineering meaning from code is difficult. Describe not only how your algorithm works, but also the purpose of that algorithm.

• Stored procedures allow an optional COMMENT that can be specified with the CREATE PROCEDURE or ALTER PROCEDURE statement. Other people can read this comment by running the SHOW PROCEDURES command.

• If practical, consider keeping a master copy of each stored procedure’s CREATE PROCEDURE command in a source code control system. Snowflake’s Time Travel feature does not apply to stored procedures, so looking up old versions of stored procedures must be done outside Snowflake. If a source code control system is not available, you can partly simulate one by storing the CREATE PROCEDURE commands in a VARCHAR field in a table, and adding each new version (without replacing the older version(s)).

• Consider using a naming convention to help provide information about stored procedures. For example, a prefix or suffix in the name might indicate whether the procedure is a caller’s rights stored procedure or an owner’s rights stored procedure. (E.g. you could use cr_ as a prefix for Caller’s Rights.)

• To see the data types and order of the input arguments, as well as the comment, you can use the SHOW PROCEDURES command. Remember, however, that this shows only the names and data types of the arguments; it does not explain the arguments.

• If you have appropriate privileges, you can use the DESCRIBE PROCEDURE command to see:

  • The names and data types of the arguments.

  • The body of the procedure, and whether the procedure executes as owner or caller.

  • The data type of the return value.

  • Other useful information.

Can a stored procedure have multiple parameters?

Can SQL stored procedure have multiple output parameters?

Can pass 3 types of parameters to stored procedures What are they?

How pass multiple parameters in SQL query?