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_count ry 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_count ry
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.Ope n();

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandT ext = "GetJurisNa me";
sqlCmd.CommandT ype = CommandType.Sto redProcedure;
sqlCmd.Connecti on = _Connection;

//Handle the parameters
SqlParameter[] arParameters = new SqlParameter[4];
arParameters[ 0 ] = sqlCmd.Paramete rs.Add("@in_jur is_id", SqlDbType.Int);
arParameters[ 0 ].Value = nJurisID;
arParameters[ 0 ].Direction = ParameterDirect ion.Input;
arParameters[ 1 ] = sqlCmd.Paramete rs.Add("@in_jur is_type", SqlDbType.Int);
arParameters[ 1 ].Value = nJurisType;
arParameters[ 1 ].Direction = ParameterDirect ion.Input;
arParameters[ 2 ] = sqlCmd.Paramete rs.Add("@in_jur is_country",
SqlDbType.Int);
arParameters[ 2 ].Value = nJurisCountry;
arParameters[ 2 ].Direction = ParameterDirect ion.Input;
arParameters[ 3 ] = sqlCmd.Paramete rs.Add("@out_ju ris_name",
SqlDbType.VarCh ar);
arParameters[ 3 ].Value = string.Empty;
arParameters[ 3 ].Direction = ParameterDirect ion.Output;

sqlCmd.ExecuteN onQuery();
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.Paramete rs.Add("@out_ju ris_name",
SqlDbType.VarCh ar, 40);

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

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_count ry 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_count ry
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.Ope n();

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandT ext = "GetJurisNa me";
sqlCmd.CommandT ype = CommandType.Sto redProcedure;
sqlCmd.Connecti on = _Connection;

//Handle the parameters
SqlParameter[] arParameters = new SqlParameter[4];
arParameters[ 0 ] = sqlCmd.Paramete rs.Add("@in_jur is_id", SqlDbType.Int);
arParameters[ 0 ].Value = nJurisID;
arParameters[ 0 ].Direction = ParameterDirect ion.Input;
arParameters[ 1 ] = sqlCmd.Paramete rs.Add("@in_jur is_type",
SqlDbType.Int);
arParameters[ 1 ].Value = nJurisType;
arParameters[ 1 ].Direction = ParameterDirect ion.Input;
arParameters[ 2 ] = sqlCmd.Paramete rs.Add("@in_jur is_country",
SqlDbType.Int);
arParameters[ 2 ].Value = nJurisCountry;
arParameters[ 2 ].Direction = ParameterDirect ion.Input;
arParameters[ 3 ] = sqlCmd.Paramete rs.Add("@out_ju ris_name",
SqlDbType.VarCh ar);
arParameters[ 3 ].Value = string.Empty;
arParameters[ 3 ].Direction = ParameterDirect ion.Output;

sqlCmd.ExecuteN onQuery();
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_count ry 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_count ry
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.Ope n();

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandT ext = "GetJurisNa me";
sqlCmd.CommandT ype = CommandType.Sto redProcedure;
sqlCmd.Connecti on = _Connection;

//Handle the parameters
SqlParameter[] arParameters = new SqlParameter[4];
arParameters[ 0 ] = sqlCmd.Paramete rs.Add("@in_jur is_id", SqlDbType.Int);
arParameters[ 0 ].Value = nJurisID;
arParameters[ 0 ].Direction = ParameterDirect ion.Input;
arParameters[ 1 ] = sqlCmd.Paramete rs.Add("@in_jur is_type", SqlDbType.Int);
arParameters[ 1 ].Value = nJurisType;
arParameters[ 1 ].Direction = ParameterDirect ion.Input;
arParameters[ 2 ] = sqlCmd.Paramete rs.Add("@in_jur is_country",
SqlDbType.Int);
arParameters[ 2 ].Value = nJurisCountry;
arParameters[ 2 ].Direction = ParameterDirect ion.Input;
arParameters[ 3 ] = sqlCmd.Paramete rs.Add("@out_ju ris_name",
SqlDbType.VarCh ar);
arParameters[ 3 ].Value = string.Empty;
arParameters[ 3 ].Direction = ParameterDirect ion.Output;

sqlCmd.ExecuteN onQuery();
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.Paramete rs.Add("@out_ju ris_name",
SqlDbType.VarCh ar, 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