Need to return a string ( varchar(40) ) from MS-SQLSERVER storedprocedure

[Brian Parker]

I've been banging my head on this for hours and it's something that
should be very very easy. I have a Stored Procedure in a MS-SQL 2000
server that returns a Varchar(40) and I want to grab that value in
C-Sharp the easiest way possible. I've tried multiple ways, using
ExecuteNonQuery, ExecuteScalar, etc. and I just can't get the value.

I've tested the SP in SQL Manager and it returns the value just fine.

This is the SP:

--------------------------------------------------------
CREATE PROCEDURE [dbo].[GetJurisName] (
@in_juris_id INT, -- ID of the Jurisdiction
@in_juris_type INT, -- Type of the Jurisdiction
@in_juris_country INT, -- ID of the Country
@out_juris_name VARCHAR(40) OUTPUT -- Returned name of the Jurisdiction
)
AS
BEGIN
SET @out_juris_name = 'UNKNOWN'

SELECT @out_juris_name = juris_name
FROM dl_jurisdiction
WHERE juris_id = @in_juris_id AND juris_type = @in_juris_type AND
juris_country = @in_juris_country
END
--------------------------------------------------------

This is the latest way I've been trying to get the value, and it's
keeping the empty string in the 4th parameter:

--------------------------------------------------------
_Connection.Open();

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "GetJurisName";
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Connection = _Connection;

//Handle the parameters
SqlParameter[] arParameters = new SqlParameter[4];
arParameters[ 0 ] = sqlCmd.Parameters.Add("@in_juris_id", SqlDbType.Int);
arParameters[ 0 ].Value = nJurisID;
arParameters[ 0 ].Direction = ParameterDirection.Input;
arParameters[ 1 ] = sqlCmd.Parameters.Add("@in_juris_type", SqlDbType.Int);
arParameters[ 1 ].Value = nJurisType;
arParameters[ 1 ].Direction = ParameterDirection.Input;
arParameters[ 2 ] = sqlCmd.Parameters.Add("@in_juris_country",
SqlDbType.Int);
arParameters[ 2 ].Value = nJurisCountry;
arParameters[ 2 ].Direction = ParameterDirection.Input;
arParameters[ 3 ] = sqlCmd.Parameters.Add("@out_juris_name",
SqlDbType.VarChar);
arParameters[ 3 ].Value = string.Empty;
arParameters[ 3 ].Direction = ParameterDirection.Output;

sqlCmd.ExecuteNonQuery();
string strJurisName = arParameters[ 3 ].Value.ToString();
--------------------------------------------------------

I'm not married to the above code, I just want the thing to work. Any
help is appreciated.

TIA,
-BEP

[Nicholas Paldino [.NET/C# MVP]]

Brian,

It doesn't appear that you are setting the size of the output parameter.
When adding the last parameter, your code should read:

arParameters[ 3 ] = sqlCmd.Parameters.Add("@out_juris_name",
SqlDbType.VarChar, 40);

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Brian Parker" <be******@yahoo.comwrote in message
news:RRgrg.3896$nK.1171@dukeread05...

I've been banging my head on this for hours and it's something that should
be very very easy. I have a Stored Procedure in a MS-SQL 2000 server that
returns a Varchar(40) and I want to grab that value in C-Sharp the easiest
way possible. I've tried multiple ways, using ExecuteNonQuery,
ExecuteScalar, etc. and I just can't get the value.

I've tested the SP in SQL Manager and it returns the value just fine.

This is the SP:

--------------------------------------------------------
CREATE PROCEDURE [dbo].[GetJurisName] (
@in_juris_id INT, -- ID of the Jurisdiction
@in_juris_type INT, -- Type of the Jurisdiction
@in_juris_country INT, -- ID of the Country
@out_juris_name VARCHAR(40) OUTPUT -- Returned name of the Jurisdiction
)
AS
BEGIN
SET @out_juris_name = 'UNKNOWN'

SELECT @out_juris_name = juris_name
FROM dl_jurisdiction
WHERE juris_id = @in_juris_id AND juris_type = @in_juris_type AND
juris_country = @in_juris_country
END
--------------------------------------------------------

This is the latest way I've been trying to get the value, and it's keeping
the empty string in the 4th parameter:

--------------------------------------------------------
_Connection.Open();

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "GetJurisName";
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Connection = _Connection;

//Handle the parameters
SqlParameter[] arParameters = new SqlParameter[4];
arParameters[ 0 ] = sqlCmd.Parameters.Add("@in_juris_id", SqlDbType.Int);
arParameters[ 0 ].Value = nJurisID;
arParameters[ 0 ].Direction = ParameterDirection.Input;
arParameters[ 1 ] = sqlCmd.Parameters.Add("@in_juris_type",
SqlDbType.Int);
arParameters[ 1 ].Value = nJurisType;
arParameters[ 1 ].Direction = ParameterDirection.Input;
arParameters[ 2 ] = sqlCmd.Parameters.Add("@in_juris_country",
SqlDbType.Int);
arParameters[ 2 ].Value = nJurisCountry;
arParameters[ 2 ].Direction = ParameterDirection.Input;
arParameters[ 3 ] = sqlCmd.Parameters.Add("@out_juris_name",
SqlDbType.VarChar);
arParameters[ 3 ].Value = string.Empty;
arParameters[ 3 ].Direction = ParameterDirection.Output;

sqlCmd.ExecuteNonQuery();
string strJurisName = arParameters[ 3 ].Value.ToString();
--------------------------------------------------------

I'm not married to the above code, I just want the thing to work. Any
help is appreciated.

TIA,
-BEP

[Peter Bromberg [C# MVP]]

Brian,
I think the reason why this may be confusing is if I remember correctly, you
must close the connection before the values of output parameters are
available. Personally, I rarely do this, if I only have one value I simply
select it out and use executeScalar which returns an object (which in your
case you would simply cast to a string.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Brian Parker" wrote:

I've been banging my head on this for hours and it's something that
should be very very easy. I have a Stored Procedure in a MS-SQL 2000
server that returns a Varchar(40) and I want to grab that value in
C-Sharp the easiest way possible. I've tried multiple ways, using
ExecuteNonQuery, ExecuteScalar, etc. and I just can't get the value.

I've tested the SP in SQL Manager and it returns the value just fine.

This is the SP:

--------------------------------------------------------
CREATE PROCEDURE [dbo].[GetJurisName] (
@in_juris_id INT, -- ID of the Jurisdiction
@in_juris_type INT, -- Type of the Jurisdiction
@in_juris_country INT, -- ID of the Country
@out_juris_name VARCHAR(40) OUTPUT -- Returned name of the Jurisdiction
)
AS
BEGIN
SET @out_juris_name = 'UNKNOWN'

SELECT @out_juris_name = juris_name
FROM dl_jurisdiction
WHERE juris_id = @in_juris_id AND juris_type = @in_juris_type AND
juris_country = @in_juris_country
END
--------------------------------------------------------

This is the latest way I've been trying to get the value, and it's
keeping the empty string in the 4th parameter:

--------------------------------------------------------
_Connection.Open();

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "GetJurisName";
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Connection = _Connection;

//Handle the parameters
SqlParameter[] arParameters = new SqlParameter[4];
arParameters[ 0 ] = sqlCmd.Parameters.Add("@in_juris_id", SqlDbType.Int);
arParameters[ 0 ].Value = nJurisID;
arParameters[ 0 ].Direction = ParameterDirection.Input;
arParameters[ 1 ] = sqlCmd.Parameters.Add("@in_juris_type", SqlDbType.Int);
arParameters[ 1 ].Value = nJurisType;
arParameters[ 1 ].Direction = ParameterDirection.Input;
arParameters[ 2 ] = sqlCmd.Parameters.Add("@in_juris_country",
SqlDbType.Int);
arParameters[ 2 ].Value = nJurisCountry;
arParameters[ 2 ].Direction = ParameterDirection.Input;
arParameters[ 3 ] = sqlCmd.Parameters.Add("@out_juris_name",
SqlDbType.VarChar);
arParameters[ 3 ].Value = string.Empty;
arParameters[ 3 ].Direction = ParameterDirection.Output;

sqlCmd.ExecuteNonQuery();
string strJurisName = arParameters[ 3 ].Value.ToString();
--------------------------------------------------------

I'm not married to the above code, I just want the thing to work. Any
help is appreciated.

TIA,
-BEP

[Brian Parker]

Nicholas Paldino [.NET/C# MVP] wrote:

Brian,

It doesn't appear that you are setting the size of the output parameter.
When adding the last parameter, your code should read:

arParameters[ 3 ] = sqlCmd.Parameters.Add("@out_juris_name",
SqlDbType.VarChar, 40);

Thanks, Nick. That worked.

-BEP

[Brian Parker]

Peter Bromberg [C# MVP] wrote:

Brian,
I think the reason why this may be confusing is if I remember correctly, you
must close the connection before the values of output parameters are
available. Personally, I rarely do this, if I only have one value I simply
select it out and use executeScalar which returns an object (which in your
case you would simply cast to a string.

Nick set me straight with the size of the Varchar being sent in and that
fixed it. But, I prefer to use ExecuteScalar() if I can, so I'm going
to try to convert it back to that and send in the size.

Thanks!
-BEP