MS SQL: Defining Constants in Transact-SQL

Microsoft SQL Server3 Comments on MS SQL: Defining Constants in Transact-SQL

MS SQL: Defining Constants in Transact-SQL

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();
Ulf Emsoy has long working experience in project management, software development and supply chain management.

3 thoughts on “MS SQL: Defining Constants in Transact-SQL

    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top