Can't drop a view inside a function

Question

I'm trying to make a simple function to return a random eye color. I could do this in a stored procedure but I was trying to challenge my self and try something new. I'm getting some errors, I've tried this a few different ways and I have found some documents roughly related but I'm just not skilled enough I think to understand what I did wrong and what the documents I've found are referring too syntax wise. I hope that makes sense.

CREATE FUNCTION Random_Eyes ()
RETURNS NVARCHAR(100)
AS
BEGIN
    CREATE VIEW Rand_Eyes 
    AS 
       SELECT TOP 1 [Eyes] 
       FROM [dbo].[Eyes] 
       ORDER BY NEWID()
    GO

    DECLARE @Eyes AS NVARCHAR(100) = (SELECT [Eyes] FROM Rand_Eyes)

    RETURN @Eyes

    DROP VIEW Rand_Eyes
END
GO

Errors:

Msg 156, Level 15, State 1, Procedure Random_Eyes, Line 14
Incorrect syntax near the keyword 'VIEW'.

Msg 102, Level 15, State 1, Procedure Random_Eyes, Line 14
Incorrect syntax near ')'

Msg 178, Level 15, State 1, Line 4
A RETURN statement with a return value cannot be used in this context.

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'END'.

Any feedback or suggestions would be helpful. Thank you

You cannot do anything inside a function that alters the state of anything outside the function, including creating database objects like views. Its hard to see a case where creating a view in a function (or procedure) would be a good idea anyway ... — Alex K., Mar 08 '18 at 18:53

Dai · Accepted Answer · 2018-03-08T19:33:07.540

1

You don't need a temporary view inside a function - that's not even allowed anyway as functions in SQL Server are strictly read-only: they cannot perform any DDL operations (CREATE, UPDATE, INSERT, DELETE).

Note that ORDER BY NEWID() is not the best way to get a random result (because it triggers a table linear scan). Try this:

CREATE FUNCTION Random_Eyes()
    RETURNS NVARCHAR(100)
AS
BEGIN

    DECLARE @count int = ( SELECT COUNT(*) FROM [Eyes] )

    SELECT
        Eyes
    FROM
        dbo.Eyes
    ORDER BY
        EyeId -- or whatever your constant, incrementing primary-key is
    OFFSET
        ( RAND() * @count ) ROWS
    FETCH NEXT 
       1 ROWS ONLY

END

edited Mar 08 '18 at 19:33

answered Mar 08 '18 at 18:58

Dai

141,631
28
261
374

The `OFFSET ... FETCH` in SQL Server **requires** an `ORDER BY` clause which you haven't included in your code (those two keywords are actually *extensions* of the `ORDER BY` clause ...) – marc_s Mar 08 '18 at 19:17
@marc_s Good catch, fixed! but `OFFSET ... FETCH` is part of the SQL:2008 standard, they're not extensions anymore. – Dai Mar 08 '18 at 19:33
`The number of rows provided for a OFFSET clause must be an integer.` (The "a `OFFSET`" isn't my misspelling.) You may as well do `DECLARE @offset INT = (SELECT RAND() * COUNT(*) FROM [Eyes])` to take care of the conversion in one go. – Jeroen Mostert Mar 08 '18 at 19:50
Also, you may be disappointed by the performance of this; it can still easily induce a scan, and is not necessarily much more efficient than `TOP(1) ... ORDER BY NEWID()`. Especially since `COUNT(*)` can itself be an expensive operation (and that's ignoring the fact that the `COUNT(*)` can change in between queries). Try before you buy. – Jeroen Mostert Mar 08 '18 at 20:01
@JeroenMostert I would have thought that `COUNT(*)` without a predicate would use the table's `STATISTICS` for O(1) result. – Dai Mar 08 '18 at 20:12
You're not the first one to think that, but you'd be wrong. `COUNT(*)` performs an index scan (on the smallest index it can find, but still), as it needs to be transactionally consistent just like any other operation. There are faster (if less accurate) ways of getting a table's row count, but those are the subject of [other questions](https://stackoverflow.com/q/44158023/4137916). :-) – Jeroen Mostert Mar 08 '18 at 20:14
Everything I read said as a work around create a view to use the random function. Here is a link to the article. I'm a little confused now because your saying something totally different then what I found was suggested or maybe I'm just not understanding your logic. Link: https://stackoverflow.com/questions/772517/newid-inside-sql-server-function – mholmes Mar 08 '18 at 20:33
@mholmes The example you linked to has a function that uses a preexisting view, it doesn't create-then-drop it. – Dai Mar 08 '18 at 20:52
Oh ok that makes sense. – mholmes Mar 08 '18 at 21:04

Can't drop a view inside a function

1 Answers1