Cannot retrieve value of output parameter from Stored Procedure

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

SQL Server Development (2000)

Cannot retrieve value of output parameter from Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-20 : 08:06:19

gerry@pairofdocs.net writes "Any suggestions you cna provide will be greatly appreciated.

Here is a snippet of my ASP.NET application:

Dim UserID As Guid
Dim cmdAddUser As New SqlCommand("sp_AddUser", myCon)
cmdAddUser.CommandType = CommandType.StoredProcedure

cmdAddUser.Parameters.Add(("@Add"), bAddUser) ' boolean - 1 add, 0 - update
cmdAddUser.Parameters.Add(("@UserName"), strUserName)
[add a whole bunch of input parameters]
cmdAddUser.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16)
cmdAddUser.Parameters("@UserID").Direction = ParameterDirection.Output

myCon.Open()
cmdAddUser.ExecuteNonQuery()
UserID = CType(cmdAddUser.Parameters("@UserID").Value, Guid)

Here is a snippet from the stored procedure:

ALTER PROCEDURE sp_AddUser
@Add bit,
@UserName varchar(50),
[bunch of parameters]
@UserID uniqueidentifier = NULL output
AS
if @Add = 1
INSERT myTable (<Columns>)
VALUES (<values>)

else
UPDATE myTable
SET <columns>=<values>
WHERE UserName = @UserName

Select @UserID = UserID
FROM myTable
WHERE Username = @UserName
RETURN

Note that in the table definition, the default value for UserID is newID(). UserID does not appear in either the INSERT or UPDATE statements. Also note that UserName is guaranteed to be unique.

Using the Stored Procedure debugger in VS.NET, I have confirmed that the variable @UserID takes on a non-NULL value for @Add=0 and @Add=1. I have also confirmed that cmdAddUser.Parameters("@UserID") is being properly initialized. I have also confirmed that when the ExecuteNonQuery call is made, myTable is updated properly.

HOWEVER, I cannot get the value for UserID! When I put a breakpoint immediately after the ExecuteNonQuery and look at cmdAddUser.Parameters("@UserID").Value, is shows NULL. (Needless to say, the following cast fails...) I am at a complete loss - please help!"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-02-20 : 09:32:16

try getting a v.simple procedure to work using plain variables....ie char.

Once you get the structure of the call right to receive OUTPUT variables (SP->ASP), then switch up to using the uniqueidentifier....you may be experiencing a compatability issue between variables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources