是否有一种方法可以以编程方式 (在T-SQL中) 检索数据类型的最大值(和最小值)?就像C#中的 float.MaxValue 一样。
当参数不等于数据库中的任何实际值时,我想在某些选择中使用它,因此我将使用类似
declare @min float declare @max float --fill @min and @max, can be null if undefined select * from foo where bar between isnull(@min,0 ) and isnull(@max,max(float)/*magic*/)
尽管似乎没有任何内联方法来获取最小值或最大值,但是有人可以将其组合在一起:
CREATE TABLE datatype_extrema (min_bit bit NOT NULL DEFAULT (0) CHECK (min_Bit=0) ,max_bit AS CAST(0x1 AS bit) ,min_tinyint AS CAST(0x00 AS tinyint) ,max_tinyint AS CAST(0xFF AS tinyint) ,min_smallint AS CAST(0x8000 AS smallint) ,max_smallint AS CAST(0x7FFF AS smallint) ,min_int AS CAST(0x80000000 AS int) ,max_int AS CAST(0x7FFFFFFF AS int) ,min_bigint AS CAST(0x8000000000000000 AS bigint) ,max_bigint AS CAST(0x7FFFFFFFFFFFFFFF AS bigint) ,min_float AS CAST('-1.79E+308' AS float) ,max_float AS CAST('1.79E+308' AS float) ,min_real AS CAST('-3.40E+38' AS real) ,max_real AS CAST('3.40E+38' AS real) ,min_smalldatetime AS CAST('19000101 00:00' AS smalldatetime) ,max_smalldatetime AS CAST('20790606 23:59' AS smalldatetime) ,min_datetime AS CAST('17530101 00:00:00.000' AS datetime) ,max_datetime AS CAST('99991231 23:59:59.997' AS datetime) ) INSERT INTO datatype_extrema DEFAULT VALUES GO CREATE TRIGGER nochange_datatype_extrema ON datatype_extrema INSTEAD OF INSERT, UPDATE, DELETE AS BEGIN RAISERROR ('No changes allowed for table datatype_extrema.', 16, 1) ROLLBACK TRANSACTION END GO
之后,您可以将最大值复制到局部变量,也可以(在使用查询时)与此表进行交叉联接。
Declare @max_int int Set @max_int=(SELECT max_int FROM datatype_extrema) IF COALESCE(@FirstInt, @max_int) < COALESCE(@SecondInt, 0)