Help needed

Hi all,

I am trying to write a stored procedure, which has two insert statements.

the first insert statement, is simple which inserts data into a table and returns the primary key for the new row added. using this primary key, i am writing another insert statement, which passes a list of elements which would be then entered into another table, with the primary key of the first table.

Is this possible to do in a single stored procedure? I have implemented this using two different sp, but am wondering if it can be done other way?

thanks for your help!

Hello rasesh_dave,

Yes, you can do this with one stored procedure.

The newly inserted primary key value can be retrieved with @.@.Identity.

|||

Ofcourse, just put the two insert statements into one proc.

|||

yup, but how do i pass a list / array of items to be inserted into the second table?

|||

Post the code you currently have so we can see what you are doing..

|||

Okay,

I have two stored procs. The first one is

CREATE PROCEDURE [dbo].[sp_EventNew_Insert]

@.EventName VARCHAR(50),
@.StartDate DATETIME,
@.EndDate DATETIME,
@.Organiser VARCHAR(50),
@.Telephone VARCHAR(15),
@.Fax VARCHAR(15),
@.Email VARCHAR(50),
@.EventURL VARCHAR(50),
@.PendingVerification BIT,
@.SubmitersName VARCHAR(50),
@.SubmitersEmail VARCHAR(50),
@.EventVenue VARCHAR(50),
@.EventCity VARCHAR(50),
@.EventState VARCHAR(50),
@.CountryID VARCHAR(50),
@.SubmiterName VARCHAR(50),
@.SubmiterEmail VARCHAR(50)

AS

INSERT INTO tblEventTest (EventName, CountryID, StartDate, EndDate, Venue, City, State, Organiser, Telephone, Fax,
Email, EventWebsite, DisplayURL, PendingVerification, SubmitersName, SubmitersEmail)
VALUES (@.EventName, @.CountryID, @.StartDate, @.EndDate, @.EventVenue, @.EventCity, @.EventState, @.Organiser, @.Telephone, @.Fax, @.Email, @.EventURL, @.EventURL, @.PendingVerification,
@.SubmiterName, @.SubmiterEmail)
SELECT @.@.Identity as EventID
GO

which returns the EventID which is received by the DataAdapter class, and

then executes a loop for say 10 SiteID and calls another stored procedure for each siteID which is

CREATE PROCEDURE [dbo].[sp_EventSitesNew_Insert]
@.EventID INT,
@.SiteID INT,
@.LiveOnWeb BIT
AS

INSERT into tblEventSitesTest (EventID, SiteID, LiveOnWeb)
VALUES (@.EventID, @.SiteID, @.LiveOnWeb)
GO

is there a way i can pass an array of siteID into the sp, and execute a loop without generating a loop at the Data Adapter?

|||

Here's how I would modify your proc

(1) DO NOT name your procs with "sp_..". sp_ means system proc and SQL Server looks for the proc under master db thinking its a system proc and if it doesnt find there, it will look under the db you are running queries from. So this is an unnecessary overhead.

(2) Create a function that takes a concatenated string and returns a table of parsed values. Refer:http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx

(3) Finally modify the proc as follows:

CREATE PROCEDURE [dbo].[SP_EVENTNEW_INSERT]@.EventNameVARCHAR(50),@.StartDateDATETIME,@.EndDateDATETIME,@.OrganiserVARCHAR(50),@.TelephoneVARCHAR(15),@.FaxVARCHAR(15),@.EmailVARCHAR(50),@.EventURLVARCHAR(50),@.PendingVerificationBIT,@.SubmitersNameVARCHAR(50),@.SubmitersEmailVARCHAR(50),@.EventVenueVARCHAR(50),@.EventCityVARCHAR(50),@.EventStateVARCHAR(50),@.CountryIDVARCHAR(50),@.SubmiterNameVARCHAR(50),@.SubmiterEmailVARCHAR(50),@.SiteIDVarchar(100),@.LiveOnWebBITASBEGINSET NOCOUNT ONDeclare @.EventIdINTINSERT INTO tblEventTest (EventName, CountryID, StartDate, EndDate, Venue, City, State, Organiser, Telephone, Fax,Email, EventWebsite, DisplayURL, PendingVerification, SubmitersName, SubmitersEmail)VALUES (@.EventName, @.CountryID, @.StartDate, @.EndDate, @.EventVenue, @.EventCity, @.EventState, @.Organiser, @.Telephone, @.Fax,@.Email, @.EventURL, @.EventURL, @.PendingVerification,@.SubmiterName, @.SubmiterEmail)SELECT @.EventId = SCOPE_IDENTITY()Declare @.SiteIdTableTable (SiteIdINT)INSERT into @.SiteIdTableSELECT *FROM dbo.fnGetIDs (@.SiteID)INSERT into tblEventSitesTest (EventID, SiteID, LiveOnWeb)SELECT @.EventID, SiteID, @.LiveOnWebFROM @.SiteIdTableSET NOCOUNT OFFEND

|||

Thanks for the code, i am trying to implement it but is gives me an error

Server: Msg 208, Level 16, State 1, Procedure sp_EventNew_Insert, Line 35
Invalid object name 'dbo.fnGetIDs'.

I am trying this in query analyser! i dont know if i neet to add any udf as well with that?

|||

You need to create the function based on the code I provided in the article link.

|||

Thanks Dinakar,

I wonder how much efficient code a developer can write, if they know the system copletely!

|||

rasesh_dave:

Thanks Dinakar,

I wonder how much efficient code a developer can write, if they know the system copletely!

Definetely...