?
Solved

stored procedure create mirror of id

Posted on 2012-04-01
10
Medium Priority
?
212 Views
Last Modified: 2012-04-13
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
0
Comment
Question by:goodluck11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 37794290
Did you try,table level, column properties - Identity Specification - Is Identity?
0
 

Author Comment

by:goodluck11
ID: 37794450
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
 
LVL 30

Expert Comment

by:hnasr
ID: 37794577
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 19

Expert Comment

by:Rimvis
ID: 37794627
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
 

Author Comment

by:goodluck11
ID: 37794750
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
 
LVL 19

Accepted Solution

by:
Rimvis earned 2000 total points
ID: 37794975
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
 
LVL 30

Expert Comment

by:hnasr
ID: 37796438
/* 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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37804438
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
 

Author Comment

by:goodluck11
ID: 37804519
thank you,,testing...
0
 

Author Comment

by:goodluck11
ID: 37812752
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question