Stored Procedures... driving me around the bend

Results 1 to 2 of 2

Thread: Stored Procedures... driving me around the bend

  1. #1
    Join Date
    Dec 1969

    Default Stored Procedures... driving me around the bend

    Right, firstly i&#039;m just going to declare my ignorance when it comes to stored procedures. I posted some of the problems I was having with stored procedures yesterday on the forum. I got alot of response but I was having trouble understanding them, so I just want to explain what I need the Stored procedure to do step by step and hopefully some one can help me. <BR><BR>1 Insert "UserName" into the table "tbl_Applicant_Users"<BR><BR>2 Call back the id "RegId" autonumber assigned to that row for table "tbl_Applicant_Users"<BR><BR>3 Insert the "RegId" (autonumber) called from the previous table into another table "tbl_Applicant_Personal" (thus linking them by a common id), along with other values (firstname,lastname etc. <BR><BR>I actually need to do this for more than 2 tables, but for the time being i&#039;m just trying to get the stored procedure to work. <BR><BR>Here is my stored procedure so far (it doesn&#039;t work):<BR><BR>CREATE PROCEDURE Applicant_AddUser_sp<BR>(<BR>@UserName nvarchar(50), <BR>@FirstName nvarchar(50), <BR>@LastName nvarchar(50), <BR>@RegId int output <BR>)<BR><BR>AS <BR><BR>INSERT INTO tbl_Applicant_Users (UserName) <BR>VALUES(@UserName) <BR>Select @RegId = @@identity <BR>GO <BR><BR>INSERT INTO tbl_Applicant_Personal (RegId,FirstName,LastName) <BR>VALUES (@RegId,@FirstName,@LastName) <BR>GO <BR><BR>The SP doesn&#039;t work. When I check the syntax it tells me to declare "RegID" so I add the line "declare @RegId int" and then I get another error message saying "incorrect syntax near declare, the variable @regid has already been declared? <BR><BR>I hope someone can help me, I have a SQL server book with examples on SPs but it doesn&#039;t seem to cover what I need to do here. <BR><BR>Thanks in advance<BR><BR>James<BR><BR><BR><BR><BR><BR><BR><B R><BR><BR><BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Stored Procedures... driving me around the ben

    There is an extra GO between the two inserts. This splits the statement into two batches, the first creating a stored proc which dows an insert into tbl_Applicant_Users, the second doing an insert into tbl_Applicant_Personal.<BR><BR>Just take out the GO and it should work fine.<BR><BR>Gavin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts