a stored procedures question or two.

My main problem is retrieving an output value. My stored procedure is:

.......................

USE [CyclingClub]
GO
/****** Object: StoredProcedure [dbo].[ValidateMemberUsrPwd] Script Date: 05/20/2007 14:46:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Poldie
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[ValidateMemberUsrPwd]
-- Add the parameters for the stored procedure here
@.username nvarchar(16) = NULL,
@.password nvarchar(16) = NULL,
@.memberid int output

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT @.memberid = member_id from members
where @.username = member_username
and @.password= member_password

END
.......................

and when I run it from the management studio app I get the results I expect. When I run it from within Visual Studio 2005 Pro Server Explorer, if I assign a valid value for @.username and @.password but leave @.memberid as <DEFAULT> in the Run Stored Procedure box I get the following output:

.......................

Running [dbo].[ValidateMemberUsrPwd] ( @.username = poldie, @.password = plop, @.memberid = <DEFAULT> ).

Procedure or function 'ValidateMemberUsrPwd' expects parameter '@.memberid', which was not supplied.
No rows affected.
(0 row(s) returned)
@.memberid =
@.RETURN_VALUE =
Finished running [dbo].[ValidateMemberUsrPwd].

.......................

Which is a little odd, as I wouldn't have thought the value of an output parameter would have mattered very much. But I can live with that, and if I try again and give a dummy value of 666 I get the following:

.......................

Running [dbo].[ValidateMemberUsrPwd] ( @.username = poldie, @.password = plop, @.memberid = 666 ).

No rows affected.
(0 row(s) returned)
@.memberid = 1
@.RETURN_VALUE = 0
Finished running [dbo].[ValidateMemberUsrPwd].

.......................

Which is better, as 1 is the correct value. But when I try and retrieve the output value in code I only get what I've assigned as the dummy value. My code is:

.......................
Dim cn As New SqlConnection("server=(local);Trusted_Connection=yes;initial catalog=CyclingClub")
Dim cmd As New SqlCommand("ValidateMemberUsrPwd", cn)
cmd.CommandType = Data.CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@.username", Data.SqlDbType.NVarChar, 16, Data.ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.password", Data.SqlDbType.NVarChar, 16, Data.ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.Output))

cmd.Parameters("@.memberid").Value = 666
cmd.Parameters("@.username").Value = sUsername
cmd.Parameters("@.password").Value = sPassword

cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
.......................

In the Immediate window:

?cmd.Parameters("@.memberid").Value
666 {Integer}
Integer: 666 {Integer}

Any ideas what I'm doing wrong? Is it the whole way in which I'm trying to retrieve data? I know there are all sorts of datagrids and sets and readers etc but I'd like to do it this way initially. I tried using the return value initially and couldn't get that working - could that be for the same reason this isn't working?

Thanks in advance for even reading this far!

Hello my friend,

Working with output parameters is tedious. It would be better if you do not use the output parameter. Do not pass in @.MemberID. Declare it in the procedure, set it and then return it from the procedure like so: -

DECLARE @.MemberID AS INT

SET @.MemberID = (SELECT ...)

SELECT @.MemberID

Then instead of using ExecuteNonQuery(), use Object myID = ExecuteScalar() to return one value; which will be the @.MemberID. Then cast it to an integer if it is not null if you need to.

Kind regards

Scotty

|||

Hi poldie,

I think your code is perfectly fine just check these things

1. When you are retrieving the output value? It should be done after cmd.ExecuteNonQuery

Like

cn.open()

cmd.ExecuteNonQuery()

cn.close()

Dim str as string

str=cmd.parameters("@.memberid").value

This should work..

Satya

|||

Thanks. That works, although I chose output type parameters because I'll later need to return a number of fields! I guess this is as good a time as any to learn a little more about this sort of thing!

|||

Thats good... mark the reply as answered if this helped ...

Satya

|||

satya_tanwar:

Thats good... mark the reply as answered if this helped ...

Does the bestest answerer get sweeties?

|||

No,

But its always good time to help anyone and save some time...

Satya

|||

satya_tanwar:

1. When you are retrieving the output value? It should be done after cmd.ExecuteNonQuery

Like

cn.open()

cmd.ExecuteNonQuery()

cn.close()

Dim str as string

str=cmd.parameters("@.memberid").value

I was doing it after ExecuteNonQuery but before I closed the connection.

|||

First, all resultsets must be fully returned and closed before output parameters are available. So, there are specifics that must happen.

Using cn As SqlConnection = New SqlConnection("server=(local);Trusted_Connection=yes;initial catalog=CyclingClub")
Using cmd As SqlCommand = New SqlCommand("dbo.ValidateMemberUsrPwd", cn)
cmd.CommandType = CommandType.StoredProcedure

Dim pUsername As New SqlParameter("@.username", SqlDbType.NVarChar, 16)
Dim pPassword As New SqlParameter("@.password", SqlDbType.NVarChar, 16)
Dim pMemberId As New SqlParameter("@.memberid", SqlDbType.Int)

pUsername.Value = sUsername
pPassword.Value = sPassword
pMemberid.Value = 666
pMemberId.ParameterDirection = ParameterDirection.Output

cmd.Parameters.Add( pUsername )
cmd.Parameters.Add( pPassword )
cmd.Parameters.Add( pMemberId )

cn.Open()
cmd.ExecuteNonQuery()

Response.Write( "MemberId: " + pMemberId.Value.ToString() )

'''
''' Output Parameters available now
'''
End Using
End Using

|||

davidpenton:

First, all resultsets must be fully returned and closed before output parameters are available. So, there are specifics that must happen.

Yes, it's not that though. I just tried your code - that works too. It's as if your parameters (pMemberId) are getting updated by the stored procedure, whereas I'm see the original, unchanged parameters that went into the stored procedure. Is it anything like strings, where if you change a string the old memory gets removed after being copied to the memory used by what will become the new string (which is why you should use Append and not just + to build strings)? Perhaps I'm looking at memory which has been marked for removal by the garbage collector later but which hasn't occurred yet?

Anyway, thanks - that's fixed it!