Solved

stored procedure create mirror of id

Posted on 2012-04-01
10
206 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now