Solved

stored procedure create mirror of id

Posted on 2012-04-01
10
210 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
  • 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 500 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 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