sqlparser/dialect/
mssql.rs

1// Licensed to the Apache Software Foundation (ASF) under one
2// or more contributor license agreements.  See the NOTICE file
3// distributed with this work for additional information
4// regarding copyright ownership.  The ASF licenses this file
5// to you under the Apache License, Version 2.0 (the
6// "License"); you may not use this file except in compliance
7// with the License.  You may obtain a copy of the License at
8//
9//   http://www.apache.org/licenses/LICENSE-2.0
10//
11// Unless required by applicable law or agreed to in writing,
12// software distributed under the License is distributed on an
13// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14// KIND, either express or implied.  See the License for the
15// specific language governing permissions and limitations
16// under the License.
17
18use crate::ast::helpers::attached_token::AttachedToken;
19use crate::ast::{
20    BeginEndStatements, ConditionalStatementBlock, ConditionalStatements, CreateTrigger,
21    GranteesType, IfStatement, Statement,
22};
23use crate::dialect::Dialect;
24use crate::keywords::Keyword;
25use crate::parser::{Parser, ParserError};
26use crate::tokenizer::Token;
27#[cfg(not(feature = "std"))]
28use alloc::{vec, vec::Vec};
29
30/// A [`Dialect`] for [Microsoft SQL Server](https://www.microsoft.com/en-us/sql-server/)
31#[derive(Debug, Default, Clone, Copy, PartialEq, Eq, Hash, PartialOrd, Ord)]
32#[cfg_attr(feature = "serde", derive(serde::Serialize, serde::Deserialize))]
33pub struct MsSqlDialect {}
34
35impl Dialect for MsSqlDialect {
36    fn is_delimited_identifier_start(&self, ch: char) -> bool {
37        ch == '"' || ch == '['
38    }
39
40    fn is_identifier_start(&self, ch: char) -> bool {
41        // See https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-2017#rules-for-regular-identifiers
42        ch.is_alphabetic() || ch == '_' || ch == '#' || ch == '@'
43    }
44
45    fn is_identifier_part(&self, ch: char) -> bool {
46        ch.is_alphabetic()
47            || ch.is_ascii_digit()
48            || ch == '@'
49            || ch == '$'
50            || ch == '#'
51            || ch == '_'
52    }
53
54    fn identifier_quote_style(&self, _identifier: &str) -> Option<char> {
55        Some('[')
56    }
57
58    /// SQL Server has `CONVERT(type, value)` instead of `CONVERT(value, type)`
59    /// <https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16>
60    fn convert_type_before_value(&self) -> bool {
61        true
62    }
63
64    fn supports_outer_join_operator(&self) -> bool {
65        true
66    }
67
68    /// SQL Server supports `$` as a prefix for money literals
69    /// <https://learn.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql?view=sql-server-ver17#money-constants>
70    fn supports_dollar_as_money_prefix(&self) -> bool {
71        true
72    }
73
74    fn supports_create_proc_syntax(&self) -> bool {
75        true
76    }
77
78    fn supports_connect_by(&self) -> bool {
79        true
80    }
81
82    fn supports_eq_alias_assignment(&self) -> bool {
83        true
84    }
85
86    fn supports_try_convert(&self) -> bool {
87        true
88    }
89
90    /// In MSSQL, there is no boolean type, and `true` and `false` are valid column names
91    fn supports_boolean_literals(&self) -> bool {
92        false
93    }
94
95    fn supports_named_fn_args_with_colon_operator(&self) -> bool {
96        true
97    }
98
99    fn supports_named_fn_args_with_expr_name(&self) -> bool {
100        true
101    }
102
103    fn supports_named_fn_args_with_rarrow_operator(&self) -> bool {
104        false
105    }
106
107    fn supports_start_transaction_modifier(&self) -> bool {
108        true
109    }
110
111    fn supports_end_transaction_modifier(&self) -> bool {
112        true
113    }
114
115    /// See: <https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql>
116    fn supports_set_stmt_without_operator(&self) -> bool {
117        true
118    }
119
120    /// See: <https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table>
121    fn supports_table_versioning(&self) -> bool {
122        true
123    }
124
125    /// See <https://learn.microsoft.com/en-us/sql/t-sql/language-elements/slash-star-comment-transact-sql?view=sql-server-ver16>
126    fn supports_nested_comments(&self) -> bool {
127        true
128    }
129
130    /// See <https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql>
131    fn supports_object_name_double_dot_notation(&self) -> bool {
132        true
133    }
134
135    /// See <https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles>
136    fn get_reserved_grantees_types(&self) -> &[GranteesType] {
137        &[GranteesType::Public]
138    }
139
140    fn is_select_item_alias(&self, explicit: bool, kw: &Keyword, parser: &mut Parser) -> bool {
141        match kw {
142            // List of keywords that cannot be used as select item (column) aliases in MSSQL
143            // regardless of whether the alias is explicit or implicit.
144            //
145            // These are T-SQL statement-starting keywords; allowing them as implicit aliases
146            // causes the parser to consume the keyword as an alias for the previous expression,
147            // then fail on the token that follows (e.g. `TABLE`, `@var`, `sp_name`, …).
148            Keyword::IF
149            | Keyword::ELSE
150            | Keyword::DECLARE
151            | Keyword::EXEC
152            | Keyword::EXECUTE
153            | Keyword::INSERT
154            | Keyword::UPDATE
155            | Keyword::DELETE
156            | Keyword::DROP
157            | Keyword::CREATE
158            | Keyword::ALTER
159            | Keyword::TRUNCATE
160            | Keyword::PRINT
161            | Keyword::WHILE
162            | Keyword::RETURN
163            | Keyword::THROW
164            | Keyword::RAISERROR
165            | Keyword::MERGE => false,
166            _ => explicit || self.is_column_alias(kw, parser),
167        }
168    }
169
170    fn is_table_factor_alias(&self, explicit: bool, kw: &Keyword, parser: &mut Parser) -> bool {
171        match kw {
172            // List of keywords that cannot be used as table aliases in MSSQL
173            // regardless of whether the alias is explicit or implicit.
174            //
175            // These are T-SQL statement-starting keywords. Without blocking them here,
176            // a bare `SELECT * FROM t` followed by a newline and one of these keywords
177            // would cause the parser to consume the keyword as a table alias for `t`,
178            // then fail on the token that follows (e.g. `@var`, `sp_name`, `TABLE`, …).
179            //
180            // `SET` is already covered by the global `RESERVED_FOR_TABLE_ALIAS` list;
181            // the keywords below are MSSQL-specific additions.
182            Keyword::IF
183            | Keyword::ELSE
184            | Keyword::DECLARE
185            | Keyword::EXEC
186            | Keyword::EXECUTE
187            | Keyword::INSERT
188            | Keyword::UPDATE
189            | Keyword::DELETE
190            | Keyword::DROP
191            | Keyword::CREATE
192            | Keyword::ALTER
193            | Keyword::TRUNCATE
194            | Keyword::PRINT
195            | Keyword::WHILE
196            | Keyword::RETURN
197            | Keyword::THROW
198            | Keyword::RAISERROR
199            | Keyword::MERGE => false,
200            _ => explicit || self.is_table_alias(kw, parser),
201        }
202    }
203
204    fn parse_statement(&self, parser: &mut Parser) -> Option<Result<Statement, ParserError>> {
205        if parser.parse_keyword(Keyword::BEGIN) {
206            // Check if this is a BEGIN...END block rather than BEGIN TRANSACTION
207            let is_block = parser
208                .maybe_parse(|p| {
209                    if p.parse_transaction_modifier().is_some()
210                        || p.parse_one_of_keywords(&[
211                            Keyword::TRANSACTION,
212                            Keyword::WORK,
213                            Keyword::TRAN,
214                        ])
215                        .is_some()
216                        || matches!(p.peek_token_ref().token, Token::SemiColon | Token::EOF)
217                    {
218                        p.expected_ref("statement", p.peek_token_ref())
219                    } else {
220                        Ok(())
221                    }
222                })
223                .unwrap_or(None)
224                .is_some();
225            if is_block {
226                Some(parser.parse_begin_exception_end())
227            } else {
228                parser.prev_token();
229                None
230            }
231        } else if parser.peek_keyword(Keyword::IF) {
232            Some(self.parse_if_stmt(parser))
233        } else if parser.parse_keywords(&[Keyword::CREATE, Keyword::TRIGGER]) {
234            Some(self.parse_create_trigger(parser, false))
235        } else if parser.parse_keywords(&[
236            Keyword::CREATE,
237            Keyword::OR,
238            Keyword::ALTER,
239            Keyword::TRIGGER,
240        ]) {
241            Some(self.parse_create_trigger(parser, true))
242        } else {
243            None
244        }
245    }
246
247    fn get_next_precedence(&self, parser: &Parser) -> Option<Result<u8, ParserError>> {
248        let token = parser.peek_token_ref();
249        match &token.token {
250            // lowest prec to prevent it from turning into a binary op
251            Token::Colon => Some(Ok(self.prec_unknown())),
252            _ => None,
253        }
254    }
255}
256
257impl MsSqlDialect {
258    /// ```sql
259    /// IF boolean_expression
260    ///     { sql_statement | statement_block }
261    /// [ ELSE
262    ///     { sql_statement | statement_block } ]
263    /// ```
264    fn parse_if_stmt(&self, parser: &mut Parser) -> Result<Statement, ParserError> {
265        let if_token = parser.expect_keyword(Keyword::IF)?;
266
267        let condition = parser.parse_expr()?;
268
269        let if_block = if parser.peek_keyword(Keyword::BEGIN) {
270            let begin_token = parser.expect_keyword(Keyword::BEGIN)?;
271            let statements = self.parse_statement_list(parser, Some(Keyword::END))?;
272            let end_token = parser.expect_keyword(Keyword::END)?;
273            ConditionalStatementBlock {
274                start_token: AttachedToken(if_token),
275                condition: Some(condition),
276                then_token: None,
277                conditional_statements: ConditionalStatements::BeginEnd(BeginEndStatements {
278                    begin_token: AttachedToken(begin_token),
279                    statements,
280                    end_token: AttachedToken(end_token),
281                }),
282            }
283        } else {
284            let stmt = parser.parse_statement()?;
285            ConditionalStatementBlock {
286                start_token: AttachedToken(if_token),
287                condition: Some(condition),
288                then_token: None,
289                conditional_statements: ConditionalStatements::Sequence {
290                    statements: vec![stmt],
291                },
292            }
293        };
294
295        let mut prior_statement_ended_with_semi_colon = false;
296        while let Token::SemiColon = parser.peek_token_ref().token {
297            parser.advance_token();
298            prior_statement_ended_with_semi_colon = true;
299        }
300
301        let mut else_block = None;
302        if parser.peek_keyword(Keyword::ELSE) {
303            let else_token = parser.expect_keyword(Keyword::ELSE)?;
304            if parser.peek_keyword(Keyword::BEGIN) {
305                let begin_token = parser.expect_keyword(Keyword::BEGIN)?;
306                let statements = self.parse_statement_list(parser, Some(Keyword::END))?;
307                let end_token = parser.expect_keyword(Keyword::END)?;
308                else_block = Some(ConditionalStatementBlock {
309                    start_token: AttachedToken(else_token),
310                    condition: None,
311                    then_token: None,
312                    conditional_statements: ConditionalStatements::BeginEnd(BeginEndStatements {
313                        begin_token: AttachedToken(begin_token),
314                        statements,
315                        end_token: AttachedToken(end_token),
316                    }),
317                });
318            } else {
319                let stmt = parser.parse_statement()?;
320                else_block = Some(ConditionalStatementBlock {
321                    start_token: AttachedToken(else_token),
322                    condition: None,
323                    then_token: None,
324                    conditional_statements: ConditionalStatements::Sequence {
325                        statements: vec![stmt],
326                    },
327                });
328            }
329        } else if prior_statement_ended_with_semi_colon {
330            parser.prev_token();
331        }
332
333        Ok(IfStatement {
334            if_block,
335            else_block,
336            elseif_blocks: Vec::new(),
337            end_token: None,
338        }
339        .into())
340    }
341
342    /// Parse `CREATE TRIGGER` for [MsSql]
343    ///
344    /// [MsSql]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql
345    fn parse_create_trigger(
346        &self,
347        parser: &mut Parser,
348        or_alter: bool,
349    ) -> Result<Statement, ParserError> {
350        let name = parser.parse_object_name(false)?;
351        parser.expect_keyword_is(Keyword::ON)?;
352        let table_name = parser.parse_object_name(false)?;
353        let period = parser.parse_trigger_period()?;
354        let events = parser.parse_comma_separated(Parser::parse_trigger_event)?;
355
356        parser.expect_keyword_is(Keyword::AS)?;
357        let statements = Some(parser.parse_conditional_statements(&[Keyword::END])?);
358
359        Ok(CreateTrigger {
360            or_alter,
361            temporary: false,
362            or_replace: false,
363            is_constraint: false,
364            name,
365            period: Some(period),
366            period_before_table: false,
367            events,
368            table_name,
369            referenced_table_name: None,
370            referencing: Vec::new(),
371            trigger_object: None,
372            condition: None,
373            exec_body: None,
374            statements_as: true,
375            statements,
376            characteristics: None,
377        }
378        .into())
379    }
380
381    /// Parse a sequence of statements, optionally separated by semicolon.
382    ///
383    /// Stops parsing when reaching EOF or the given keyword.
384    fn parse_statement_list(
385        &self,
386        parser: &mut Parser,
387        terminal_keyword: Option<Keyword>,
388    ) -> Result<Vec<Statement>, ParserError> {
389        let mut stmts = Vec::new();
390        loop {
391            if let Token::EOF = parser.peek_token_ref().token {
392                break;
393            }
394            if let Some(term) = terminal_keyword {
395                if parser.peek_keyword(term) {
396                    break;
397                }
398            }
399            stmts.push(parser.parse_statement()?);
400            while let Token::SemiColon = parser.peek_token_ref().token {
401                parser.advance_token();
402            }
403        }
404        Ok(stmts)
405    }
406}
sqlparser/dialect/mssql.rs

sqlparser/dialect/
mssql.rs
