accessing output values from a SP
-
DB: MS SQL server 2k
Lang: VB 6.0
API: ADO2.6
query:---
I have a SP which accepts Inputs (empID, name,....,RETempID OUTPUT)
This SP does eithter 1 of the below 2 operations --
1.)UPdate - If empID already exists in the emp table & return the empID thro RETEmpID
2.)Insert - If the empID deosnot exist in the table & return the New empID thro the RETEmpID
Problem:
Not able to access the OUTPUT parameter in VB
but able to access it in SQL Query analyser
please reply ASAP
-
How are you executing the procedure from your VB application? If you aren't already doing it, use a command-object and like this:
cmd.Parameters.Append cmd.CreateParameter("@RETEmpID", adInteger, adParamOutput, 4, MyLongVariable)
cmd.Execute()
MyLongVariable = cmd.Parameters("@RETEmpID")
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
i am using the command object....& doing what u suggested
after i execute the SP thro the command.execute method....& check for the number of recs affected...it shows properly....but this method is not returning the recordset object....as it should.....& so i am not able to access the OUTPUT values from the SP....as the doc says that i need to close this recordset before accessing the OUTPUT values from the SP......
-
Why you care about a recordset if you're using output parameters? You normally use them to avoid creating a recordset.
Andy
-
What does returning a recordset have to do with retreiving the output parameters? You do not access the output parameters via a recordset you access them as Chris as shown above with: MyLongVariable = cmd.Parameters("@RETEmpID")
If you can't figure it out you may need to post a snipet of your code so we can see what you are doing.
-
Here is the SP i am using
--------------------------------------------
CREATE PROCEDURE dbo.sp_AddUpdateEmpTable
@EmpName nvarchar(50),
@EmpID int,
@returnEmpID int output
AS
BEGIN
If exists(select * from EmpTable where EmpID= @EmpID)
BEGIN
Update EmpTable
Set EmpName=@EmpName
Where EmpID = @EmpID
select @returnEmpID= @EmpID
END
Else
BEGIN
Insert into EmpTable(EmpName)
Values (@EmpName)
select @returnEmpID= @@identity
END
END
GO
---------------------------------------------
Here is the VB code i am using to access the SP...
NOTE: this is code is part of a COMplus
component...but i have made changes so that it will run outside COMplus
---------------------------------------------
Public Function AddUpdateEmpTable(ByVal EmpID As Integer, ByVal EmpName As String) As Integer
On Error GoTo AddUpdateEmpTableErr
Dim oAddUpdateEmpTableCon As New ADODB.Connection
Dim oAddUpdateEmpTableRs As New ADODB.Recordset
Dim oAddUpdateEmpTableCom As New ADODB.Command
Dim ReturnEmpID As Integer
Dim EmpIDPrm As Parameter, EmpNamePrm As Parameter, ReturnEmpIDPrm As Parameter, ReturnScenarioDProcCEIDIDPrm As Parameter
Set oAddUpdateEmpTableCon = EstablishConnection
Set ObjContext = GetObjectContext
Set contextState = ObjContext
Set oAddUpdateEmpTableCom.ActiveConnection = oAddUpdateEmpTableCon
oAddUpdateEmpTableCom.CommandText = "sp_AddUpdateEmpTable"
oAddUpdateEmpTableCom.CommandType = adCmdStoredProc
Set EmpIDPrm = oAddUpdateEmpTableCom.CreateParameter("EmpID", adInteger, adParamInput)
oAddUpdateEmpTableCom.Parameters.Append EmpIDPrm
EmpIDPrm.Value = EmpID
Set EmpNamePrm = oAddUpdateEmpTableCom.CreateParameter("EmpName", 202, adParamInput,50)
oAddUpdateEmpTableCom.Parameters.Append EmpNamePrm
EmpNamePrm.Value = EmpName
Set ReturnEmpIDPrm = oAddUpdateEmpTableCom.CreateParameter("ReturnEmpID", adInteger, adParamOutput)
oAddUpdateEmpTableCom.Parameters.Append ReturnEmpIDPrm
ReturnEmpIDPrm.Value = ReturnEmpIDID
Set oAddUpdateEmpTableRs = oAddUpdateEmpTableCom.Execute
Debug.Print oAddUpdateEmpTableRs.State
If oAddUpdateEmpTableRs.State = 1 Then
oAddUpdateEmpTableRs.Close
Debug.Print oAddUpdateEmpTableCom.Parameters("ReturnEmpID")
AddUpdateScenarioDProcCEID = oAddUpdateEmpTableCom.Parameters("ReturnEmpID")
Else
Debug.Print oAddUpdateEmpTableCom.Parameters("ReturnEmpID")
Err.Raise 9999, "cFactScenario:AddUpdateEmpTable Method", "AddUpdateEmpTable SP failed"
End If
oAddUpdateEmpTableCon.Close
Set oAddUpdateEmpTableCon = Nothing
Set oAddUpdateEmpTableCom = Nothing
Set oAddUpdateEmpTableRs = Nothing
Exit Function
AddUpdateEmpTableErr:
oAddUpdateEmpTableCon.Close
Set oAddUpdateEmpTableCon = Nothing
Set oAddUpdateEmpTableCom = Nothing
Set oAddUpdateEmpTableRs = Nothing
Debug.Print Err.Number & Err.Description & Err.Source
Debug.Print Err.Number & Err.Description & Err.Source
Call RaiseError(Err.Number, "cFactScenario:oAddUpdateEmpTable Method", Err.Description)
End Function
---------------------------------------------
really appreciate ur help
rgds,
van
-
you must return the value from your stored procedure:
return @returnEmpID
-
I have two recommendations for you to solve this:
1) Add SET NOCOUNT xx statements to your procedure (maybe not necessary, but I would do it anyway) so that your procedure looks like below. Then skip your recordset, so that your execute functionality looks like below.
--------------------------
CREATE PROCEDURE dbo.sp_AddUpdateEmpTable
@EmpName nvarchar(50),
@EmpID int,
@returnEmpID int output
AS
BEGIN
SET NOCOUNT ON
If exists(select * from EmpTable where EmpID= @EmpID)
BEGIN
Update EmpTable
Set EmpName=@EmpName
Where EmpID = @EmpID
select @returnEmpID= @EmpID
END
Else
BEGIN
Insert into EmpTable(EmpName)
Values (@EmpName)
select @returnEmpID= @@identity
END
SET NOCOUNT OFF
END
GO
--------------------------
VB code:
<snip>
Set ReturnEmpIDPrm = oAddUpdateEmpTableCom.CreateParameter("ReturnEmpID", adInteger, adParamOutput)
oAddUpdateEmpTableCom.Parameters.Append ReturnEmpIDPrm
ReturnEmpIDPrm.Value = ReturnEmpIDID
oAddUpdateEmpTableCom.Execute , , adExecuteNoRecords
AddUpdateScenarioDProcCEID = oAddUpdateEmpTableCom.Parameters("ReturnEmpID")
</snip>
2. The other solution is to use return as Jonathan recommended. Change your procedure and VB code to below. I think you have to create your return parameter first in the VB code.
--------------------------------------------
CREATE PROCEDURE dbo.sp_AddUpdateEmpTable
@EmpName nvarchar(50),
@EmpID int
AS
BEGIN
SET NOCOUNT ON
DECLARE @returnEmpID int
If exists(select * from EmpTable where EmpID= @EmpID)
BEGIN
Update EmpTable
Set EmpName=@EmpName
Where EmpID = @EmpID
select @returnEmpID= @EmpID
END
Else
BEGIN
Insert into EmpTable(EmpName)
Values (@EmpName)
select @returnEmpID= @@identity
END
SET NOCOUNT OFF
RETURN @returnEmpID
END
GO
---------------------------------------------
VB code:
<snip>
Set EmpIDPrm = oAddUpdateEmpTableCom.CreateParameter("EmpID", adInteger, adParamReturnValue)
oAddUpdateEmpTableCom.Parameters.Append EmpIDPrm
EmpIDPrm.Value = AddUpdateScenarioDProcCEID
Set EmpIDPrm = oAddUpdateEmpTableCom.CreateParameter("EmpID", adInteger, adParamInput)
oAddUpdateEmpTableCom.Parameters.Append EmpIDPrm
EmpIDPrm.Value = EmpID
Set EmpNamePrm = oAddUpdateEmpTableCom.CreateParameter("EmpName", 202, adParamInput,50)
oAddUpdateEmpTableCom.Parameters.Append EmpNamePrm
EmpNamePrm.Value = EmpName
oAddUpdateEmpTableCom.Execute , , adExecuteNoRecords
AddUpdateScenarioDProcCEID = oAddUpdateEmpTableCom.Parameters("RetVal")
</snip>
Actually, I don't think you need the row "EmpIDPrm.Value = AddUpdateScenarioDProcCEID", but my old code looks like that so it won't hurt...
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply