Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 8th, 2003, 07:17 PM
Authorized User
 
Join Date: Jun 2003
Location: Mount Gilead, Ohio, USA.
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default Creating a stored procedure

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...
Reply With Quote
  #2 (permalink)  
Old June 9th, 2003, 03:29 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old June 9th, 2003, 06:11 AM
Authorized User
 
Join Date: Jun 2003
Location: Mount Gilead, Ohio, USA.
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thank you for responding.
I do appericate it. It looks soooo simple and easy !
Reply With Quote
  #4 (permalink)  
Old June 9th, 2003, 07:01 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old June 9th, 2003, 07:38 AM
Authorized User
 
Join Date: Jun 2003
Location: Mount Gilead, Ohio, USA.
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old June 9th, 2003, 07:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #7 (permalink)  
Old June 9th, 2003, 08:13 AM
Authorized User
 
Join Date: Jun 2003
Location: Mount Gilead, Ohio, USA.
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 ?
Reply With Quote
  #8 (permalink)  
Old June 9th, 2003, 07:07 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old June 10th, 2003, 06:08 AM
Authorized User
 
Join Date: Jun 2003
Location: Mount Gilead, Ohio, USA.
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
:)
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
creating variables in a Stored procedure miamikk SQL Language 1 June 4th, 2007 03:02 AM
Creating stored procedure with trigger (HOWTO ..) AzlanAziz SQL Language 0 January 16th, 2007 04:34 AM
Creating Charts Using Stored Procedure srinivasparimi SQL Server 2005 1 August 7th, 2006 06:45 AM
Problem creating Stored Procedure vivekshah SQL Server 2000 4 May 28th, 2006 11:33 PM
creating stored procedure drachx SQL Server 2000 2 October 14th, 2004 08:10 AM



All times are GMT -4. The time now is 10:33 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.