以编程方式获得SQL中float的最大值 [英] Getting maximum value of float in SQL programmatically

问题描述

有没有一种以编程的方式（在T-SQL中）检索数据类型的最大（和最小）值的方法？它会像在C＃中的 float.MaxValue 。

我想在一些选择中使用它，当参数不等于任何实际在数据库中的值，所以我会使用类似于

  declare @min float 
 declare @max float 
  - 填充@min和@max，如果未定义，则可以为null 
 select * from foo 
其中isnull（@ min，0）和isnull（@ max，max（float）/ * magic * /）
  

解决方案

虽然没有出现任何内联的方式来获得最小值或最大值，有人提供了一个解决方案：
$ b $ pre $ code 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 b）
，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 INSERT，UPDATE，DELETE
AS BEGIN
RAISERROR（'不允许更改表datatype_extrema。'，16，1）
ROLLBA CK TRANSACTION
END
GO

之后，您可以复制最大值值的本地变量或
（使用查询时）与此表交叉连接。

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Set $ max $ FirstInt，@max_int）< COALESCE（@SecondInt，0）

Is there an method for programmatically (in T-SQL) retrieving the maximum (and minimum) value of a datatype? That it would act like float.MaxValue in C#.

I would like to use it in some selection when the parameter does not equal any actual values in the database, so I would use something like

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*/)

解决方案

Though there doesn't appear to be any inline way to get the min or max values, 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 

After that, you can either copy a maximum value to a local variable or (when using queries) cross join with this table.

  Declare @max_int int 
  Set @max_int=(SELECT max_int FROM datatype_extrema) 
  IF COALESCE(@FirstInt, @max_int) < COALESCE(@SecondInt, 0) 

这篇关于以编程方式获得SQL中float的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！