Is there a way to make a TSQL variable constant?

Question

Is there a way to make a TSQL variable constant?

Answer 1

No, but you can create a function and hardcode it in there and use that.

Here is an example:

CREATE FUNCTION fnConstant() RETURNS INT AS BEGIN     RETURN 2 END GO  SELECT dbo.fnConstant() 

Answer 2

One solution, offered by Jared Ko is to use pseudo-constants.

As explained in SQL Server: Variables, Parameters or Literals? Or… Constants?:

Pseudo-Constants are not variables or parameters. Instead, they're simply views with one row, and enough columns to support your constants. With these simple rules, the SQL Engine completely ignores the value of the view but still builds an execution plan based on its value. The execution plan doesn't even show a join to the view!

Create like this:

CREATE SCHEMA ShipMethod GO -- Each view can only have one row. -- Create one column for each desired constant. -- Each column is restricted to a single value. CREATE VIEW ShipMethod.ShipMethodID AS SELECT CAST(1 AS INT) AS [XRQ - TRUCK GROUND]       ,CAST(2 AS INT) AS [ZY - EXPRESS]       ,CAST(3 AS INT) AS [OVERSEAS - DELUXE]       ,CAST(4 AS INT) AS [OVERNIGHT J-FAST]       ,CAST(5 AS INT) AS [CARGO TRANSPORT 5] 

Then use like this:

SELECT h.* FROM Sales.SalesOrderHeader h JOIN ShipMethod.ShipMethodID const     ON h.ShipMethodID = const.[OVERNIGHT J-FAST] 

Or like this:

SELECT h.* FROM Sales.SalesOrderHeader h WHERE h.ShipMethodID = (SELECT TOP 1 [OVERNIGHT J-FAST] FROM ShipMethod.ShipMethodID)