Database Reference
In-Depth Information
All natively-compiled stored procedures are schema-bound, and they require you to specify the
SCHEMABINDING
option. Finally, setting the execution context is a requirement. Natively-compiled stored procedures do not support
the
EXECUTE AS CALLER
security context and require you to specify
EXECUTE AS OWNER
,
EXECUTE AS USER
, or
EXECUTE
AS SELF
context in the definition.
■
Note
As you have already seen in Listing 33-2, you can specify the required parameters by using the
NOT NULL
construct in the parameter's definition. SQL Server raises an error if you do not provide their values at the time of the
stored procedure call.
Finally, it is recommended that you avoid type conversion and do not use named parameters when you call
natively-compiled stored procedures. It is more efficient to use the:
exec Proc value [..,value]
rather than the
exec Proc @Param=value [..,@Param=value]
calling format.
■
Note
you can detect inefficient parameterization with
hekaton_slow_parameter_parsing
extended event.
Supported T-SQL Features
Natively-compiled stored procedures support only a limited set of T-SQL constructs. Let's look at the supported
features in different areas.
Control Flow
The following control flow options are supported:
IF
and
WHILE
Assigning a value to a variable with the
SELECT
and
SET
operators.
RETURN
TRY
/
CATCH
/
THROW
(
RAISERROR
is not supported). It is recommended that you use a single
TRY
/
CATCH
block for the entire stored procedure for better performance.
It is possible to declare variables as
NOT NULL
as long as they have an initializer as part of
the
DECLARE
statement.
Operators
The following operators are supported:
Comparison operators, such as
=
,
<
,
<=
,
>
,
>=
,
<>
.
Unary and binary operators, such as
+
,
-
,
*
,
/
,
%
.
+
operators are supported for both
numbers and strings.
Bitwise operators, such as
&
,
|
,
~
,
^
.
Logical operators, such as
AND
,
OR
,
and
NOT
.
However, the
OR
and
NOT
operators are not
supported in the
WHERE
and
HAVING
clauses of the query.
