Hi Alzdba, and thanks for your quick answer.
I am not sure I follow you. Would you mind keeping the code within the context of a stored procedure or similar so that I can use it in a setting like: UPDATE waitinglist SET position=dbo.COUNT()
I have tried putting your code in this context the way I understand you, and if I've put it together right, I still get "Cannot access temporary tables from within a function." The code is pasted below
In this example, working with identity() is indeed a good option, but I would prefer an option that allows me to do a custom calculation (I kept this calculation an easy +1 calculation for the sake of clarity. I plan on basing my calculation on some input parameters, like UPDATE list SET value=dbo.CALC(memberData))
create table #mytmptb (myNumber int identity(1,1))
set nocount on
CREATE FUNCTION COUNT() RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
declare @MyNumber int
insert into #mytmptb
output inserted.myNumber
default values
set @MyNumber = scope_identity()
delete from #mytmptb
return @MyNumber
END;
GO
SELECT dba.COUNT()
GO