January 18, 2008 at 6:20 am
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
January 18, 2008 at 7:12 am
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
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
January 18, 2008 at 7:30 am
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
January 18, 2008 at 8:15 am
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
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
January 23, 2008 at 1:33 am
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
January 23, 2008 at 2:10 am
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"
January 23, 2008 at 3:07 am
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply