?
Solved

return value from a stored procedure

Posted on 2005-02-28
36
Medium Priority
?
3,514 Views
Last Modified: 2012-05-05
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.


0
Comment
Question by:ericworldz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 9
  • 8
  • +2
36 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13426247
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
 
LVL 11

Expert Comment

by:ram2098
ID: 13426251
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
 

Author Comment

by:ericworldz
ID: 13426292
Thanks for the fast response. I have tried both suggestion and none works.


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:ram2098
ID: 13426305
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13426318
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13426327
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
 

Author Comment

by:ericworldz
ID: 13426347
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
 
LVL 13

Expert Comment

by:KarinLoos
ID: 13427228
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13429541
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
 

Author Comment

by:ericworldz
ID: 13472516
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
 

Author Comment

by:ericworldz
ID: 13472792
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13473351
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13473367
>>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
 

Author Comment

by:ericworldz
ID: 13473584
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13473712
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
 

Author Comment

by:ericworldz
ID: 13473960
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13474013
>>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
 
LVL 13

Expert Comment

by:KarinLoos
ID: 13474886
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
 

Author Comment

by:ericworldz
ID: 13476050
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
 
LVL 13

Expert Comment

by:KarinLoos
ID: 13476163
<< 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
 

Author Comment

by:ericworldz
ID: 13477168
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
 

Author Comment

by:ericworldz
ID: 13477386
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
 

Author Comment

by:ericworldz
ID: 13485814
any ideas?
0
 
LVL 13

Accepted Solution

by:
KarinLoos earned 300 total points
ID: 13486868
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
 
LVL 13

Expert Comment

by:KarinLoos
ID: 13486920
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
 

Author Comment

by:ericworldz
ID: 13487557
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
 

Author Comment

by:ericworldz
ID: 13488358
increase to 100. hoping someone can help me resolve this bug quickly.
0
 
LVL 13

Expert Comment

by:KarinLoos
ID: 13488609
in your simple example please post your asp code as you have it now
0
 
LVL 13

Expert Comment

by:KarinLoos
ID: 13488619
also do you have sql2000 or sql 7.0
0
 

Author Comment

by:ericworldz
ID: 13490167
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
 
LVL 13

Expert Comment

by:KarinLoos
ID: 13490280
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
 

Author Comment

by:ericworldz
ID: 13491946
yes
0
 

Author Comment

by:ericworldz
ID: 13492039
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
 
LVL 1

Expert Comment

by:MrLogin
ID: 20775697
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20783021
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
 
LVL 1

Expert Comment

by:MrLogin
ID: 20785313
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question