CODEWITHSUNDEEP
c#sqlconstants
Icerman
Icerman

Reputation: 1119

Best practice for SQL constant values

In C# or any other similar languages, if we use a magic number, it is bad practice. How about SQL? I have seen this type of SQL a lot:

CREATE PROCEDURE ProcessOrder
    @productTypeId   INT
    , @productName   NVARCHAR(50)  
AS
BEGIN

    IF (@productType = 3) -- Electronic product type 
        -- Handle electronic
    ELSE IF (@productType = 4) -- Other product type     

END

The user call this with an ENUM which converted to INT. Assuming ProductType table (3, 'Electronic') exist. What should be the best practice here?

Upvotes: 2

Views: 1865

Answers (4)

usr-local-ΕΨΗΕΛΩΝ
usr-local-ΕΨΗΕΛΩΝ

Reputation: 26874

Magic numbers are surely a bad antipattern. However constants that map to magic numbers are a pattern. Like

if (productType == 3) //Bad
if (productType == PRODUCT_ELECTRO) //Good even while PRODUCT_ELECTRO=3

A good practice in SQL is using lookup tables!

   PRODUCT TABLE
 TYPE       DESCRIPTION
 1          FOOD
 2          ELECTRONIC
 3          HOUSE

And then define your entities with a FOREIGN KEY to this table.

Example

CREATE TABLE PRODUCT_TYPES (PRODUCT_TYPE_ID NUMBER PRIMARY KEY, PRODUCT_TYPE_DESCRIPTION VARCHAR2);

CREATE TABLE PRODUCTS (PRODUCT_ID NUMBER PRIMARY KEY, PRODUCT_TYPE NUMBER NOT NULL REFERENCES PRODUCT_TYPES(PRODUCT_TYPE_ID), BLA BLA BLA..........);

In your code you can define constants like

public class ProductTypes {
    public const int FOOD_PRODUCT = 1;
    ......
}

Example query (please don't remind me that this pattern is unsafe :))

public Product[] getElectronicProducts() { ... //init connection bla bla bla Command.CommandText = String.Format("SELECT * FROM PRODUCTS WHERE PRODUCT_TYPE = {0}",ProductTypes.ELECTRONIC_PRODUCT);

//Produces "SELECT * FROM PRODUCTS WHERE PRODUCT_TYPE = 3"

... //do the query and return

}

Upvotes: 0

JackAce
JackAce

Reputation: 1405

You can always use functions.

CREATE FUNCTION
    [dbo].PRODUCT_ELECTRO()
RETURNS INT
AS
BEGIN
        RETURN 3
END


-- This returns the value 3
SELECT
    dbo.PRODUCT_ELECTRO()

IF @MyValue = dbo.PRODUCT_ELECTRO()
BEGIN
    PRINT 'The value is tres'
END

Upvotes: 2

JotaBe
JotaBe

Reputation: 39015

Using enums with values it's a good design.

Adding a lookup table offers two advantages:

1: you can add referential integrity

2: someone trying to guess what those magic numbers will find the lookup table and clear his doubts

The most important part on the application code is your use of enums. You can even use attributes to assign string keys to enum values.

So let me recommend using a table which instead of "magic numbers" have somewhat menaingful strings. (4 chars = 4 bytes, just like an int key - 1 char is 1 byte, just like a tinyint)

You could have a lookup table like this:

Key      Value
'Elec'   'Electronic'
'Othr'   'Other'

using the same space as if you had an int key.

this makes easier to read your SQL queries.

Upvotes: 0

Shyju
Shyju

Reputation: 218732

If you have a look up table, i would like to have a column called "Code" for each record and keep it unique. so i will always join the lookup table as well and use that string value instead of checking it with an ID field. and the string value can be some thing meaningful( Ex: ELE_PROD_TYPE) and human readable. 

IF (@productTypeCode = 'ELE_PROD_TYPE')

Upvotes: 0

Related Questions