SP - Dazed and Confused

Results 1 to 4 of 4

Thread: SP - Dazed and Confused

  1. #1
    Rob Guest

    Default SP - Dazed and Confused

    Hello,<BR><BR>I am calling a sql 7.0 stored procedure (sp) from an active server page(asp). <BR><BR>The sp is a simple insert. I need to read the return the value of the sp in my asp. <BR><BR>If the insert is successful, my return value is coming back correctly (to whatever i set it)....but if there is an error such as a Uniqueness Constraint, I can&#039t get the return code(set in the SP) to come back to the ASP. It comes back blank. (The literature I&#039ve read says that processing should continue in the SP, so you can perform error processing...is that right?)<BR><BR>I set the return var in my ASP as:<BR>objCommand.Parameters.Append objCommand.CreateParameter("return",_<BR>adInteger ,adParamReturnValue,4) <BR>and read it back as:<BR>strReturn = objCommand.Parameters("return").Value<BR><BR><BR>I n my SP I simply do;<BR><BR>INSERT blah blah<BR>if @@error = 0<BR>return(100)<BR>else<BR>return(200)<BR><BR>(I don&#039t ever get back "200", even though I know it&#039s an error)<BR><BR>Any ideas???<BR><BR>Thanks for your help.

  2. #2
    Allen Hamlin Guest

    Default RE: SP - Dazed and Confused

    I think a Begin and End statement are necessary.<BR>I use two other variables @err = @@error and an integer I want to return ( IntReturn smallint )<BR>@err = @@error<BR>Begin<BR>if @err &#060;&#062; 0<BR>IntReturn = 100<BR>else<BR>IntReturn = 200<BR>END <BR>

  3. #3
    Allen Hamlin Guest

    Default An example of an SP

    CREATE PROCEDURE sp_delRate<BR>@IntRetVal integer OUTPUT,<BR>@HotelKey smallint<BR>AS<BR>-- this stored procedure deletes a rate<BR>begin<BR>declare @err smallint<BR> begin transaction<BR> delete from Rates where HotelKey = @HotelKey<BR> select @err = @@error <BR>-- now if there were no errors the rate information can be deleted<BR> if @err &#060;&#062; 0<BR><BR> Begin<BR> select @IntRetVal = -1<BR> Rollback Transaction <BR> End <BR> else<BR> Begin <BR> select @IntRetVal = 1<BR> Commit Transaction<BR> End<BR> <BR>End

  4. #4
    Allen Hamlin Guest

    Default An example of the ASP

    function public_delRate(HotelKey)<BR><BR>strConnString = "Provider=SQLOLEDB;SERVER=;DATABASE=;UID=;PWD="<BR > <BR> CONST adCmdStoredProc = 4 <BR> CONST adSmallInt = 2<BR> CONST adParamInput = 1<BR> CONST adParamOutput = 2<BR> CONST adInteger = 3<BR> CONST adParamReturnValue = 4<BR><BR>set delRate = CreateObject("adodb.connection")<BR> delRate.ConnectionString = strConnString<BR> delRate.Open<BR> <BR> set cmdDelete = CreateObject("adodb.command")<BR> cmdDelete.ActiveConnection = delRate<BR> cmdDelete.CommandText = "sp_delRate"<BR> cmdDelete.CommandType = adCmdStoredProc <BR> <BR> cmdDelete.Parameters.Append cmdDelete.CreateParameter (prmOutput , adInteger, adParamOutput , , intOutput) <BR> cmdDelete.Parameters.Append cmdDelete.CreateParameter (prmHotelKey , adSmallInt , adParamInput , 2 , HotelKey)<BR> <BR> cmdDelete.Execute<BR> <BR> intRetVal = cmdDelete.Parameters(1).Value <BR> if intRetVal &#062; 0 then<BR> msgbox "Successfully deleted the rate "<BR> ELSE<BR> msgbox "The delete failed..."<BR> end if<BR><BR>end function

Posting Permissions

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