vbscript, New in VBScript - I need to intercept a SQL Stored Proc Return value

New in VBScript - I need to intercept a SQL Stored Proc Return value

Author	Message
Mariano Gomez Ben #1 / 5	New in VBScript - I need to intercept a SQL Stored Proc Return value Hi, This may be VBS 101 for many of you but I have the following: I have a SQL stored procedure as follow: create procedure sp_format_rec as /* This sproc contains to return statements / . . return -1 . . return 1 / End of the sproc */ In VBS, I would like to capture the returned values of this stored proc, so I created the following script: Dim ObjConn Set ObjConn = CreateObject("ADODB.Connection") ObjConn.Open "Provider=SQLOLEDB; Data Source ..." SQLStmt = "EXEC sp_format_rec" ObjConn.Execute SQLStmt,,AdExecuteNoRecords ObjConn.Close set ObjConn = Nothing Where or how do I capture the returned values? Much thanks for your time and help.
Tue, 08 Apr 2003 03:00:00 GMT

Chad Myer #2 / 5	New in VBScript - I need to intercept a SQL Stored Proc Return value Dim objConn Dim objRst Dim SQLStmt Dim iRetVal Set objConn = CreateObject("ADODB.Connection") Set objRst = CreateObject("ADODB.Recordset") ' By the way, you should use Server.CreateObject() if ' you're using ASP, or WScript.CreateObject() if you're ' using WSH here. Set objRST = ObjConn.Execute(SQLStmt) If Not objRst.EOF and Not objRst.BOF Then iRetVal = objRst("Return Value") End If objRst.Close ObjConn.Close Set objRst = Nothing set ObjConn = Nothing -Chad Quote: > Hi, > This may be VBS 101 for many of you but I have the following: > I have a SQL stored procedure as follow: > create procedure sp_format_rec as > /* This sproc contains to return statements / > . > . > return -1 > . > . > return 1 > / End of the sproc */ > In VBS, I would like to capture the returned values of this stored proc, so > I created the following script: > Dim ObjConn > Set ObjConn = CreateObject("ADODB.Connection") > ObjConn.Open "Provider=SQLOLEDB; Data Source ..." > SQLStmt = "EXEC sp_format_rec" > ObjConn.Execute SQLStmt,,AdExecuteNoRecords > ObjConn.Close > set ObjConn = Nothing > Where or how do I capture the returned values? > Much thanks for your time and help.
Tue, 08 Apr 2003 03:00:00 GMT

David Crow [MCSD #3 / 5	New in VBScript - I need to intercept a SQL Stored Proc Return value According to MSDN, you would assign the return value from ObjConn.Execute to a Recordset object as in: Dim objRecordset Set objRecordset = ObjConn.Execute SQLStmt,,AdExecuteNoRecords Then, you would look at objRecordset(0) to see what the stored procedure returned. However, I've not been able to get this to work unless the stored procedure was successful. If any of the T-SQL statements within the stored procedure generate an error (e.g., duplicate row), the remainder of the stored procedure, which includes the Return statement, is not executed. I am, however, able to pull information from the Connection object's Errors collection but I would rather know what error number was returned from the stored procedure. Quote: > Hi, > This may be VBS 101 for many of you but I have the following: > I have a SQL stored procedure as follow: > create procedure sp_format_rec as > /* This sproc contains to return statements / > . > . > return -1 > . > . > return 1 > / End of the sproc */ > In VBS, I would like to capture the returned values of this stored proc, so > I created the following script: > Dim ObjConn > Set ObjConn = CreateObject("ADODB.Connection") > ObjConn.Open "Provider=SQLOLEDB; Data Source ..." > SQLStmt = "EXEC sp_format_rec" > ObjConn.Execute SQLStmt,,AdExecuteNoRecords > ObjConn.Close > set ObjConn = Nothing > Where or how do I capture the returned values? > Much thanks for your time and help.
Tue, 08 Apr 2003 03:00:00 GMT

Mariano Gomez Ben #4 / 5	New in VBScript - I need to intercept a SQL Stored Proc Return value Much thanks for your input , It worked !. Quote: > Dim objConn > Dim objRst > Dim SQLStmt > Dim iRetVal > Set objConn = CreateObject("ADODB.Connection") > Set objRst = CreateObject("ADODB.Recordset") > ' By the way, you should use Server.CreateObject() if > ' you're using ASP, or WScript.CreateObject() if you're > ' using WSH here. > Set objRST = ObjConn.Execute(SQLStmt) > If Not objRst.EOF and Not objRst.BOF Then > iRetVal = objRst("Return Value") > End If > objRst.Close > ObjConn.Close > Set objRst = Nothing > set ObjConn = Nothing > -Chad > > Hi, > > This may be VBS 101 for many of you but I have the following: > > I have a SQL stored procedure as follow: > > create procedure sp_format_rec as > > /* This sproc contains to return statements / > > . > > . > > return -1 > > . > > . > > return 1 > > / End of the sproc */ > > In VBS, I would like to capture the returned values of this stored proc, so > > I created the following script: > > Dim ObjConn > > Set ObjConn = CreateObject("ADODB.Connection") > > ObjConn.Open "Provider=SQLOLEDB; Data Source ..." > > SQLStmt = "EXEC sp_format_rec" > > ObjConn.Execute SQLStmt,,AdExecuteNoRecords > > ObjConn.Close > > set ObjConn = Nothing > > Where or how do I capture the returned values? > > Much thanks for your time and help.
Tue, 08 Apr 2003 03:00:00 GMT

Jay Davi #5 / 5	New in VBScript - I need to intercept a SQL Stored Proc Return value Could you not also try this... Dim MyReturn 'Create a connection and execute SP with Parameters Set objCmd = Server.CreateObject("ADODB.Command") objCmd.ActiveConnection = objConn objCmd.CommandType = adCmdStoredProc objCmd.CommandText = mystoredproc_SP objCmd.Parameters.Append objCmd.CreateParameter("return", adInteger, adParamReturnValue, 4) objCmd.Parameters.Append objCmd.CreateParameter("myParam", adVarChar, adParamInput, 12, myparam) objCmd.Execute 'Set variable MyReturn equal to the value of the StoredProcedure return MyReturn = objCmd.Parameters("return").value If MyReturn = 0 Then WHATEVER end if If MyReturn > 0 Then WHATEVER end if This doesn't require the use of a recordset and I've found that it works well as long as the return value is always first. Is there a performance issue in creating it this way? jay Quote: > Hi, > This may be VBS 101 for many of you but I have the following: > I have a SQL stored procedure as follow: > create procedure sp_format_rec as > /* This sproc contains to return statements / > . > . > return -1 > . > . > return 1 > / End of the sproc */ > In VBS, I would like to capture the returned values of this stored proc, so > I created the following script: > Dim ObjConn > Set ObjConn = CreateObject("ADODB.Connection") > ObjConn.Open "Provider=SQLOLEDB; Data Source ..." > SQLStmt = "EXEC sp_format_rec" > ObjConn.Execute SQLStmt,,AdExecuteNoRecords > ObjConn.Close > set ObjConn = Nothing > Where or how do I capture the returned values? > Much thanks for your time and help.
Wed, 09 Apr 2003 03:00:00 GMT

Page 1 of 1

[ 5 post ]