有没有一种方法来编程(在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*/)
谢谢
解决方法
虽然似乎没有任何内联方式获得最小或最大值,there’s a solution somebody put together：
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_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)
