stored procedure create mirror of id

How do we create a store procedure than when a NEW record is created, the field id2 will insert a number +1 from the main table identity field ?

What type of field datatype would it need to be ? thanks in advanced
goodluck11Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hnasrCommented:
Did you try,table level, column properties - Identity Specification - Is Identity?
0
goodluck11Author Commented:
but this column is not identity, id2 would be a copy of id(identity) and updated.

for example:

- usersid20 inserts a record

- id (identity) for the table and is = 231 (other users use the same table)
id2 would be = 1 (first record inserted by user)

- users inserted 20 new records.

- then everytime  usersid20 access the table, id2 is taken,  get the last id2 number (20)  add +1 and update the 231 record. id is 21 now.

Not sure how else explain this, but please ask if needs better explanation.

Its pretty much taking each record accessed by the user, and moving it to last by using id2 as reference.

The store procedure would need to create the first id2. ASPX can do the rest.
0
hnasrCommented:
Better explanation is by example. Check and modify if necessary.
User     id     id2
0001      1      1
0001      2      2
0002      3      1
0002      4      2
0002      5      3
0003      6      1
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RimvisCommented:
Hello goodluck11,

How do you identify different users? By SQL Server login name? Or user identifier inserted along with other data?  Could you please post you table structure?
0
goodluck11Author Commented:
the example is good

How do you identify different users? - by userid

By SQL Server login name? login name

Or user identifier inserted along with other data? -nop

 Could you please post you table structure?

table 1
id - userid - password -

join with

table 2
id - userid - adcreated - currentad_id (id2)

where every time table 2 is queried(retrived by user), currentad_id should become last currentad_id +1
0
RimvisCommented:
Hi, is this what you want?
CREATE PROCEDURE sprGetUserInfo 
	@UserID INT
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION

IF NOT EXISTS(SELECT TOP 1 ID FROM table2 WHERE userid = @UserID)
	--Insert first record
	INSERT INTO table2 (userid,currentad_id)VALUES (@UserID,1) 
ELSE
	--Increment 
	UPDATE table2 SET currentad_id = currentad_id+1 WHERE userid = @UserID
	
--Return data
SELECT table1.*, table2.currentad_id FROM table1 INNER JOIN table2 ON table1.userid = table2.userid	WHERE table1.userid = @UserID
	
COMMIT TRANSACTION

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hnasrCommented:
/* Check this for table A(a, x, n) , you pass the value for a, and outputs the new n+1
   that can be used in relevant statements */

use yourDatabse;
go
/*check if user stored procedure next n  exists
to drop it first  before creation*/
if object_id('uspNextn','P') IS NOT NULL
      drop procedure uspNextn;
go
create procedure uspNextn      
      @a nvarchar(25),   --to get for this user a
      @out numeric(7,1) OUT  -- this new value of n to be used else where
AS
      set nocount on;
      --table A(a, x, n)
      SET @out=(SELECT MAX(n) from A where a=@a)+1;
go
-- use stored procedure, declare variables, execute and use out value
declare @a nvarchar(25), @out numeric(7,1)
set @a='1'
EXECUTE uspNextn @a, @out out
--use the out value which is the next n
print 'The max value of n for the value of a = ''' + @a + ''' is ' + cast(@out As nvarchar(25));
0
Anthony PerkinsCommented:
Something like this perhaps:

INSERT	YourTable(UserID, ID2)
SELECT	TOP 1
	UserID, ID2
FROM	(
	SELECT	@UserID, 1 ID2
	UNION ALL
	SELECT	TOP 1
		@UserID, ID2 + 1
	FROM	YourTable
	WHERE	UserID = @UserID
	ORDER BY
		ID2 DESC) x
ORDER BY
	ID2 DESC

Open in new window

0
goodluck11Author Commented:
thank you,,testing...
0
goodluck11Author Commented:
We are not sure what is the best way to do this. Triggers? stored procedure ? New table?

We have a table with htmlads and another with users.

This is the scenario:

1.      Every time a user calls/retrieve one of his html ad in the htmlads table, we want that particular ad to go to the end of the queue.

      Either we add a field “CurrentAd_id” and assign a number to it, first time user creates an ad this field will be 1. Then everytime the user creates an add it will be CurrentAd_id  + 1

2.      When the user calls htmlads to retrieve an ad, then this field becomes the last CurrentAd_id  + 1.

Makes sense ? we are just not sure if should create a new table that would contain this counter, or should be in the same table that contains the html ads and update it.

Your help is appreciated. Thanks in advanced.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.