Stored Procedure Question
Kodo | Posted 11:43am 19. July 2005 Server Time |
Not my area of expertise so I need a little nudge.
What I want to do is to run three inserts, get the id (scope_identity) of each inserted record and after each insert use that id returned to insert into another table.
This is what I have but it obviously doesn't work otherwise I wouldn't have posted ;)
' I chopped it to one insert here for brevity
ALTER PROCEDURE dbo.spDemoCreatePages
(@SiteID bigint)
AS
Declare @Page1 bigint
INSERT INTO dbo.Page (page_site, page_name, page_title) VALUES (@SiteID,'About', 'About Us')
Select Scope_Identity() as Page1
INSERT INTO pagetext (pagetext_page,pagetext_entry,pagetext_description) VALUES(@Page1,'This is page text1','Page Text Description Here1')
any ideas?
thanks :) |
Kodo | Posted 11:57am 19. July 2005 Server Time |
Disregard,
I've found the answer
INSERT INTO dbo.Page (page_site, page_name, page_title) VALUES (@SiteID,'About', 'About Us')
set @Page1=Scope_Identity()
INSERT INTO pagetext (pagetext_page,pagetext_entry,pagetext_description) VALUES(@Page1,'This is page text1','Page Text Description Here1')
Informant | Posted 12:02am 19. July 2005 Server Time |
First of all you really need to but this into a transaction with a little error handling/rollback mechanism and you can google that to find all kinds of examples second you must always use a @ in t-sql to prequalify a local variable. and I like to use SET instead of select just to clarify when I am setting variables and selecting resultsets. So you code my bw tweaked from the above ro something like:
Alter proc procName
(
@SiteID int --dont know why you need big int but if you do switch it
)
BEGIN TRAN
Declare @lastID as int
--insert here page id
SET @lastID = SCOPE_IDENTITY()
--insert here with new id
SET @lastID = SCOPE_IDENTITY()
--insert here with new id
COMMIT TRAN
ROLLBACK TRAN
something along those lines should do it...
Informant | Posted 12:03am 19. July 2005 Server Time |
figures as I am in the middle of writing it! penis!
Reply to Post Stored Procedure Question
|
|
|
