Persistent variables between stored procedure calls

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Persistent variables between stored procedure...

Persistent variables between stored procedure calls

Rate Topic

Display Mode

Topic Options

Author

Message

Nik-574695

Posted Friday, January 18, 2008 6:19 AM

Forum 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. Smile

Cheers

Nik

Post #444815

ALZDBA

Posted Friday, January 18, 2008 7:12 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 6,862, Visits: 8,049

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

     Jul 13

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

Need a bit of Powershell? How about this

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

Post #444847

Nik-574695

Posted Friday, January 18, 2008 7:30 AM

Forum 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

ALZDBA

Posted Friday, January 18, 2008 8:15 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 6,862, Visits: 8,049

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

     Jul 13

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

Need a bit of Powershell? How about this

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

Post #444900

Nik-574695

Posted Wednesday, January 23, 2008 1:33 AM

Forum 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

ChiragNS

Posted Wednesday, January 23, 2008 2:10 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

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

ALZDBA

Posted Wednesday, January 23, 2008 3:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 6,862, Visits: 8,049

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

     Jul 13

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

Need a bit of Powershell? How about this

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

Post #446319

« Prev Topic | Next Topic »

Permissions