calling stored procedure with parameter as reference

[VMI]

Can I call a stored proc from .net with a parm that changes during the SP?
For example, I call the SP with parameter sMyVariable (which changes in the
SP), and when I access it after the SP is executed, sMyVariable will have the
new value. My solution (if this doesn't exist) would've been to add the
value to the resultset (make it part of the Select statement)

Thanks.

[Bjorn Abelli]

"VMI" wrote...

Can I call a stored proc from .net with a parm that changes
during the SP? For example, I call the SP with parameter
sMyVariable (which changes in the SP), and when I access it
after the SP is executed, sMyVariable will have the
new value. My solution (if this doesn't exist) would've been
to add the value to the resultset (make it part of the Select
statement)

AFAIK that depends on what database you're using.

In ADO.NET you can have a Parameter argument with e.g.
ParameterDirection.InputOutput.

This can be used in conjunction with e.g. an "IN OUT" parameter in an Oracle
SP, but AFAIK, SQL Server doesn't have that. It *does* have the possibility
of "OUTPUT" parameters, so you could possibly use that in your SP to return
the new value, though through a second parameter.
// Bjorn A

[carion1]

You don't need a second parameter. You can declare the parameter as
InputOutput for SQL Server (maybe for others as well). I believe you can
also return a set in conjunction with said parameter if you like.

--

Derek Davis
dd******@gmail.com

"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:Oi**************@TK2MSFTNGP14.phx.gbl...

"VMI" wrote...

Can I call a stored proc from .net with a parm that changes
during the SP? For example, I call the SP with parameter
sMyVariable (which changes in the SP), and when I access it
after the SP is executed, sMyVariable will have the
new value. My solution (if this doesn't exist) would've been
to add the value to the resultset (make it part of the Select
statement)

AFAIK that depends on what database you're using.

In ADO.NET you can have a Parameter argument with e.g.
ParameterDirection.InputOutput.

This can be used in conjunction with e.g. an "IN OUT" parameter in an
Oracle SP, but AFAIK, SQL Server doesn't have that. It *does* have the
possibility of "OUTPUT" parameters, so you could possibly use that in your
SP to return the new value, though through a second parameter.
// Bjorn A

[Bjorn Abelli]

"carion1" wrote...

You don't need a second parameter. You can declare the
parameter as InputOutput for SQL Server (maybe for others
as well).

I know it works with Oracle, but is it really true for SPs in SQL Server?

As I have searched for that possibility, I would be happy if you could
provide a link to where I can read about SQL Server having InputOutput
parameters in stored procedures?

// Bjorn A

[carion1]

http://msdn.microsoft.com/library/de...es_07_3q7n.asp

Below is just a quick example:

CREATE proc dbo.usp_test

@test varchar(100) out

as

set @test = @test + ' World!'
GO

--------------------------------

Connection = new SqlConnection();
Connection.ConnectionString = YOUR_CONNECTION_STRING_GOES_HERE
Connection.Open();
Command = new SqlCommand();
Command.Connection = Connection;
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "usp_test";
SqlParameter parm = new SqlParameter("@test", "Hello");
parm.Direction = ParameterDirection.InputOutput;
Command.Parameters.Add(parm);
Command.ExecuteNonQuery();
MessageBox.Show(Command.Parameters["@test"].Value.ToString());
Command.Dispose();
Connection.Dispose();

--

Derek Davis
dd******@gmail.com

"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...

"carion1" wrote...

You don't need a second parameter. You can declare the
parameter as InputOutput for SQL Server (maybe for others
as well).

I know it works with Oracle, but is it really true for SPs in SQL Server?

As I have searched for that possibility, I would be happy if you could
provide a link to where I can read about SQL Server having InputOutput
parameters in stored procedures?

// Bjorn A

[carion1]

I forgot to specify the size for the parameter:

parm.Size = 100;

--

Derek Davis
dd******@gmail.com

"carion1" <dd******@gmail.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

http://msdn.microsoft.com/library/de...es_07_3q7n.asp

Below is just a quick example:

CREATE proc dbo.usp_test

@test varchar(100) out

as

set @test = @test + ' World!'
GO

--------------------------------

Connection = new SqlConnection();
Connection.ConnectionString = YOUR_CONNECTION_STRING_GOES_HERE
Connection.Open();
Command = new SqlCommand();
Command.Connection = Connection;
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "usp_test";
SqlParameter parm = new SqlParameter("@test", "Hello");
parm.Direction = ParameterDirection.InputOutput;
Command.Parameters.Add(parm);
Command.ExecuteNonQuery();
MessageBox.Show(Command.Parameters["@test"].Value.ToString());
Command.Dispose();
Connection.Dispose();

--

Derek Davis
dd******@gmail.com

"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...

"carion1" wrote...

You don't need a second parameter. You can declare the
parameter as InputOutput for SQL Server (maybe for others
as well).

I know it works with Oracle, but is it really true for SPs in SQL Server?

As I have searched for that possibility, I would be happy if you could
provide a link to where I can read about SQL Server having InputOutput
parameters in stored procedures?

// Bjorn A

[Bjorn Abelli]

"carion1" wrote

"Bjorn Abelli" wrote

As I have searched for that possibility, I would be happy
if you could provide a link to where I can read about SQL
Server having InputOutput parameters in stored procedures?

[snipped code sample]

Thank you!

How I have looked, I haven't seen any documentation on this functionality of
the "output" parameter in T-SQL. It seems that I have been deceived by the
use of "output" as the keyword for what really is an InputOutput parameter!

You never stop to learn... ;-)
// Bjorn A

[carion1]

Glad I could help.

--

Derek Davis
dd******@gmail.com

"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

"carion1" wrote

"Bjorn Abelli" wrote As I have searched for that possibility, I would be happy
if you could provide a link to where I can read about SQL
Server having InputOutput parameters in stored procedures?

[snipped code sample]

Thank you!

How I have looked, I haven't seen any documentation on this functionality
of the "output" parameter in T-SQL. It seems that I have been deceived by
the use of "output" as the keyword for what really is an InputOutput
parameter!

You never stop to learn... ;-)
// Bjorn A