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

B

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
 
N

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]
- (e-mail address removed)


Brian Parker said:
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
Click to expand...
 
G

Guest

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 said:
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
Click to expand...
 
B

Brian Parker

Nicholas said:
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);
Click to expand...

Thanks, Nick. That worked.

-BEP
 
B

Brian Parker

Peter said:
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.
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error: Failed to convert parameter value from a String to a Byte[]. (Need Help Urgently) 0
using the sort and rowFilter from a stored procedure dataset 1
Invalid cast from System.Int32 to System.Byte[]. 1
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may 4

Top