473,881 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

returning output vars from nested stored procedure

Hi all,

I have an app that currently runs through 3 seperate stored procedures each
returning a count of records. What I would like to do is combine these
calls into one call, however I am having an issue getting the output
parameters' values after execution. Here is a snipit of code that calls one
for simplicity's sake: I think I am close, i was under the impression I
could use the datareader to get at it, but is is always returning 0 :)

Cheers & thanks a bunch, Joe :)
'SQL

'TOP LEVEL CALLING SP
CREATE PROCEDURE [dbo].[desktopquery]

@empid Int,
@deptid Int

AS

EXEC empprojectcnt @empid Int, @projectcnt Int Output
GO

'PROJECTS
CREATE PROCEDURE [dbo].[empprojectcnt]

@empid Int,
@projectcnt Int Output

AS

Select @projectcnt = COUNT(projectid ) FROM PROJECT WHERE empid = @empid
Return @projectcnt
GO

'ASP NET

Private Sub GetEmpStats()

Dim curEmpID As Integer = CInt(Session("e mpid"))

Dim curDeptID As Integer = CInt(Session("d eptid"))

Dim MyConn As New
SqlConnection(S ystem.Configura tion.Configurat ionSettings.App Settings("dbCon n
"))

Dim MySQLe As String = "[dbo].[desktopquery]"

Dim Cmda As New SqlCommand(MySQ Le, MyConn)

Cmda.CommandTyp e = CommandType.Sto redProcedure

Dim objDR As SqlDataReader

'INPUT parameters

Cmda.Parameters .Add(New SqlParameter("@ deptid", curDeptID))

Cmda.Parameters .Add(New SqlParameter("@ empid", curEmpID))

'output parameters

Dim projectcnt As SqlParameter = Cmda.Parameters .Add("@projectc nt",
SqlDbType.Int)

projectcnt.Dire ction = ParameterDirect ion.Output

Dim curProCount As Integer

Try

If MyConn.State <> True Then

MyConn.Open()

End If

objDR = Cmda.ExecuteRea der(System.Data .CommandBehavio r.CloseConnecti on)

While objDR.Read

curProCount = CInt(Cmda.Param eters("@project cnt").Value)

End While

objDR.Close()

Catch e As SqlException

errpanel.Visibl e = True

warningpanel.Vi sible = False

mainpanel.Visib le = False

errmessage.Text = e.Message

Finally

If MyConn.State = ConnectionState .Open Then

MyConn.Close()

End If

End Try

End Sub



Feb 1 '06 #1
1 1815
Hi All,

Nevermind :) I figured it out right after I posted this :P hehe

The problem was that I was trying to access the output parameters BEFORE
closing out the datareader DUH!
So i simply removed the while loop, moved the close() method up and then
grabbed the parameter value :)
Thx for being there anyways:)
"Joe Van Meer" <jv******@eastl ink.ca> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..
Hi all,

I have an app that currently runs through 3 seperate stored procedures each returning a count of records. What I would like to do is combine these
calls into one call, however I am having an issue getting the output
parameters' values after execution. Here is a snipit of code that calls one for simplicity's sake: I think I am close, i was under the impression I
could use the datareader to get at it, but is is always returning 0 :)

Cheers & thanks a bunch, Joe :)
'SQL

'TOP LEVEL CALLING SP
CREATE PROCEDURE [dbo].[desktopquery]

@empid Int,
@deptid Int

AS

EXEC empprojectcnt @empid Int, @projectcnt Int Output
GO

'PROJECTS
CREATE PROCEDURE [dbo].[empprojectcnt]

@empid Int,
@projectcnt Int Output

AS

Select @projectcnt = COUNT(projectid ) FROM PROJECT WHERE empid = @empid
Return @projectcnt
GO

'ASP NET

Private Sub GetEmpStats()

Dim curEmpID As Integer = CInt(Session("e mpid"))

Dim curDeptID As Integer = CInt(Session("d eptid"))

Dim MyConn As New
SqlConnection(S ystem.Configura tion.Configurat ionSettings.App Settings("dbCon n "))

Dim MySQLe As String = "[dbo].[desktopquery]"

Dim Cmda As New SqlCommand(MySQ Le, MyConn)

Cmda.CommandTyp e = CommandType.Sto redProcedure

Dim objDR As SqlDataReader

'INPUT parameters

Cmda.Parameters .Add(New SqlParameter("@ deptid", curDeptID))

Cmda.Parameters .Add(New SqlParameter("@ empid", curEmpID))

'output parameters

Dim projectcnt As SqlParameter = Cmda.Parameters .Add("@projectc nt",
SqlDbType.Int)

projectcnt.Dire ction = ParameterDirect ion.Output

Dim curProCount As Integer

Try

If MyConn.State <> True Then

MyConn.Open()

End If

objDR = Cmda.ExecuteRea der(System.Data .CommandBehavio r.CloseConnecti on)

While objDR.Read

curProCount = CInt(Cmda.Param eters("@project cnt").Value)

End While

objDR.Close()

Catch e As SqlException

errpanel.Visibl e = True

warningpanel.Vi sible = False

mainpanel.Visib le = False

errmessage.Text = e.Message

Finally

If MyConn.State = ConnectionState .Open Then

MyConn.Close()

End If

End Try

End Sub



Feb 1 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
42199
by: Samuel Hon | last post by:
Hi I'm not sure what the best approach for this is: I have a stored procedure which I would like to use to return several output values instead of returning a recordset. CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 int OUTPUT) AS SELECT field2, field3 FROM Table WHERE field1 = @param1
3
16952
by: WGW | last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost! everything I need. Maybe not efficiently, but usefully. However, I have a problem -- a complex query problem... I can create a parameter query in a stored procedure, but how do I use the result set of a parameter query in a select query (in the same or another sp)? In short, if a select query contains a result table that is generated as a parameter query, how do I...
0
1355
by: sbest | last post by:
Hi all, I am trying to execute a MS-SQL 2000 stored proedure from PHP 4.2.2 and return the single row of 3 output values. I've whittled down the error messages so that I'm now mainly getting an error message that the MS-SQL Query ($sql) failed. Here's the stored procedure statements that define the input and output variables: CREATE PROCEDURE dbo.sl_mostrecent_State
0
2442
by: Sahadev K | last post by:
Here is the question 1. I have two stored procedures P1 and P2. 2. I want to call stored procedure P2 from P1. 3. Stored Procedure P2 returns a result set(as an open cursor). 4. In P1, I want to retrieve the result set returned by P2 into a cursor, iterate through the cursor and insert records into a temporary table created in P1. 5. Here are the steps I followed inside the stored proced P1: /* Declare the variable */
6
2271
by: David Lozzi | last post by:
Here is the proc: CREATE PROCEDURE . @CID as int, @Netname as nvarchar(25), @Return as int OUTPUT AS IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
5
16568
by: Wael | last post by:
Hi, I have the following stored procedure that does some processing and puts the result in a temporary table. I tried several things that procedure to display output that I can access with ADO.Net, but it doesn't work. It doesn't even display the result in the query analyzer unless I add SELECT @ReturnFullName Any help? The stored procedure:
4
4367
by: scparker | last post by:
Hello, We have a stored procedure that does a basic insert of values. I am then able to retrieve the ID number created for this new record. We are currently using ASP.NET 2.0 and use N-Tier Architecture. The Stored Procedures are used through TableAdaptors, which in turn are used by Class Files. I wish to be able to return this new ID value using the Stored
3
4396
by: codefragment | last post by:
Hi I have a chunky bit of sql that I will want to call from a number of places. It will return a few thousand rows. Whats the best way of structuring this? 1) I initially thought of using nested stored procedures and returning the result in a temporary table. However the scope of the temporary table seems to be limited to the stored procedure its created in so unless I create the temporary table in every stored
10
25169
by: pinman | last post by:
hi i am trying to implemement forms authentication for my website but can't seem to get the stored procedure to output the correct value when checking a users credentials. the code is ALTER PROCEDURE validlogin @uname nchar(30), @pass nchar(100), @valid int OUTPUT AS IF EXISTS(SELECT * FROM Users WHERE User_name = @uname AND Password = @pass)
0
9926
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11095
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10812
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10399
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7952
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7108
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5780
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5976
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4194
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.