insert stored procedure

  • I have an unbound form and after the user enters data, I need to add data to three different tables, adding a new row in each of those tables. I call this SP from ADO code (in MS Access). I am just learning to write SPs.

    Here is my SP so far:

    CREATE PROCEDURE dbo.proc_FacilityAddressInsert

    @EmpCreated varchar(10),

    @FAddress varchar(100),

    @FCity varchar(30),

    @FState varchar(5),

    @FZip varchar (9),

    @FName varchar (100),

    @FType smallint,

    @FSelect smallint,

    @NewAddressID int OUTPUT

    AS

    INSERT INTO Address (EmpCreated, EmpUpdated, Address1, City, PState, Zip)

    VALUES (@EmpCreated, @EmpCreated, @FAddress, @FCity, @FState, @FZip)

    SET @NewAddressID=SCOPE_IDENTITY()

    GO

    INSERT INTO Facility (EmpCreated, EmpUpdated, FacilityName, FacilityType, FacSelect)

    VALUES (@EmpCreated, @EmpCreated, @FName, @FType, @FSelect)

    GO

    I get a syntax error saying I haven't defined the variables @FName and so forth. What am I missing?

    Thanks so much!!

    SMK

     

  • GO's are T-SQL batch separators.  As a result, the SQL you mentioned above is actually 2 pieces.  The first gives you a stored procedure that inserts into Address and only Address.  The 2nd batch then is executed completely independent of the first.  Taken this way, you should then see how SQL Server will error as you have not declared any of the variables for the 2nd batch.

    So, how do you solve this?  Well, simply take out the first GO.  That being said, you really should at the very least add error handling.  You might also look at adding a Begin Tran and Commit Tran to the stored procedure (just in case you are not handling errors in your calling method).

    If you need me to elaborate more just say so.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply