?
Solved

stored procedure create mirror of id

Posted on 2012-04-01
10
Medium Priority
?
213 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 31

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 31

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 31

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 SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

649 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