calling stored procedure with parameter as reference

[Guest]

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]

...

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.

 

[Bjorn Abelli]

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/default.asp?url=/library/en-us/createdb/cm_8_des_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();

 

[carion1]

I forgot to specify the size for the parameter:

parm.Size = 100;

--

Derek Davis
(e-mail address removed)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_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
(e-mail address removed)

...

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

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

"carion1" wrote

[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