468,533 Members | 1,892 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,533 developers. It's quick & easy.

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("empid"))

Dim curDeptID As Integer = CInt(Session("deptid"))

Dim MyConn As New
SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings("dbConn
"))

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

Dim Cmda As New SqlCommand(MySQLe, MyConn)

Cmda.CommandType = CommandType.StoredProcedure

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("@projectcnt",
SqlDbType.Int)

projectcnt.Direction = ParameterDirection.Output

Dim curProCount As Integer

Try

If MyConn.State <> True Then

MyConn.Open()

End If

objDR = Cmda.ExecuteReader(System.Data.CommandBehavior.Clo seConnection)

While objDR.Read

curProCount = CInt(Cmda.Parameters("@projectcnt").Value)

End While

objDR.Close()

Catch e As SqlException

errpanel.Visible = True

warningpanel.Visible = False

mainpanel.Visible = 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 1626
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******@eastlink.ca> wrote in message
news:%2****************@TK2MSFTNGP10.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("empid"))

Dim curDeptID As Integer = CInt(Session("deptid"))

Dim MyConn As New
SqlConnection(System.Configuration.ConfigurationSe ttings.AppSettings("dbConn "))

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

Dim Cmda As New SqlCommand(MySQLe, MyConn)

Cmda.CommandType = CommandType.StoredProcedure

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("@projectcnt",
SqlDbType.Int)

projectcnt.Direction = ParameterDirection.Output

Dim curProCount As Integer

Try

If MyConn.State <> True Then

MyConn.Open()

End If

objDR = Cmda.ExecuteReader(System.Data.CommandBehavior.Clo seConnection)

While objDR.Read

curProCount = CInt(Cmda.Parameters("@projectcnt").Value)

End While

objDR.Close()

Catch e As SqlException

errpanel.Visible = True

warningpanel.Visible = False

mainpanel.Visible = 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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by sbest | last post: by
6 posts views Thread by David Lozzi | last post: by
4 posts views Thread by scparker | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.