return value from a stored procedure

I want to get a return value from a stored procedure, but it's not working at all. When I print out the value in ASP, it's empty.

ASP Code:

oCmd.Parameters.Append(oCmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4))

errCode = oCmd.Parameters("RETURN_VALUE")
response.write ("errCode="&strErrCode)

SQL Stored Procedure:

begin
      declare @exists int

if exists (Select 1 From cases Where streetnum=@streetnum and streetname=@streetname and roads=@roads)
      select @exists = 55555

return @exists


Please help. Thanks.


ericworldzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
You may get more response in a more appropriate Topic Area such as:
http://www.experts-exchange.com/Web/Web_Languages/ASP/

Having said that, you need to change:
oCmd.Parameters.Append(oCmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4))

To:

oCmd.Parameters.Append oCmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)

Also, if that does not solve it, post all the relevant ASP code and the stored Procedure, so that we can see what is wrong.
0
ram2098Commented:
Not sure about ASP side...

but from SQL side, make sure it always returns a value...(initiate it to some value)

begin
     declare @exists int

select @exists = 1

if exists (Select 1 From cases Where streetnum=@streetnum and streetname=@streetname and roads=@roads)
     select @exists = 55555

return @exists
0
ericworldzAuthor Commented:
Thanks for the fast response. I have tried both suggestion and none works.


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ram2098Commented:
Then..the problem would be on ASP side...


so..as Acperkin said ...better if you post this on ASP topic...
 
http://www.experts-exchange.com/Web/Web_Languages/ASP/
0
Anthony PerkinsCommented:
>>Thanks for the fast response. I have tried both suggestion and none works.<<
That is to be expected, without having all the facts.  Now, if you can post as I requested we should be able to reach a solution as this is a trivial problem.
0
Anthony PerkinsCommented:
I will make one more WAG before I move on:  Make sure that the ADO parameters are declared, either using a Meta tag or using the ADOVBS.INC include file.
0
ericworldzAuthor Commented:
here's the asp code:

set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = objConn
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "sp_createReferralWithCaseNumber"

'Set parameters
oCmd.Parameters.Append(oCmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4))
oCmd.Parameters.Append(oCmd.CreateParameter("@casenumber",adVarChar,adParamInput,20,strCaseNumber))
oCmd.Parameters.Append(oCmd.CreateParameter("@referral",adVarChar,adParamInput,100,strReferral))

'Execute the stored procedure
set oRSInsert = oCmd.Execute
On Error Resume Next
errCode = oCmd.Parameters("RETURN_VALUE")
response.write ("errCode="&strErrCode)



ms sql stored procedure:

CREATE procedure dbo.sp_createReferralWithCaseNumber
@casenumber varchar(20),
@referral varchar(100)

as
begin
      declare @exists int
      select @exists = 1
if exists (Select 1 From cases Where streetnum=@streetnum and streetname=@streetname and roads=@roads)
      select @exists = 55555
...

return @exists

end


0
KarinLoosCommented:
well for starters put begin and end around the statements the sql must execute if exists condition is true

CREATE procedure dbo.sp_createReferralWithCaseNumber
@casenumber varchar(20),
@referral varchar(100)

as
begin
     declare @exists int
     select @exists = 1
if exists (Select 1 From cases Where streetnum=@streetnum and streetname=@streetname and roads=@roads)
  BEGIN
     select @exists = 55555
     ...
 END
 return @exists
end
0
Anthony PerkinsCommented:
Again, without seeing the WHOLE stored procedure, it is difficult to know what your intent is, so we can only guess.

In any case make the following changes:

set oCmd = Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = objConn                                  ' if objConn is a Connection object than make this change
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "sp_createReferralWithCaseNumber"

'Set parameters
oCmd.Parameters.Append oCmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)                     ' Correct this syntax
oCmd.Parameters.Append oCmd.CreateParameter("@casenumber",adVarChar,adParamInput,20,strCaseNumber)          ' Correct this syntax
oCmd.Parameters.Append oCmd.CreateParameter("@referral",adVarChar,adParamInput,100,strReferral)                      ' Correct this syntax

'Execute the stored procedure
oCmd.Execute ,, adExecuteNoRecords                                                      ' Change this
' On Error Resume Next                                                                          ' Lose this
errCode = oCmd.Parameters("RETURN_VALUE")
response.write ("errCode="&strErrCode)
0
ericworldzAuthor Commented:
what's wrong with the syntax?

oCmd.Parameters.Append oCmd.CreateParameter("@casenumber",adVarChar,adParamInput,20,strCaseNumber)          ' Correct this syntax
oCmd.Parameters.Append oCmd.CreateParameter("@referral",adVarChar,adParamInput,100,strReferral)                      ' Correct this syntax


change this to what?

oCmd.Execute ,, adExecuteNoRecords                                                      ' Change this


i'm still not able to get a return value. this is very frustrating.
0
ericworldzAuthor Commented:
the problem seems like to be in the stored procedure, because it tries to insert a record into the table. that means the " if exists..." doesn't work. and it skips the "if exists..." part and goes directly to the else ....  I have tested out the sql statement and it seems fine.


as
begin
      declare @exists int
      select @exists = 1
if exists (Select 1 From cases Where streetnum='@streetnum' and streetname='@streetname' and roads='@roads')
      begin
            select @exists = 55555
            return @exists
      end
else
      begin
                   insert into cases (casenumber, streetnum, streetname, roads, apt, city, state, zip)
            values (@casenumber, @streetnum, @streetname, @roads, @apt, @city, @state, @zip)
      end
0
Anthony PerkinsCommented:
>>what's wrong with the syntax?<<
Nothing.  Yours was wrong.

>>change this to what?<<
Change your code:
set oRSInsert = oCmd.Execute

To:
oCmd.Execute ,, adExecuteNoRecords      
0
Anthony PerkinsCommented:
>>the problem seems like to be in the stored procedure<<
I agree.  I wish we could help you without seeing the WHOLE stored procedure, unfortunately while many of us are pretty good at debugging stored procedures, very few are clairvoyant.
0
ericworldzAuthor Commented:
here's the full stored procedure:


CREATE procedure dbo.sp_createReferralWithCaseNumber
@casenumber varchar(20),
@referral varchar(100),
@email varchar(50),
@clientfirstname varchar(50),
@clientlastname varchar(50),
@streetnum varchar(4),
@streetname varchar(20),
@roads varchar(8),
@apt varchar(5),
@city varchar(50),
@state varchar(20),
@zip varchar(12),
@homephone varchar(12),
@workphone varchar(12),
@cellphone varchar(12),
@primarylanguage varchar(50),
@rodents varchar(8),
@cockroaches varchar(8),
@mold varchar(8),
@moisture varchar(8),
@comments varchar(2000),
@consent varchar(8),
@IsdDate varchar(12),
@inspection varchar(8),
@InspectionDate varchar(12),
@inspector varchar(50)

as
begin
      declare @exists int
      select @exists = 1
if exists (Select 1 From cases Where streetnum='@streetnum' and streetname='@streetname' and roads='@roads')
      begin
            select @exists = 55555
            return @exists
      end
else
      
                   insert into cases (casenumber, referral, email, clientfirstname, clientlastname,
            streetnum, streetname, roads, apt, city, state, zip, homephone, workphone, cellphone, primarylanguage,
            rodents, cockroaches, mold, moisture, comments, consent, IsdDate, inspection,
            InspectionDate, inspector)
            values (@casenumber, @referral, @email, @clientfirstname, @clientlastname,
            @streetnum, @streetname, @roads, @apt, @city, @state, @zip, @homephone, @workphone, @cellphone,
            @primarylanguage, @rodents, @cockroaches, @mold, @moisture, @comments,
            @consent, @IsdDate, @inspection, @InspectionDate, @inspector)
      
if not exists (Select 1 From isd Where casenumber='@casenumber')
      begin
                   insert into isd (caseID, casenumber, inspector, InspectionDate)
            values (@@identity, @casenumber, @inspector, @InspectionDate)
      end
else
      return @exists

end
GO
0
Anthony PerkinsCommented:
Make the following change to your stored procedure:

CREATE procedure dbo.sp_createReferralWithCaseNumber
@casenumber varchar(20),
@referral varchar(100),
@email varchar(50),
@clientfirstname varchar(50),
@clientlastname varchar(50),
@streetnum varchar(4),
@streetname varchar(20),
@roads varchar(8),
@apt varchar(5),
@city varchar(50),
@state varchar(20),
@zip varchar(12),
@homephone varchar(12),
@workphone varchar(12),
@cellphone varchar(12),
@primarylanguage varchar(50),
@rodents varchar(8),
@cockroaches varchar(8),
@mold varchar(8),
@moisture varchar(8),
@comments varchar(2000),
@consent varchar(8),
@IsdDate varchar(12),
@inspection varchar(8),
@InspectionDate varchar(12),
@inspector varchar(50)

as
begin
      declare @exists int
      select @exists = 1
      if exists (Select 1 From cases Where streetnum='@streetnum' and streetname='@streetname' and roads='@roads')
     begin
          select @exists = 55555
          return @exists
     end
      else
                  insert into cases (casenumber, referral, email, clientfirstname, clientlastname,
                              streetnum, streetname, roads, apt, city, state, zip, homephone, workphone, cellphone, primarylanguage,
                              rodents, cockroaches, mold, moisture, comments, consent, IsdDate, inspection,
                              InspectionDate, inspector)
                              values (@casenumber, @referral, @email, @clientfirstname, @clientlastname,
                              @streetnum, @streetname, @roads, @apt, @city, @state, @zip, @homephone, @workphone, @cellphone,
                              @primarylanguage, @rodents, @cockroaches, @mold, @moisture, @comments,
                              @consent, @IsdDate, @inspection, @InspectionDate, @inspector)
     
      if not exists (Select 1 From isd Where casenumber='@casenumber')
            begin
                  insert into isd (caseID, casenumber, inspector, InspectionDate)
                                     values (@@identity, @casenumber, @inspector, @InspectionDate)
                        return @exists                       -- Add this line so that at least you get a value back
            end
      else
            return @exists

End
GO


Call the above stored procedure as follows:

Set oCmd = Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = objConn                                  ' if objConn is a Connection object than make this change
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "sp_createReferralWithCaseNumber"

'Set parameters
oCmd.Parameters.Append oCmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)
oCmd.Parameters.Append oCmd.CreateParameter("@casenumber", adVarChar, adParamInput,20, strCaseNumber)
oCmd.Parameters.Append oCmd.CreateParameter("@referral", adVarChar, adParamInput, 100, strReferral)
oCmd.Parameters.Append oCmd.CreateParameter("@email", adVarChar, adParamInput, 50, ValueOfEmailGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@clientfirstname", adVarChar, adParamInput, 50, ValueOfclientfirstnameGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@clientlastname", adVarChar, adParamInput, 50, ValueOfclientlastnameGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@streetnum", adVarChar, adParamInput, 4, ValueOfstreetnumGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@streetname", adVarChar, adParamInput, 20, ValueOfstreetnameGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@roads", adVarChar, adParamInput, 8, ValueOfroadsGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@apt", adVarChar, adParamInput, 5, ValueOfaptGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@city", adVarChar, adParamInput, 50, ValueOfcityGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@state", adVarChar, adParamInput, 20, ValueOfstateGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@zip", adVarChar, adParamInput, 12, ValueOfzipGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@homephone", adVarChar, adParamInput, 12, ValueOfhomephoneGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@workhone", adVarChar, adParamInput, 12, ValueOfworkphoneGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@cellphone", adVarChar, adParamInput, 12, ValueOfcellphoneGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@primarylanguage", adVarChar, adParamInput, 50, ValueOfprimarylanguageGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@rodents", adVarChar, adParamInput, 8, ValueOfrodentsGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@cockroaches", adVarChar, adParamInput, 8, ValueOfcockroachesGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@mold", adVarChar, adParamInput, 8, ValueOfmoldGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@moisture", adVarChar, adParamInput, 8, ValueOfmoistureGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@comments", adVarChar, adParamInput, 2000, ValueOfcommentsGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@consent", adVarChar, adParamInput, 8, ValueOfconsentGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@IsdDate", adVarChar, adParamInput, 12, ValueOfIsdDateGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@inspection", adVarChar, adParamInput, 8, ValueOfinspectionGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@InspectionDate", adVarChar, adParamInput, 12, ValueOfInspectionDateGoesHere)
oCmd.Parameters.Append oCmd.CreateParameter("@inspector", adVarChar, adParamInput, 8, ValueOfinspectorGoesHere)

'Execute the stored procedure
oCmd.Execute ,, adExecuteNoRecords

errCode = oCmd.Parameters("RETURN_VALUE").Value
response.write "errCode=" & strErrCode
0
ericworldzAuthor Commented:
acperkins,

you have this :
'Set parameters
oCmd.Parameters.Append oCmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)

>> note the @RETURN_VALUE

and

errCode = oCmd.Parameters("RETURN_VALUE").Value

those two have to match?


also i have tried exactly what you suggested, but no cigars.

as i have mentioned above, the "if statement" in the stored procedure is executed, because i'm still getting a duplicate key error return back to the browser.

0
Anthony PerkinsCommented:
>>those two have to match?<<
Yes.  It was a typo.

>>the "if statement" in the stored procedure is executed,<<
Which if statement (there are two)?

>> i'm still getting a duplicate key error return back to the browser.<<
You need to take a look at the structure of the table in question (cases or isd) and see which unique index is getting violated.  This will give  you a clue where you need to modify your stored procedure.
0
KarinLoosCommented:
mm well  
<<  if exists (Select 1 From cases Where streetnum='@streetnum' and streetname='@streetname' and roads='@roads') >>
AND
<<  if not exists (Select 1 From isd Where casenumber='@casenumber')>>
I suspect are not producing the correct results... try REMOVING the apostrophe around the parameters..

IE
 if exists (Select 1 From cases Where streetnum=@streetnum and streetname=@streetname and roads=@roads)

AND
 if not exists (Select 1 From isd Where casenumber=@casenumber)

That might help.




0
ericworldzAuthor Commented:
it's violating the 1st "if statement", it tries to insert into the "cases" table.

i put apostrophe around the parameters, because they are all varchars.

0
KarinLoosCommented:
<< i put apostrophe around the parameters, because they are all varchars. >>

you are passing in a paramters, DONT put the apostrophes around the parameters, they are already
varchars.  You only put apostrophes around actual strings  ie  street = 'abc'


0
ericworldzAuthor Commented:
i remove the apostrophe in the stored procedures. now i no longer get the "primary duplicate key" error return to the web browser because i believe it has skipped the "if statement".

now it's trying to print out the errCode, which is still getting a blank value.

0
ericworldzAuthor Commented:
my thought:

since i declare a variable in stored procedure: @exists

in the asp, should i be using the same variable - @exists instead of @RETURN_VALUE?

does it matter?
0
ericworldzAuthor Commented:
any ideas?
0
KarinLoosCommented:
no, you shouldnt , @exists is an internal variable within the sp.
made the following changes ( see the remarks)
could you try this -

begin
     declare @exists int  --<<<< INTERNAL varaiable
     SET @exists = 1     -- <<<< initialise to 1  -- CHANGED IT TO SET as opposed to SELECT
   
    if exists (Select 1 From cases Where streetnum=@streetnum and streetname=@streetname and roads=@roads)
     begin
           ---- IN THIS CASE the condition is true so NOTHING is done
          set @exists = 55555
          return @exists        -- <<<<  RETURN does an immediate exit out of the sp with this return code ( ie 55555)
     end
     else
      BEGIN   ---<<< ADDED
               insert into cases (casenumber, referral, email, clientfirstname, clientlastname,
                         streetnum, streetname, roads, apt, city, state, zip, homephone, workphone, cellphone, primarylanguage,
                         rodents, cockroaches, mold, moisture, comments, consent, IsdDate, inspection,
                         InspectionDate, inspector)
                         values (@casenumber, @referral, @email, @clientfirstname, @clientlastname,
                         @streetnum, @streetname, @roads, @apt, @city, @state, @zip, @homephone, @workphone,
                          @cellphone,
                         @primarylanguage, @rodents, @cockroaches, @mold, @moisture, @comments,
                         @consent, @IsdDate, @inspection, @InspectionDate, @inspector)
     
                     if not exists (Select 1 From isd Where casenumber=@casenumber)
                        begin
                               insert into isd (caseID, casenumber, inspector, InspectionDate)
                                            values (@@identity, @casenumber, @inspector, @InspectionDate)
                                           ---- REMOVED THE RETURN
                         end
                     ---- REMOVED THE ELSE NO NEED FOR IT

              RETURN @exists   --- immediate exit  return code = 1  as per initialised
       END  -- <<< ADDED
End
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KarinLoosCommented:
note on the ado return_value parm :

<<oCmd.Parameters.Append oCmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)>>
<<errCode = oCmd.Parameters("RETURN_VALUE").Value>>

If you are using SQL2000 you must reference it as @RETURN_VALUE  if you are using SQL 7.0 then "RETURN_VALUE"

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q285295

0
ericworldzAuthor Commented:
i try running a very simple test sp, and still getting no value

CREATE procedure dbo.sp_test

as
Begin
     declare @exists int
     set @exists = 1
          return @exists
End
GO

0
ericworldzAuthor Commented:
increase to 100. hoping someone can help me resolve this bug quickly.
0
KarinLoosCommented:
in your simple example please post your asp code as you have it now
0
KarinLoosCommented:
also do you have sql2000 or sql 7.0
0
ericworldzAuthor Commented:
oCmd.ActiveConnection = objConn
oCmd.CommandType = adCmdStoredProc

oCmd.CommandText="sp_test"

oCmd.Parameters.Append oCmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)

 oCmd.Execute ,, adExecuteNoRecords

            errCode = oCmd.Parameters("@RETURN_VALUE").Value
            response.write "errCode=" & strErrCode


and i'm running sql2000
0
KarinLoosCommented:
can you test the following in query analyser

declare @rtnvalue int
exec @rtnvalue = sp_test

select convert(varchar(2), @rtnvalue)

does it give you a result of 1 ?
0
ericworldzAuthor Commented:
yes
0
ericworldzAuthor Commented:
i have found the bug!

errCode = oCmd.Parameters("@RETURN_VALUE").Value
          response.write "errCode=" & strErrCode


that's the bug

errCode

and trying to print out strErrCode
0
MrLoginCommented:
One other thing to look out for:   "SQL Server Returns Output Parameters Only After Resultsets" so set   connection.CursorLocation = adUseClient   to force everything back. Reference -
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q256234&
0
Anthony PerkinsCommented:
MrLogin

Not sure if you realized:
1.  There is NO resultset involved in this thread.  Take a look at the Stored Procedure and you will see what I mean.  It is just simple INSERT statements.
2.  The thread is nearly 3 years old.  Were you on some archealogical trip?
0
MrLoginCommented:
Was using a return code to flag the absence of a result set from a stored proc but the rc was not set until I included adUseClient - even when no result set was present.      Presume this is an implementation issue.

Also when returning multiple record sets about 3% of the time some of the later record sets would not return (remote db server).  The use of adUseClient appears to have resolved this as well - if I encounter further issues here will raise as a new question.


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.