none
How to define a constant in TSQL ?

    Question

  • I need to define a group of int constants and use them by a group of functions . 

    so how I define a constant ?

    and how to make it accessible by multiple functions ?

     

    Wednesday, August 13, 2014 10:49 PM

Answers

  • You have to put the constants into a table.

    Such a table commonly referred to as a lookup table.

    Example for lookup table:

    /****** Script for SelectTopNRows command from SSMS  ******/
    SELECT TOP 1000 [ShiftID]
          ,[Name]
          ,[StartTime]
          ,[EndTime]
          ,[ModifiedDate]
      FROM [AdventureWorks].[HumanResources].[Shift]
    /*
    ShiftID	Name	StartTime	EndTime	ModifiedDate
    1	Day	1900-01-01 07:00:00.000	1900-01-01 15:00:00.000	1998-06-01 00:00:00.000
    2	Evening	1900-01-01 15:00:00.000	1900-01-01 23:00:00.000	1998-06-01 00:00:00.000
    3	Night	1900-01-01 23:00:00.000	1900-01-01 07:00:00.000	1998-06-01 00:00:00.000
    */


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012









    Wednesday, August 13, 2014 11:11 PM
    Moderator

All replies

  • You have to put the constants into a table.

    Such a table commonly referred to as a lookup table.

    Example for lookup table:

    /****** Script for SelectTopNRows command from SSMS  ******/
    SELECT TOP 1000 [ShiftID]
          ,[Name]
          ,[StartTime]
          ,[EndTime]
          ,[ModifiedDate]
      FROM [AdventureWorks].[HumanResources].[Shift]
    /*
    ShiftID	Name	StartTime	EndTime	ModifiedDate
    1	Day	1900-01-01 07:00:00.000	1900-01-01 15:00:00.000	1998-06-01 00:00:00.000
    2	Evening	1900-01-01 15:00:00.000	1900-01-01 23:00:00.000	1998-06-01 00:00:00.000
    3	Night	1900-01-01 23:00:00.000	1900-01-01 07:00:00.000	1998-06-01 00:00:00.000
    */


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012









    Wednesday, August 13, 2014 11:11 PM
    Moderator
  • Hi, 

    I am guessing that you are developer by your nickname, so i will add something for developers :-)

    This is actually a good question and the answer is that you can use database (and this is where this forum come to help you, and Kalman Toth gave you the basic answer), but if your need is for application that you are developing then there are several more option which you need to check what is best for you.

    define a group of int constants in application can be done:

    >> Using database table (best for big collection or a Collection that you need some changing)
    >> Using ini file (very useful for constant Collection)
    >> Using app config file (useful for constant Collection, remember that changing the file will restart the app and all users sessions will end in some cases)
    >> Using static class

    and so on
    I hope this is useful :-)


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, August 13, 2014 11:29 PM
    Moderator