Efficient way to retrieve constant values in SQL Server

Question

I have a requirement to store some constants in database which I use to do calculations in SQL stored procedures. I used a table to store these constants

ConstantName(nvarchar)        Value(int)

The problem is that there are many stored procedures that need to access these variables and I have to retrieve the values of all these constants in those stored procedures, which is clearly redundant work. There are nearly 50 constants. I think there must be a better way of doing this, but I do not know of one. Can someone please suggest a nice approach?

Is your code limited to TSQL or are stored procedures called from something else (e.g. .NET) ? — Yuriy Galanter, Aug 20 '13 at 18:25
I am calling them from SSIS. Also, I need a way to make changes at a later stage if required. So I have to store them in db. — trailblazer, Aug 20 '13 at 18:38
Yet another reason [why using stored procedures is a bad idea](http://stackoverflow.com/questions/6368985/mysql-stored-procedures-use-them-or-not-to-use-them/6369030#6369030) — Bohemian, Aug 20 '13 at 20:28

score 3 · Answer 1 · answered Aug 20 '13 at 18:55

3

You can define each constant as scalar function, that returns hardcoded value, e.g.

CREATE FUNCTION MY_INT_CONST()
RETURNS INT
AS
BEGIN
    RETURN 12345
END

Then you can use it in calculations across the all SPs

SELECT dbo.MY_INT_CONST() * 2

answered Aug 20 '13 at 18:55

Yuriy Galanter

38,833
15
69
136

I know this was long ago, but it's interesting. Would it be worth defining a new schema just for storing them? [database].[const].Some_var() – Hecatonchires Dec 09 '20 at 06:11

score 3 · Accepted Answer · answered Aug 20 '13 at 19:01

3

You could create a scalar-valued function to do the "redundant" work:

CREATE FUNCTION getConst( @constName nvarchar(255))
RETURNS INT
AS
BEGIN
    DECLARE @Value int
    SELECT @Value = Value FROM Constants WHERE ConstantName = @constName
    RETURN @Value
END

and call it with SELECT getConst("MyName")

answered Aug 20 '13 at 19:01

D Stanley

149,601
11
178
240

Since OP is using table already, this is better the UDF with hardcoded values. I think it is usually not recommended to perform table operations inside of scalar function, but due to relatively small size of the table this should be ok. – Yuriy Galanter Aug 20 '13 at 19:08
@YuriyGalanter I agree, if the table is small and uses the key as the clustered index it should be lightening fast. – D Stanley Aug 20 '13 at 19:21
Will this be different from getting the values for all the variables in all the stored procs? i.e. instead of the scalar function, what if I do the following: declare \@value int; select @value = value from constants where name = 'Constant Name' for all the constants. – trailblazer Aug 20 '13 at 19:31
I am just guessing that this will not have any performance effects, but it will save me some lines of code. – trailblazer Aug 20 '13 at 19:36
1

I thought that was the "redundant work" you were trying to avoid? The performance difference _should_ be negligible if your constants table is properly indexed. – D Stanley Aug 20 '13 at 19:40

Efficient way to retrieve constant values in SQL Server

2 Answers2