newid() inside sql server function

here's a clever solution:

create view getNewID as select newid() as new_id

create function myfunction ()
returns uniqueidentifier
as begin
   return (select new_id from getNewID)
end

that i can't take credit for. i found it here: http://omnibuzz-sql.blogspot.com/2006/07/accessing-non-deterministic-functions.html

-don

You can pass NEWID() as a parameter to your function.

CREATE FUNCTION SOMEIDFUNCTION
(
    @NEWID1 as varchar(36), @NEWID2 as varchar(36)
)
RETURNS varchar(18)
AS
BEGIN
    -- Do something --
    DECLARE @SFID varchar(18)
    SELECT @SFID = 'DYN0000000' + LOWER(LEFT(@NEWID1,4)) + LEFT(@NEWID2,4) 
    RETURN @SFID
END
GO

Call the function like this;

SELECT dbo.SOMEIDFUNCTION(NewID(),NewID())

use it as a default instead

create table test(id uniqueidentifier default newsequentialid(),id2 int)

insert test(id2) values(1)

select * from test

NB I used newsequentialid() instead of newid() since newid() will cause pagesplits since it is not sequential, see here: Some Simple Code To Show The Difference Between Newid And Newsequentialid

newid() inside sql server function

Related

Recent Posts