Parameter object with return value

[=?iso-8859-1?Q?Norbert_P=FCrringer?=]

Hello there!

Is it possible to define a stored procedure command by adding the return
value parameter without specifying the data type? I don't really know, what
the data type of the return value is, so I prefer to get the value as
object. Is there a chance to do it?

Regards,
Norbert

 

[William \(Bill\) Vaughn]

In SQL Server the type returned by the RETURN statement is always an integer
(of some kind). In SQL Server 2005 it's a BigInt.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[=?iso-8859-1?Q?Norbert_P=FCrringer?=]

Hello once more!



Here is my code sample to create and add a parameter to the parameters
collection supporting several database models.

This function works quite good, but I don't like the way, how I create an
output parameter due to specifying a default return data type
(varchar(1000)).

For input parameters I use AddWithValue to support all input data types, but
how can I create an output parameter with a data type set by the stored
procedure itself?

Does anyone has any tips for me to improve following function?



public IDataParameter CreateParameter(IDataParameterCollection coll, string
name, object value)

{

try

{

IDataParameter par = null;

if (coll is OracleParameterCollection)

{

if (value == null)

{

par = new OracleParameter(name, OracleType.VarChar, 1000);

coll.Add(par);

}

else

par = ((OracleParameterCollection)coll).AddWithValue(name, value);

}

else if (coll is SqlParameterCollection)

{

if (value == null)

{

par = new SqlParameter(name, SqlDbType.VarChar, 1000);

coll.Add(par);

}

else

par = ((SqlParameterCollection)coll).AddWithValue("@" + name, value);

}

else if (coll is OdbcParameterCollection)

{

if (value == null)

{

par = new OdbcParameter(name, OdbcType.VarChar, 1000);

coll.Add(par);

}

else

par = ((OdbcParameterCollection)coll).AddWithValue("@" + name, value);

}

else if (coll is OleDbParameterCollection)

{

if (value == null)

{

par = new OleDbParameter(name, OleDbType.VarChar, 1000);

coll.Add(par);

}

else

par = ((OleDbParameterCollection)coll).AddWithValue("@" + name, value);

}

else

throw new DataException("Unknown connection type!");

if (value == null)

par.Direction = ParameterDirection.Output;

else

par.Direction = ParameterDirection.Input;

return par;

}

catch

{

throw ex;

}

}



Regards,

Norbert