1

I have the following function defined in mysql, and am attempting to convert it to MSSQL. What would the MSSQL (2012) equivalent be?

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 ;
jschweitzer
  • 88
  • 8

1 Answers1

3

There is a glitch to it .... In SQL Server you cannot use RAND(), NEWID() it will throw an error complaining about having some sort of Slide-Effecting function bla bla..

But there is a way around to it you can Create a View which calls that RAND() function and then use that view inside your Function definition.

View

CREATE VIEW vw_Rand_Value
AS
  SELECT RAND() Rnd_Value
GO

Function for SQL 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

Function for 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

Call Function

SELECT  dbo.get_random_state()
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • Good catch on the UDF limitation but personally I would just select one value from the view to work around that limitation, stick it in a scalar variable then use `choose` as in the deleted answer. Simpler and more efficient than populating and sorting a table variable. – Martin Smith Sep 10 '14 at 21:26
  • @MartinSmith Great suggestion but is `CHOOSE()` allowed inside a UDF?? I dont have 2012 installed I cannot test it, Please feel free to edit my answer if you have 2012 and test it, cheers :) – M.Ali Sep 10 '14 at 21:35
  • You don't need to have it installed. [SQL Fiddle](http://www.sqlfiddle.com/#!6/4eb59/1) – Martin Smith Sep 10 '14 at 21:40
  • sorted, much more sleek with `CHOOSE()`. – M.Ali Sep 10 '14 at 21:46
  • @MartinSmith is `CHOOSE()` expanded into a `CASE` expression? – ypercubeᵀᴹ Sep 10 '14 at 21:57
  • @ypercube yes. There was actually a question on that topic today. [here](http://stackoverflow.com/questions/25769897/t-sql-query-gives-different-results-when-declaring-integer-vs-calculating-in-que/25769952#25769952) – Martin Smith Sep 10 '14 at 21:58
  • 1
    I just deployed the second function on my 2012 server and it works great. – jschweitzer Sep 10 '14 at 22:08