Is there any way to declare constants file in Sql Server?

user_13939310 0
Viewed 545

I want to know if there are any provisions in SQL server where we can store the constants variables either in some file or else just like codeigniter's constants.php file from where we can define constant variables and whenever we need we can call the variable.

I want to use these feature as because i have multiple views, stored procedures which takes static values and whenever these values changes in database i can just change those values in constant and my job is done. I am ok to create any XML or other file and call it in SP,View wherever i needed.

Thank you in advance.

sql-server
2 Answers

This is not possible at the server level but you can do this if you have all the views in a single .SQL file (if you want to, several batches in a single .SQL script). You will need to use SQLCMD mode in SSMS. To enable SQLCMD mode go to Query > SQLCMD Mode

enter image description here

Once you have SQLCMD mode enabled you can define constants/variables at the top of your SQL script and use them throughout the different views in your .SQL file.

To define the variables/constants

:setvar cons1 10
:setvar cons2 20

And to use them

$(cons1)
$(cons2)
user_23443940

A "constants file" is really a list of key/value pairs

const key = "value"

Translated into SQL, a list of key/value pairs is a table

Key varchar() [UK]
IntValue int null
StringValue nvarchar() null

etc. for the supported data types.

Reference the values using

SELECT [ValueColumn] FROM [SettingsTable] WHERE Key = '[Key]'

Since the result is a NULLable scalar, you can use this expression to read a "constant" value in a sub-SELECT.

If your constants are really really constant, you can also define a view as

CREATE VIEW V_SystemConstants AS
SELECT 42 AS TheAnswer
devio_213360
Related