Transact-SQL does not offer a good way to support constants in your stored procedures or userdefined functions. This means that you either can choose to hardcode your constants or, the more elegant way, define a userdefined function for each “constant” value. To ensure optimal performance, the schemabinding keyword should be used.
Here is an example:
CREATE FUNCTION [dbo].[sudf_Security_Privilege_User]
(
)
RETURNS INT
with schemabinding
AS
BEGIN
-- Return the access level
RETURN (1);
END
In your stored procedure you can call the function the normal way, i.e.
set x = dbo.sudf_Security_Privilege_User();
RED GATE do not agree: https://www.red-gate.com/hub/product-learning/sql-prompt/misuse-scalar-user-defined-function-constant-pe017
or maybe since it is schema bound, this is ok?
Hi Evan,
Thank you for your comment. The concept of constants does not really exist in Transact-SQL, so it is an approximation. As the referenced article mentions, there will be an overhead when using user-defined functions. However, using the “with schemabinding” keyword the function will only be evaluated once for each query.