Stored Procedure Problem

Results 1 to 2 of 2

Thread: Stored Procedure Problem

  1. #1
    Join Date
    Dec 1969

    Default Stored Procedure Problem

    Hi all. I seem to be having a problem creating this stored procedure correctly. It seems to not be generating the build_ID. If I hard-code a build_ID into the insert transaction, then it works fine. What am I doing wrong?? Thanks in advance for all of the help<BR><BR>Stored Procedure:<BR>CREATE PROCEDURE sp_AddID (<BR> @BuildName varchar(250),<BR> @IntID int)<BR><BR>AS<BR><BR>DECLARE @BuildID int,<BR> @Error int<BR> <BR><BR> IF EXISTS (SELECT build_ID from build where build_nm=@BuildName)<BR><BR> SELECT @BuildID= @@IDENTITY<BR> <BR><BR>BEGIN<BR> <BR> <BR><BR>BEGIN TRANSACTION AddID <BR><BR> <BR><BR> INSERT INTO Int_FACILITY(Int_id, build_id, int_role_cd, int_cmnt) VALUES (@IntID, @BuildID, 0, @BuildName)<BR><BR> SELECT @Error = @@Error<BR><BR> IF @Error &#062; 0<BR> ROLLBACK TRANSACTION AddID<BR> ELSE<BR> COMMIT TRANSACTION AddID<BR><BR>END<BR><BR><BR>ASP Code that calls stored procedure:<BR>&#039define store procedure to insert new information<BR> Set cmd = server.CreateObject("ADODB.Command")<BR> <BR> cmd.CommandText = "sp_AddID"<BR> cmd.CommandType = adCmdStoredProc<BR> cmd.ActiveConnection=application("connstring")<BR> <BR> &#039BuildName<BR> Set param = cmd.CreateParameter("BuildName", adVarChar, adParamInput, 250, Request.Form("Location"))<BR> cmd.Parameters.Append param<BR> <BR> &#039IntID<BR> Set param = cmd.CreateParameter("IntID", adInteger, adParamInput, 4, session("strIntID"))<BR> cmd.Parameters.Append param<BR> <BR> cmd.Execute<BR> <BR> Set param = Nothing<BR> Set cmd = Nothing

  2. #2
    michal m Guest

    Default RE: Stored Procedure Problem

    Hi Kim,<BR>1)The @@identity global variable is set after you insert a record, but you are trying to query it without inserting in case 1 of yopur proc.<BR>2)In the second case you do not set the buildid at all.<BR>---<BR>Try revising your code to something like this:<BR>Stored Procedure:<BR>CREATE PROCEDURE sp_AddID (<BR>@BuildName varchar(250),<BR>@IntID int)<BR><BR>AS<BR><BR>DECLARE @BuildID int,<BR>@Error int<BR><BR><BR>IF EXISTS (SELECT build_ID from build where build_nm=@BuildName)<BR><BR>-- Get the existing build id<BR>SELECT @BuildID= build_ID from build where build_nm=@BuildName<BR><BR>BEGIN<BR><BR><BR><BR>BE GIN TRANSACTION AddID <BR><BR><BR><BR>INSERT INTO Int_FACILITY(Int_id, build_id, int_role_cd, int_cmnt) VALUES (@IntID, @BuildID, 0, @BuildName)<BR><BR>-- Get newly inserted build id<BR>SELECT @BuildID= @@IDENTITY<BR><BR>SELECT @Error = @@Error<BR><BR>IF @Error &#062; 0<BR>ROLLBACK TRANSACTION AddID<BR>ELSE<BR>COMMIT TRANSACTION AddID<BR><BR>END<BR>

Posting Permissions

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