How to write Stored procedure for this problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to write Stored procedure for this problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-19 : 07:46:12

PARAG writes "I have a database in which there are many master tables,
in which there is column called (fldID) this column name varies from table to table.

While inserting data i want to generate this id automatically ie
taking max(id)+1 but i m not using auto increament feature, this
should be done through front end.

so i want to write a stored procedure for which i am going to pass tablename and column storedProc should return me maximum value of the columnname + 1

can any body help me out in this."

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-19 : 08:50:10

First, I disagree with the front end generating an id for a table but here you go:

SELECT MAX(ISNULL(<column>, 0)) + 1

Since the table name and column are dynamic, you'll need to use sp_executesql to execute the query and return the result to you.

Bambola
Posting Yak Master

103 Posts

Posted - 2003-02-19 : 09:18:02

You can create a table id_tables that has the fields:

column_name varchar(20)
id_value int

and write a sproc that accept column_name and returns the next id_value.

CREATE PROCEDURE get_next_id
(
@column_name varchar(20)
)
AS
DECLARE @id_value int
set @id_value = -2

UPDATE id_tables
SET @id_value = id_value = id_value + 1
WHERE column_name = @column_name

IF @@ERROR <> 0
whatever....

RETURN (@id_value)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-19 : 12:57:25

What's the advantage of a formula like that over an IDENTITY field? the formula is basically duplicating the functionality of the IDENTITY field since the logic is simply "take the max() and add 1".

The downside of "simulating" an IDENTITY field like this is multiple users could try to insert records with the same ID if they do it at the same time.

- Jeff

Bambola
Posting Yak Master

103 Posts

Posted - 2003-02-19 : 14:34:35

quote:

What's the advantage of a formula like that over an IDENTITY field? the formula is basically duplicating the functionality of the IDENTITY field since the logic is simply "take the max() and add 1".
- Jeff

I have never said that there is advantage to this "formula" over using IDENTITY. But I thought s/he said that they cannot/do not want to use IDENTITY.
As long as IDENTITY is not an option, the procedure seem to me a better solution than SELECT MAX(column) + 1.
To my understanding using MAX can still result duplications. I cannot see it happenning using this procedure since updating the row (while selecting the value) will result a lock that will prevent duplications.
Please correct me if I'm wrong :-)

Bambola.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-19 : 19:17:23

Bambola -- sorry, the question wasn't directed at you, it was to the person who posted the question about wanting to do this in the first place.

Sorry about the confusion!

- Jeff

Bambola
Posting Yak Master

103 Posts

Posted - 2003-02-20 : 02:06:04

quote:

Bambola -- sorry, the question wasn't directed at you, it was to the person who posted the question about wanting to do this in the first place.
Sorry about the confusion!
- Jeff

No problems, Jeff

Bambola.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources