Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Persistent variables between stored procedure calls


Persistent variables between stored procedure calls

Author
Message
Nik-574695
Nik-574695
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 6
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

SET @c = @c + 1
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

SET @c = @c + 1
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
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6978 Visits: 8839
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 on
declare @MyNumber int
insert into #mytmptb
output inserted.myNumber
default values

set @MyNumber = scope_identity()

delete from #mytmptb

print @MyNumber




Also keep in mind multiple processes may run in parallel ... locking may occur when using persistent objects or global temptb's.

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Nik-574695
Nik-574695
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 6
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

ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6978 Visits: 8839
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 int
insert into #mytmptb default values
set @MyNumber = scope_identity()
delete from #mytmptb



I 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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Nik-574695
Nik-574695
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 6
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
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
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"
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6978 Visits: 8839
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search