Friday, February 24, 2012

Inserting Simultaneous tables using Stored Procedure

I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says"Incorrect syntax near '@.WIP'. Must declare the scalar variable "@.ECReason" and"@.WIP"." I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.

I made up the two tables just for testing, they are:

tbltest

ECID – int (PK)

ECReason – varchar(50)

tblWIP

WIPID – int (PK)

ECID – int

WIP - varchar(50)

Operation - varchar(50)

CREATE PROCEDUREtestInsert2(

@.ECReasonas varchar(50)
@.WIPas varchar(50)
@.Operationas varchar(50)
)
AS
BEGIN

DECLARE@.ECIDINT
INSERT INTOtbltest(ECReason)VALUES(@.ECReason)

SELECT@.ECID=@.@.IDENTITY

INSERT INTOtblWIP(ECID, WIP, Operation)
VALUES(@.ECID,@.WIP,@.Operation)

END

Thanks again for the help!

I think you forgot the commas between the proc's arguments. Try the following:

CREATE PROCEDURE testInsert2( @.ECReasonas varchar(50), @.WIPas varchar(50), @.Operationas varchar(50) )ASBEGIN DECLARE @.ECIDINT INSERT INTO tbltest(ECReason)VALUES(@.ECReason)SELECT @.ECID=@.@.IDENTITYINSERT INTO tblWIP(ECID, WIP, Operation)VALUES(@.ECID,@.WIP,@.Operation)END

By the way, I don't know about the "as" in the argument declarations. I never use it. I would normally do the following:

CREATE PROCEDURE testInsert2( @.ECReasonvarchar(50), @.WIPvarchar(50), @.Operationvarchar(50) )AS

HTH.

|||

I took the commas out and then it worked. I know I had them in there before, but I must have changed it along the way. I left the rest of it alone and it worked fine. Thanks for the help!

No comments:

Post a Comment