returning a SPROC variable value to VB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

returning a SPROC variable value to VB

Author

Topic

JohnB92882
Starting Member

10 Posts

Posted - 2003-04-04 : 11:26:58

I have a SPROC that I pass variables to. In the SPROC I declared a variable that calcs a bunch of totals.
---------
set @mTOTAL4 = (@mTOTAL1 + @mTOTAL2 + @mTOTAL3)

RETURN @mTOTAL4
GO
-----------------
I can see my answer in the Query Analyzer, but I can't get it back into my VB application.

The following code gives me an error/access violation, yet I know the "ActiveConnection" works fine elsewhere. Can anyone spot my problem or suggest another way for me to get "@mTOTAL4".
Thanks
-----------------------

Dim cmd As Adodb.Command
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Bourne;Data Source=NEW40"
cmd.CommandText = "execute prBIDCALC_ALLITEMS @mJOB='" + mJob + " ', @mMargin= '" + mMargin + "', @mBurden='" + mBurden + "' "
cmd.CommandType = adCmdStoredProc
cmd.Execute , , Adodb.adExecuteNoRecords
txtSum3.Text = cmd.Parameters("@mTOTAL4").value

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-04 : 12:43:56

Are you OUTPUTting the variable? Just define your variable as an output parameter in the stored procedure.

For example:

CREATE PROC usp_myproc
(@var1 int OUTPUT)
AS

SET NOCOUNT ON

SELECT @var1 = COUNT(*)
FROM SomeTable
WHERE SomeColumn IS NOT NULL

RETURN

Then...
DECLARE @Somevariable int --this will need to be modified for VB
EXEC usp_myproc @somevariable OUTPUT
PRINT @Somevariable --this will need to be modified for VB

@Somevariable is now available to your application. Don't forget to include SET NOCOUNT ON though in your stored procedure. Of course my example is in pure T-SQL, so you will have to modify it for your application.

Tara

JohnB92882
Starting Member

10 Posts

Posted - 2003-04-04 : 12:59:03

Yes - my variable was "Declare(d) @mTOTAL4 int output" in my SPROC. My problem is that I do not know how to grab it (back) in my VB code.

I want "txtSum3.Text = @TOTAL4".
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-04 : 13:12:43

Well in your execute command, you are not letting it know that it is an output parameter. In the execute command, just add the work OUTPUT after the variable that is the output variable.

I should let you know that I am not a VB programmer, so I am only providing information based upon what I know about SQL Server.

Edited by - tduggan on 04/04/2003 13:13:35

JohnB92882
Starting Member

10 Posts

Posted - 2003-04-04 : 13:21:00

That's ok and Thank you.

JohnB92882
Starting Member

10 Posts

Posted - 2003-04-04 : 13:29:36

Quick question - I'm passing parameters into the SPROC but the variable I want back is declared in the sproc. If I add @mTOTAL to the execute query, won't it expect a value upon my submitting it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-04 : 13:36:49

Well you can do it in two ways:

First way from my initial example:
CREATE PROC usp_myproc
(@var1 int OUTPUT)
AS

SET NOCOUNT ON

SELECT @var1 = COUNT(*)
FROM sysobjects

RETURN

DECLARE @Somevariable int
EXEC usp_myproc @Somevariable OUTPUT
PRINT @Somevariable

DROP PROC usp_myproc

Second way:
CREATE PROC usp_myproc
AS

SET NOCOUNT ON

DECLARE @var1 int

SELECT @var1 = COUNT(*)
FROM sysobjects

RETURN @var1
go

DECLARE @count int
EXEC @count = usp_myproc
PRINT @count

DROP PROC usp_myproc

I prefer the first way. But you will get the same answer regardless of which way you pick. If you use my second example, you won't have to change much except when you call the stored procedure and also one line in your stored procedure. You will need EXEC @mTOTAL4 = prBIDCALC_ALLITEMS...(rest of string goes here). In your stored procedure, put RETURN @mTOTAL4 at the very end.

HTH

Tara

JohnB92882
Starting Member

10 Posts

Posted - 2003-04-04 : 13:42:36

Many thanks again (especially for so fast a responce) - I'll review & evaluate your suggestions for adaptation into my application.

monkeybite
Posting Yak Master

152 Posts

Posted - 2003-04-04 : 13:49:45

I noticed that in your code you're setting the CommandText to a SQL String, but declaring your CommandType to adCmdStoredProc. I don't think the SQL string is getting to your server correctly, as ADO is formatting your commandtext to be executed like a sproc. Run SQL Profiler and run your code, you'll see what's being sent to the server.

You need to do something like this instead, I don't know if I have the size and datatypes of your parameters correct, i'm guessing that they are all integers:


Dim oCmd As New ADODB.Command
Dim oCon As New ADODB.Connection

oCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Bourne;Data Source=NEW40"
oCon.Open

With oCmd
.CommandType = adCmdStoredProc
.CommandText = "[dbo].[prBIDCALC_ALLITEMS]"
.Parameters.Append .CreateParameter("@mJOB", adInteger, adParamInput, 4, mJob)
.Parameters.Append .CreateParameter("@mMargin", adInteger, adParamInput, 4, mMargin)
.Parameters.Append .CreateParameter("@mBurden", adInteger, adParamInput, 4, mBurden)
.Parameters.Append .CreateParameter("@mTOTAL4", adInteger, adParamOutput, 4)
Set .ActiveConnection = oCon
.Execute , , adExecuteNoRecords
txtSum3.Text = .Parameters("@mTOTAL4").Value
End With

oCon.Close
Set oCon = Nothing
Set oCmd = Nothing

-- monkey

Edited by - monkeybite on 04/04/2003 14:06:18

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-04 : 13:50:23

quote:

Many thanks again (especially for so fast a responce)

I just subscribe to the topic so that I get e-mailed when someone replies. It's so easy to just click on the e-mail to get to the thread and then provide more information. Of course this assumes that I am at my desk, which today it looks like I will be for most of the day.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources