newid() inside sql server function
Solution 1
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
Solution 2
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())
Solution 3
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
Florjon
Experienced developer with a demonstrated history of working in the banking and logistic industry. Skilled in relational and document databases, cloud infrastructure (Azure and GCP) and creating micro-services using Microsoft Technologies.
Updated on July 09, 2020Comments
-
Florjon almost 4 years
I have to insert a fake column at the result of a query, which is the return value of a table-value function. This column data type must be unique-identifier. The best way (I think...) is to use
newid()
function. The problem is, I can't usenewid()
inside this type of function:Invalid use of side-effecting or time-dependent operator in 'newid()' within a function.