Oracle's RETURNING INTO :variable equivalent using SQL Server

Question

I have the following code that works on Oracle:

OracleCommand command = conn.CreateCommand(); // conn is my connection

string sql = "INSERT INTO table_name (ID, NAME, GENDER) VALUES (1, 'JOHN', 'M') RETURNING ID INTO :returnedId";

command.CommandText = sql;
command.Parameters.Add(new OracleParameter("returnedId", OracleDbType.Decimal));
command.ExecuteNoQuery();

I need to port that to SQL Server. This is what I'm doing:

SqlCommand command = conn.CreateCommand(); // conn is my connection

string sql = "INSERT INTO table_name (Id, Name, Gender) OUTPUT Inserted.Id As returnedId VALUES (1, "JOHN", "M")";

command.CommandText = sql;
command.Parameters.Add(new SqlParameter("returnedId", SqlDbType.Decimal));
command.ExecuteNoQuery();

That code gives me the following error:

the parameterized query expects the parameter which was not supplied: @returnedId

What's the correct way to migrate the RETURNING and the :returnedId clauses from Oracle to SQL Server ?

I've tried OUTPUT Inserted.Id As @returnedId as well as changing the SqlParameter to "@returnedId" with no success.

souslov · Accepted Answer · 2015-01-06 20:44:18Z

5

OUTPUT is SQL Server's equivalent to RETURNING INTO.

INSERT INTO table_name (ID, NAME, GENDER) 
OUTPUT INSERTED.ID 
VALUES (1, 'JOHN', 'M') ;

And C# :

Int32 outputID = (Int32)command.ExecuteScalar();

edited Jan 6, 2015 at 20:44

answered May 6, 2014 at 2:31

souslov

44.7k11 gold badges92 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mendes Over a year ago

Can you please better elaborate ? I´ve added the table creation but still not working... Remember I´m using C# code to get the Id data.

Collectives™ on Stack Overflow

Oracle's RETURNING INTO :variable equivalent using SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related