improve support for T-SQL EXECUTE statements (#1490)
diff --git a/src/ast/mod.rs b/src/ast/mod.rs
index a24739a..31d7af1 100644
--- a/src/ast/mod.rs
+++ b/src/ast/mod.rs
@@ -3113,10 +3113,14 @@
/// EXECUTE name [ ( parameter [, ...] ) ] [USING <expr>]
/// ```
///
- /// Note: this is a PostgreSQL-specific statement.
+ /// Note: this statement is supported by Postgres and MSSQL, with slight differences in syntax.
+ ///
+ /// Postgres: <https://www.postgresql.org/docs/current/sql-execute.html>
+ /// MSSQL: <https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure>
Execute {
- name: Ident,
+ name: ObjectName,
parameters: Vec<Expr>,
+ has_parentheses: bool,
using: Vec<Expr>,
},
/// ```sql
@@ -4585,12 +4589,19 @@
Statement::Execute {
name,
parameters,
+ has_parentheses,
using,
} => {
- write!(f, "EXECUTE {name}")?;
- if !parameters.is_empty() {
- write!(f, "({})", display_comma_separated(parameters))?;
- }
+ let (open, close) = if *has_parentheses {
+ ("(", ")")
+ } else {
+ (if parameters.is_empty() { "" } else { " " }, "")
+ };
+ write!(
+ f,
+ "EXECUTE {name}{open}{}{close}",
+ display_comma_separated(parameters),
+ )?;
if !using.is_empty() {
write!(f, " USING {}", display_comma_separated(using))?;
};
diff --git a/src/parser/mod.rs b/src/parser/mod.rs
index 2bd4543..942ff19 100644
--- a/src/parser/mod.rs
+++ b/src/parser/mod.rs
@@ -529,7 +529,7 @@
// `PREPARE`, `EXECUTE` and `DEALLOCATE` are Postgres-specific
// syntaxes. They are used for Postgres prepared statement.
Keyword::DEALLOCATE => self.parse_deallocate(),
- Keyword::EXECUTE => self.parse_execute(),
+ Keyword::EXECUTE | Keyword::EXEC => self.parse_execute(),
Keyword::PREPARE => self.parse_prepare(),
Keyword::MERGE => self.parse_merge(),
// `LISTEN` and `NOTIFY` are Postgres-specific
@@ -11807,11 +11807,20 @@
}
pub fn parse_execute(&mut self) -> Result<Statement, ParserError> {
- let name = self.parse_identifier(false)?;
+ let name = self.parse_object_name(false)?;
- let mut parameters = vec![];
- if self.consume_token(&Token::LParen) {
- parameters = self.parse_comma_separated(Parser::parse_expr)?;
+ let has_parentheses = self.consume_token(&Token::LParen);
+
+ let end_token = match (has_parentheses, self.peek_token().token) {
+ (true, _) => Token::RParen,
+ (false, Token::EOF) => Token::EOF,
+ (false, Token::Word(w)) if w.keyword == Keyword::USING => Token::Word(w),
+ (false, _) => Token::SemiColon,
+ };
+
+ let parameters = self.parse_comma_separated0(Parser::parse_expr, end_token)?;
+
+ if has_parentheses {
self.expect_token(&Token::RParen)?;
}
@@ -11827,6 +11836,7 @@
Ok(Statement::Execute {
name,
parameters,
+ has_parentheses,
using,
})
}
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
index 49753a1..e372806 100644
--- a/tests/sqlparser_common.rs
+++ b/tests/sqlparser_common.rs
@@ -1396,6 +1396,10 @@
])
}
+fn ms_and_generic() -> TestedDialects {
+ TestedDialects::new(vec![Box::new(MsSqlDialect {}), Box::new(GenericDialect {})])
+}
+
#[test]
fn parse_json_ops_without_colon() {
use self::BinaryOperator::*;
@@ -9736,6 +9740,41 @@
}
#[test]
+fn parse_execute_stored_procedure() {
+ let expected = Statement::Execute {
+ name: ObjectName(vec![
+ Ident {
+ value: "my_schema".to_string(),
+ quote_style: None,
+ },
+ Ident {
+ value: "my_stored_procedure".to_string(),
+ quote_style: None,
+ },
+ ]),
+ parameters: vec![
+ Expr::Value(Value::NationalStringLiteral("param1".to_string())),
+ Expr::Value(Value::NationalStringLiteral("param2".to_string())),
+ ],
+ has_parentheses: false,
+ using: vec![],
+ };
+ assert_eq!(
+ // Microsoft SQL Server does not use parentheses around arguments for EXECUTE
+ ms_and_generic()
+ .verified_stmt("EXECUTE my_schema.my_stored_procedure N'param1', N'param2'"),
+ expected
+ );
+ assert_eq!(
+ ms_and_generic().one_statement_parses_to(
+ "EXEC my_schema.my_stored_procedure N'param1', N'param2';",
+ "EXECUTE my_schema.my_stored_procedure N'param1', N'param2'",
+ ),
+ expected
+ );
+}
+
+#[test]
fn parse_create_table_collate() {
pg_and_generic().verified_stmt("CREATE TABLE tbl (foo INT, bar TEXT COLLATE \"de_DE\")");
}
diff --git a/tests/sqlparser_postgres.rs b/tests/sqlparser_postgres.rs
index c30603b..100c8ee 100644
--- a/tests/sqlparser_postgres.rs
+++ b/tests/sqlparser_postgres.rs
@@ -1539,8 +1539,9 @@
assert_eq!(
stmt,
Statement::Execute {
- name: "a".into(),
+ name: ObjectName(vec!["a".into()]),
parameters: vec![],
+ has_parentheses: false,
using: vec![]
}
);
@@ -1549,11 +1550,12 @@
assert_eq!(
stmt,
Statement::Execute {
- name: "a".into(),
+ name: ObjectName(vec!["a".into()]),
parameters: vec![
Expr::Value(number("1")),
Expr::Value(Value::SingleQuotedString("t".to_string()))
],
+ has_parentheses: true,
using: vec![]
}
);
@@ -1563,8 +1565,9 @@
assert_eq!(
stmt,
Statement::Execute {
- name: "a".into(),
+ name: ObjectName(vec!["a".into()]),
parameters: vec![],
+ has_parentheses: false,
using: vec