Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Persistent variables between stored procedure calls Expand / Collapse
Author
Message
Posted Friday, January 18, 2008 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 25, 2008 6:22 AM
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
Post #444815
Posted Friday, January 18, 2008 7:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:56 AM
Points: 6,723, Visits: 8,462
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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #444847
Posted Friday, January 18, 2008 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 25, 2008 6:22 AM
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

Post #444863
Posted Friday, January 18, 2008 8:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:56 AM
Points: 6,723, Visits: 8,462
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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #444900
Posted Wednesday, January 23, 2008 1:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 25, 2008 6:22 AM
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
Post #446273
Posted Wednesday, January 23, 2008 2:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 1, 2014 6:55 AM
Points: 2,365, Visits: 1,845
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"
Post #446292
Posted Wednesday, January 23, 2008 3:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:56 AM
Points: 6,723, Visits: 8,462
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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #446319
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse