Persistent variables between stored procedure calls
-
Dear sirs. I'm trying to make a stored procedure that will base its decision on previous function calls. For the sake of clarity, I've made a stored procedure here that has the same problem. It's dbo.COUNT(). The function of this is give a number +1 higher than the previous time it was called within a session.
SELECT dbo.COUNT() ==> 1
SELECT dbo.COUNT() ==> 2
SELECT dbo.COUNT() ==> 3
SELECT dbo.COUNT() ==> 4
and I plan on using it like this:
UPDATE waitinglist SET position=dbo.COUNT()
I have implemented it in three ways. The first one is by defining a global variable, then using a temporary table and finally using a persistent table. Below each of the examples I've written the error message(s) I get. My question is: how should I implement it so that it works as described above?
----
DECLARE @@c int
SET @@c = 0
GO
CREATE FUNCTION COUNT() RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
SELECT @@c
SET @@c = @@c + 1
RETURN @c
END;
GO
SELECT dbo.COUNT()
GO
SELECT dbo.COUNT()
GO
SELECT dbo.COUNT()
GO
SELECT dbo.COUNT()
GO
-- Must declare the scalar variable "@@c".
----
CREATE TABLE ##count( c int )
GO
CREATE FUNCTION COUNT() RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @c int
SELECT @c = c FROM ## count
DELETE FROM ## count
INSERT INTO ##count (c) VALUES (@c)
RETURN @c
END;
GO
SELECT dbo.COUNT()
GO
SELECT dbo.COUNT()
GO
SELECT dbo.COUNT()
GO
SELECT dbo.COUNT()
GO
DROP TABLE count
go
-- Cannot access temporary tables from within a function.
----
CREATE TABLE count( c int )
GO
CREATE FUNCTION COUNT() RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @c int
SELECT @c = c FROM count
DELETE FROM count
INSERT INTO count (c) VALUES (@c)
RETURN @c
END;
GO
SELECT dbo.COUNT()
GO
SELECT dbo.COUNT()
GO
SELECT dbo.COUNT()
GO
SELECT dbo.COUNT()
GO
DROP TABLE count
go
-- Invalid use of a side-effecting operator 'DELETE' within a function.
-- Invalid use of a side-effecting operator 'INSERT' within a function.
-----
Thanks for reading this far, I'm hoping you have some good suggestions for me.
Cheers
Nik
-
Is there a problem when using a temp table to generate your number ?
e.g.
create table #mytmptb (myNumber int identity(1,1))set nocount ondeclare @MyNumber intinsert into #mytmptboutput inserted.myNumberdefault valuesset @MyNumber = scope_identity()delete from #mytmptbprint @MyNumberAlso keep in mind multiple processes may run in parallel ... locking may occur when using persistent objects or global temptb's.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
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
-
Oh no, forget about the function. that won't work with temptb's
(as the error states)
In stead just add the create #mytmptb at the beginning of your master stored procedure.
and add this part before every part where you would use the function
declare @MyNumber intinsert into #mytmptb default valuesset @MyNumber = scope_identity()delete from #mytmptbI know it seems stupid, but it's straightforward and easy to interprete.
btw you could set the increment base on inputparameters of your master proc.
create table #mytmptb (myNumber int identity(1,@increment))Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Dear Alzdba,
sorry for my late reply. I'm confused with your answer. I'm confused when you say I should forget about the function as it's exactly a function I want. I want to be able to say something like:
UPDATE list SET value=dbo.CALC(memberData))
Let me quickly write the python-equivalent of the function, and instead of confusing us with the +1 scenario, let's do SQRT(memberData)/2.19+lastValue:
import math
lastValue = 0
def CALC(memberData):
global lastValue
lastValue = math.sqrt(memberData)/2.19+lastValue
return lastValue
print CALC(3) # gives 0.79089077971181609
print CALC(3) # gives 1.5817815594236322
print CALC(3) # gives 2.3726723391354483
print CALC(3) # gives 3.1635631188472644
Note that lastValue is stored outside the procedure, note also that it's not given as an argument. I'm not saying this is good coding practice to keep a global floating around, certainly not in a multi-threaded system like a database engine, but what I'm looking for is the function to keep track of its previous state and use that in its calculation. That's why I suggested to keep it in a temporary table, but obviously I'm not allowed to do that from within a stored procedure. As far as I can tell, I cannot define my own global variables either. What other options do I have?
To make the the question brief: can you rewrite the above function into a stored procedure so that I can do the query "UPDATE list SET value=dbo.CALC(memberData))"?
Cheers
Nik
-
Hi
Functions have many limitations as you have seen . Cant yu use a stored proc for this instead of the function . you will not be able to use the update statement as you mentioned but it will get the job done.
"Keep Trying"
-
I have the impression you would like to use a "general code table" kind of table to keep track of your "sequencenumbers".
That might work, but keep in mind you may suffer (dead)locking if a single parameter row is being used by a number of simultanious users/connections.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply