I am still a newbie and would like to ask a question concerning a a stored proc that I am not sure about.
The goal is to pass the indentity pk to a stored procedure.I then want to select that particular record,insert a new record in that same table with values from the record I selected.Then pass back to asp coding , the new record identity of the newly inserted record. If this is possible, would some be as so kind to point me in the right directions on how to acheive this feat ?? I have written stored procedures but not like this.Ths stored proc would be used by many different users so i want to careful about any multi-user problems that could occurr.
And thank you in advance...

So you just want to copy a row but give the new row a new id? How about something like this:
CREATE PROC CopyRow
    @id as integer,
    @newid as integer OUTPUT
AS
    INSERT TableName
    SELECT (list all fields except the identity here) FROM TableName
    WHERE IdentityName = @id

    SET @newid = @@IDENTITY

I assume you know how to pass params in and retrieve output params, since you say you have written sprocs before...If not just ask.

hth
Phil

thank you for responding.
I do appericate it. It looks soooo simple and easy !

Note that if you are using SQL Server 2000, you would be somewhat better off using:

SET @newid = scope_identity()

Since the @@IDENTITY function returns the last Identity value used on the connection, if 'TableName' had an INSERT trigger defined (for journaling or audit purposes, say), and that trigger inserted into another table which also had an Identity column, then the results of the @@IDENTITY would be the value of *that* identity insert, and not the one from the original INSERT. The scope_identity() function returns the corrrect value of the identity within the current scope, regardless of how deeply nested triggers or stored procedures are...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thank you for pointing that out about scope_identity.
That was something that I learned very early in writing sprocs that return the identity.
But I have another question. I think I know the bits and pieces of how do the following for the procedure, but am very uncertain.
My question is, how do I handle it when i wan to change some of the fields values as I am inserting them into the new row ??
I would think I would have to declare some variables to hold the parameter values that I pass for the new values, but uncertain as how to arrange the sproc then ??
Again, thank you for responding

That would depend entirely on the nature of the change in values you wish to perform. You need to be more specific.

As a contrived example, consider where you want to variously modify the values of some columns:

INSERT TableName
SELECT (Col1 * 2, Col2/2, Col3 + 3, COl4 + Col5) FROM TableName
WHERE IdentityName = @id

That is, the values SELECTed for the INSERT can be expressions of arbitrary complexity.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Sorry I was not more defintive about changing values.
This is what I want to achieve: I want to copy a row of information (all but three of the fields would be the same) but some of the field values from the original row, need to be changed to values that I would pass as parameters.
For example, such as the business name.
does that help ?

CREATE PROCEDURE MyProc
@Val1 Int, @Val2 Int, -- new rows
@OldID Int, -- ID for the old row
@newID Int OUTPUT
AS

SET NOCOUNT ON

INSERT INTO SomeTable
(Col1, Col2, Col3, Col4)
SELECT Col1, Col2, @Val1, @Val2
FROM SomeTable
WHERE SomeID = @OldID

SET @newID = scope_identity()

regards
David Cameron

Thank you ! thank you!
There is so much to learn and sometimes it just helps to have some say: "You do it this way"
I thought it would be constructed to something like that !!
Althought I didn't quite have it right,(I spendt most of the day messing with it), I was atleast on the right track and that tells me I am learning.
Again thank all of you for helping.
:)