等效于MYSQL ()函数的MSSQL

Question

我在mysql中定义了以下函数，并试图将其转换为MSSQL。什么是等效的MSSQL (2012)？

DELIMETER $$

CREATE FUNCTION get_random_state() RETURNS VARCHAR(500) DETERMINISTIC 

BEGIN
  set @state := ELT(1 + FLOOR(RAND() * 6),'AZ','RI','VA','PA','LA','CA');       
  RETURN @state;
END $$

DELIMETER ;

Answer 1

它有个小故障..。在Server中，不能使用RAND()，NEWID()将引发一个错误，抱怨有某种幻灯片影响函数bla。

但是有一种方法，您可以创建一个View，它调用该RAND()函数，然后在您的函数定义中使用该视图。

视图

CREATE VIEW vw_Rand_Value
AS
  SELECT RAND() Rnd_Value
GO

Server 2008的函数

CREATE FUNCTION get_random_state() 
RETURNS VARCHAR(2)
BEGIN

  DECLARE @Table TABLE(States VARCHAR(2), OrderID FLOAT)
  INSERT INTO @Table (States,OrderID) 
  VALUES ('AZ', (SELECT * FROM vw_Rand_Value))
        ,('RI', (SELECT * FROM vw_Rand_Value))
        ,('VA', (SELECT * FROM vw_Rand_Value))
        ,('PA', (SELECT * FROM vw_Rand_Value))
        ,('LA', (SELECT * FROM vw_Rand_Value))
        ,('CA', (SELECT * FROM vw_Rand_Value))

  DECLARE @Random_State VARCHAR(2);

  SELECT TOP 1 @Random_State  = States
  FROM @Table
  ORDER BY OrderID;

  RETURN @Random_State;
END

SQL Server 2012的函数

CREATE FUNCTION get_random_state() 
RETURNS VARCHAR(2)
BEGIN
 DECLARE @rand int;
 DECLARE @Random_State VARCHAR(2);

SELECT @rand = 1 + FLOOR(Rnd_Value * 6) FROM vw_Rand_Value

SET @Random_State = CHOOSE(@rand,'AZ','RI','VA','PA','LA','CA');

  RETURN @Random_State;
END

呼叫功能

SELECT  dbo.get_random_state()