We help IT Professionals succeed at work.

Returning Return From SP

thunderchicken
on
I am trying to find out if a Table Exists in a Stored Procedure:

CREATE PROCEDURE spu_checkLogExist  
(
@Return int
)
AS

if object_id('tblLog102001') is null
     Return(0)
else
     Return(1)


How do I know in VB what is being returned?

...
set rs = server.CreateObject("ADODB.Command")
With rs
     .ActiveConnection = cn
     .CommandText = "spu_checklogexist"
     .Execute
End With
...
Comment
Watch Question

Commented:
It is possible to enter an output parameter or that the stored procedure returned recordset.

CREATE PROCEDURE spu_checkLogExist
(
@Return int output
)
AS

if object_id('tblLog102001') is null
set @Return=0
else
set @Return=1

or

CREATE PROCEDURE spu_checkLogExist
(
@Return int
)
AS

if object_id('tblLog102001') is null
select '0'
else
select '1'

...
dim bbbrec as adodb.recordset
set rs = server.CreateObject("ADODB.Command")
With rs
.ActiveConnection = cn
.CommandText = "spu_checklogexist"
set bbbrec=.Execute
End With
...
Commented:
You can use a parameter of the command object with a direction of adParamReturnValue.
e.g.
set cmd = server.CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = cn
    .CommandText = "spu_checklogexist"
    .commandType=adCmdStoredProc
    .parameters.add .createParameter("RETURN_VALUE",adInteger,adParamReturnValue,4)
    .Execute
    msgbox .parameters("RETURN_VALUE").value
End With

Author

Commented:
I think I'm still lost (BTW, using VBScript since this is more active and needing this answer right away)

What I'm trying to do is to find out if a table exists in SQL 7, if it doesn't I'll create it, so I have to have a stored procedure to check this, then trying to pass back if it is or isn't.  I tried both codes and had errors...

kdg2000, how do I find out what was passed from the stored procedure?

andyclap,

ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

This is the code so far:

set rs = server.CreateObject("ADODB.Command")
With rs
     .ActiveConnection = cn
     .CommandText = "spu_checklogexist"
     .commandType=adCmdStoredProc '<-- Error Here
     .parameters.add .createParameter("RETURN_VALUE",adInteger,adParamReturnValue,4)
     .Execute
     'intVal = .Parameters("@Return")
End With


Nothing set in stone, just need it to work.
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

You are getting above error because you are using the ADO constant declared in "adovbs.inc" file, which is not included in your asp page.

Search for the above and include it.

Commented:
Aha - jsut noticed this is an ASP/VBS question, so adParamReturnValue and adInteger are not defined.
You can either set it up manaually (adInteger=3, adParamReturnValue=4), or use the include file from
http://www.asp101.com/articles/john/adovbs/adovbs.inc

Author

Commented:
set rs = server.CreateObject("ADODB.Command")
With rs
     .ActiveConnection = cn
     .CommandText = "spu_checklogexist"
     .CommandType =  &H0004
     .parameters.add .createParameter("RETURN_VALUE",3,4,4)
     .Execute
     'intVal = .Parameters("@Return")
End With

Microsoft VBScript runtime (0x800A01B6)
Object doesn't support this property or method: 'add'

Author

Commented:
Figured it out from your code, thanks
If anyone here has any ideas on the following question (very similar to what is posted here), then I would certainly appreciate any comments.
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?qid=20269731

Explore More ContentExplore courses, solutions, and other research materials related to this topic.