Scalar function help
-
Hai gurus,
I have a problem. I have created a table as shown below. Actually what I am trying to do is to increment the column Incr each time a value is inserted by using scalar function. I know we can have the @@identity directly inserted.
CREATE TABLE [Employees] (
id int identity(1,1) not null,
[emp_id] [Char] (9) NOT NULL,
[fname] VARCHAR(10),
[lname] VARCHAR(10),
Incr as DBO.Incr_value())
and the code for the function is
CREATE FUNCTION dbo.Incr_value()
RETURNS int -- return_data_type.
AS
BEGIN
RETURN ( SELECT ISNULL(MAX(INCR),0)+1 from Employees)
END
Then I do an insert on the table like
insert into Employees (emp_id,fname,lname) values ('H-B39728F','Helen','XXXX')
Now if I do a select from the table it gives me this error
Server: Msg 217, Level 16, State 1, Procedure Incr_value, Line 6
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
What is the issue and what is the mistake I am doing
thanks in advance
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
What you've created there is a computed column in the table, not a column with a default. The function is recursive as written, and will call itself without end, which is why you're getting the nesting level error.
Try this instead.
CREATE TABLE [Employees] (
id int identity(1,1) not null,
[emp_id] [Char] (9) NOT NULL,
[fname] VARCHAR(10),
[lname] VARCHAR(10),
Incr INT DEFAULT DBO.Incr_value()
)
Why do you have two incrementing ields in the tble? The id and the field incr.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks a lot. This worked well. A silly mistake
Just to test how to apply scalar function to table
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
Just be aware that MAX(column) + 1 isn't a very scalable method. If the number of employees gets to large, the function will degrade in performance significantly. Also, if the table(s) involved has heavy traffic, concurrency problems may also become an issue.
Rolling your own counters may better done by having a dedicated table catering for the numbers, and have the function/proc get the values from there when need be.
But, it depends. For small volumes / low traffic you may get by anyway...
/Kenneth
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply