SQLServerCentral / Discuss Content Posted by Dinesh Asanka / Article Discussions / Article Discussions by Author / The OUPUT Command / Latest Posts

RE: The OUPUT Command

harij73 — Thu, 30 Aug 2012 02:45:55 GMT

This is a very simple article which lists the functionality of the output command. However, it would be an excellent article if the following were also included:a) what happens to the output values given by the output command and where it would be storedb) limitationsc) performance gain when comparing to triggersOtherwise a very valuable article, for the beginners to read with.Thanks

RE: The OUPUT Command

FVC — Fri, 13 Jun 2008 09:04:51 GMT

also be aware that using the output command to log/move the deleted data in a log or archive table, that this might affect performance (depending on how much records you process).Instead of 2 times the related index scans when performing first an insert into the log or archive table and afterwards the delete, the output clause has only 1 time the index scan, but performs a table spool that can cause even more IO.

RE: The OUPUT Command

Tom Garth — Fri, 06 Jun 2008 11:32:03 GMT

[quote][b]vijay_download (11/1/2007)[/b][hr]Hi,I m NOT using storedprocedure. I m using the ADO command object which will stored the compiled command text. I m passing only parameter value in UPDATE command text. And it doesn't return value or parameter.How can i do this.Pls see my command syntex below.Set rsPending = New ADODB.CommandSet rsPending.ActiveConnection = cnnDefaultrsPending.CommandType = adCmdTextrsPending.CommandText = "UPDATE vTableServerAllocationView _& " SET EG0_User='varUserid' OUTPUT INSERTED.Sr_No,INSERTED.Doc_No WHERE Sr_No =?"rsPending.Parameters.Append rsPending.CreateParameter("SN", adDouble, adParamInput)rsPending.Prepared = True[/quote]Vijay,It doen't appear that you are writing in .NET. Too bad. If I'm wrong then...I'm sure I'll get a lot of flack for suggesting this, but it works, and under the proper circumstances, there is nothing at all wrong with it. Forget the parameter. If your code returns a record in your query window, then retrieve that record with a DataReader. Just replace the code after setting the CommandText property, with the following. Dim dr As SqlDataReader dr = rsPending.ExecuteReader If dr.HasRows = True Then dr.Read() 'Collect the field data from dr('FieldName' [or ordinal]).ToString for each column. End IfBy the way... GOOD article. GOOD discussion.

RE: The OUPUT Command

Matt Miller (#4) — Fri, 06 Jun 2008 09:49:10 GMT

Vijay - I think that if you were to try to execute that as a recordset - you would get data back, just as if you had a "straight" SELECT statement in there. Hard to tell what's going on now, though - the code you posted don't actually execute the ADO command. In other words - in "traditional" ADO - you'd instantiate a recordset object and execute the command into it, thus giving you access to the rows being OUTPUT.

RE: The OUPUT Command

Anipaul — Fri, 06 Jun 2008 04:33:58 GMT

Nice description....... very useful one:)

RE: The OUPUT Command

vijay_download — Thu, 01 Nov 2007 02:00:46 GMT

Hi,I m NOT using storedprocedure. I m using the ADO command object which will stored the compiled command text. I m passing only parameter value in UPDATE command text. And it doesn't return value or parameter.How can i do this.Pls see my command syntex below.Set rsPending = New ADODB.CommandSet rsPending.ActiveConnection = cnnDefaultrsPending.CommandType = adCmdTextrsPending.CommandText = "UPDATE vTableServerAllocationView _& " SET EG0_User='varUserid' OUTPUT INSERTED.Sr_No,INSERTED.Doc_No WHERE Sr_No =?"rsPending.Parameters.Append rsPending.CreateParameter("SN", adDouble, adParamInput)rsPending.Prepared = True

RE: The OUPUT Command

VSVaidya — Wed, 31 Oct 2007 23:35:20 GMT

vijay, plz find examples of how to call SP through command object.SqlConnection conn = new SqlConnection();conn.ConnectionString = "server=(local);Integrated Security=SSPI;" + "database=Northwind";// TODO instantiate and populate SqlCommand object// TODO instantiate and populate SqlParameter objectsconn.Open();// TODO execute stored procedure// TODO get return value and result from stored procedureconn.Close(); // alternatively, conn.Dispose=======================================================================A SqlCommand object has 16 methods, 10 properties, and 1 event.SqlCommand cmd = new SqlCommand();cmd.Connection = conn;cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = "UpdatePrices";// TODO instantiate and populate SqlParameter objectscmd.ExecuteNonQuery(); // Results are returned as parameters only.There are four different execute methods:ExecuteReader use when rows are being returned (e.g., from a SELECT)ExecuteNonQuery use for INSERT, UPDATE, DELETE, SETExecuteScalar use when a single value such as count(*) is being returnedExecuteXmlReader XmlReader instance is built from the CommandTextThe UpdatePrices stored procedure returns data as parameters=======================================================================When working with parameterized stored procedures, a separate Parameter object must be instantiated for each parameter of the stored procedure. Each Parameter object must be added to the Command object’s Parameters collection. If you want to retrieve a stored procedure’s return value, it must be added to the Parameters collection first. Notice that the SqlDbType enumeration is used.Enumerations are named groups of constants. They are exposed through Intellisense. The SqlDbType enumeration provides constants representing each of the SQL Server datatypes. There is a DbType enumeration for other databases.SqlCommand cmd = new SqlCommand();cmd.Connection = conn;cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = "UpdatePrices";SqlParameter paramReturnValue = new SqlParameter();paramReturnValue.ParameterName = "@return_value";paramReturnValue.SqlDbType = SqlDbType.Int;paramReturnValue.Direction = ParameterDirection.ReturnValue;SqlParameter paramPAF = new SqlParameter();paramPAF.ParameterName = "@PriceMultiplier";paramPAF.SqlDbType = SqlDbType.Decimal;paramPAF.Direction = ParameterDirection.Input;paramPAF.Value = textBoxPAF.Text;SqlParameter paramNbrRows = new SqlParameter();paramNbrRows.ParameterName = "@NbrRows";paramNbrRows.SqlDbType = SqlDbType.Int;paramNbrRows.Direction = ParameterDirection.Output;cmd.Parameters.Add(paramReturnValue); // must be added first, parameter 0cmd.Parameters.Add(paramPAF); // parameter 1cmd.Parameters.Add(paramNbrRows); // parameter This code can be collapsed into fewer lines by taking advantage of the overloaded =======================================================================Since working with parameters has been covered, it is time to switch to a stored procedure that uses a select statement to return data as rows, not parameters. The Ten Most Expensive Products stored procedure in the Northwind database meets these requirements. The SqlCommand object is replaced by a SqlDataAdapter object (there are also OleDbDataAdapter and OracleDataAdapter classes). Because our stored procedure’s name contains embedded blanks, brackets are required as delimiters.SqlConnection conn = new SqlConnection( "server=(local);Integrated Security=SSPI;database=Northwind");SqlDataAdapter da = new SqlDataAdapter("[Ten Most Expensive Products]",conn);da.SelectCommand.CommandType = CommandType.StoredProcedure;// Pass the name of the DataSet through the overloaded constructor of// the DataSet class. When the DataSet is represented as XML, this name// is used as the name of the XML document element.DataSet dsNorthwind = new DataSet("Northwind");conn.Open();da.Fill(dsNorthwind);// You could call a second stored procedure by using// da.SelectCommand.CommandText followed by da.Fillconn.Close(); // alternatively, conn.DisposegrdNorthwind.DataSource = dsNorthwind.Tables[0];It is the Fill method of the DataAdapter object that executes the stored procedure.

RE: The OUPUT Command

vijay_download — Wed, 31 Oct 2007 23:17:41 GMT

HI,I have read your article about parameter OUTPUT.Let me know/Help.. How can we get/Return value from query(Specifically for UPDATE command) executed through Command Object which has the prepared version of query.?In advance..Thank You.vijayvijay_download@yahoo.co.in

RE: The OUPUT Command

Ronald Green — Mon, 30 Jul 2007 05:39:00 GMT

Hi,

You forgot to mention that using the output clause within UPDATE and DELETE statements can result in process blocking and even deadlocks.

RE: The OUPUT Command

DoktorBlue — Tue, 10 Jul 2007 22:36:00 GMT

Thank you, the INTO clause should have been discussed together with the OUTPUT clause in the first place. However, this example shows how wel this community works.

Thanks!

RE: The OUPUT Command

Christina Le-215319 — Tue, 10 Jul 2007 12:05:00 GMT

This is how developers use the information from the OUTPUT clause. I got this from this site: http://www.code-magazine.com/Article.aspx?quickid=0501041DECLARE @UpdatedTable TABLE( UpdatedTableID INT, OldData VARCHAR(20), NewData VARCHAR(20))UPDATE OutputTest SET Name = UPPER(Name)OUTPUT Inserted.KeyID, Deleted.Name, Inserted.NameINTO @UpdatedTableSELECT * FROM @UpdatedTableResults:UpdatedTableID OldData NewData6 Jim JIM7 Markus MARKUS 8 Rod ROD

RE: The OUPUT Command

dileep kumar-372369 — Tue, 10 Jul 2007 00:55:00 GMT

Its wonderful !

RE: The OUPUT Command

Olga B — Mon, 09 Jul 2007 13:02:00 GMT

I wondered the same thing and came up with the following:DECLARE @Output table( ID int, Code VARCHAR(25), Name VARCHAR(50), Salary Numeric(10 , 2))INSERT INTO TempTable ( Code , Name, Salary)OUTPUT Inserted.ID , Inserted.Code , Inserted.Name , Inserted.Salary INTO @OutputVALUES( 'A005' , 'Jennifer', 500 )It would have been helpful if OUTPUT INTO were discussed in the article. Otherwise, a great tip!

RE: The OUPUT Command

Steve Jones - SSC Editor — Mon, 09 Jul 2007 07:54:00 GMT

Typo's fixed.

RE: The OUPUT Command

Andy Warren — Mon, 09 Jul 2007 07:32:00 GMT

Dinesh, I no longer have the power! But I'll forward the link to Steve for you and he will make the correction, but he's headed to the UK this week so may take a day or two.

RE: The OUPUT Command

Mike Dougherty-384281 — Mon, 09 Jul 2007 07:29:00 GMT

"Select Scope_Identity() as LastInsertID"Of course the real-world use of scope_identity() is to set a variable or an output parameter, since only a hack DBA would ever attempt to copy/paste identity values in an interactive session - right?

RE: The OUPUT Command

Dinesh Asanka — Mon, 09 Jul 2007 07:25:00 GMT

Andy,

is it possible you to do following changes in the article

SELECTSCOPE_IDENTITY() should be SELECT SCOPE_IDENTITY() in first example block.

In the last example block where the delete statements are WHEREID = should be WHERE ID

RE: The OUPUT Command

Andy Warren — Mon, 09 Jul 2007 06:26:00 GMT

Jimi, that's not quite correct. Here's what it says in BOL:

output_table cannot:

Have enabled triggers defined on it.
Participate on either side of a foreign key constraint.
Have CHECK constraints or enabled rules.

You can use it on any table regardless of configuration, it's only the output table where you're landing the results that has restrictions.

The output clause is one of the really nice improvements in 2005. I've seen all too many instances where developers were essentially inserting an alternate primary key so that they could insert multiple rows at once and still get the keys back. If they had used scope_identity() they would have been forced to insert the records one at a time which is not great for performance.

Essentially the output clause exposes the logical inserted and deleted tables that were previously only visible within the trigger execution.

RE: The OUPUT Command

Dinesh Asanka — Mon, 09 Jul 2007 05:37:00 GMT

sorry for the query errors

RE: The OUPUT Command

Jimi Meyer — Mon, 09 Jul 2007 05:36:00 GMT

Missing some key information such as when you cannot use it. Will not work if there is a trigger on the table, or if it is involved in foreign key, or has check constraints. All of those are pretty common.

RE: The OUPUT Command

TJ-205129 — Mon, 09 Jul 2007 05:28:00 GMT

Useful tip, but I think the editor should have done more editing (and proofreading.)

RE: The OUPUT Command

Johan van Tonder — Mon, 09 Jul 2007 00:03:00 GMT

Yes indeed. Very valuable feature. Performance can be greatly enhanced by this, where triggers may be replaced. Those wondering about the missing spaces...

In the first example block, SELECTSCOPE_IDENTITY() should be SELECT SCOPE_IDENTITY(). In the last example block where the delete statements are WHEREID = should be WHERE ID =

RE: The OUPUT Command

Hans van Dam — Sun, 08 Jul 2007 23:37:00 GMT

Very valuable information to me. Too bad that te conclusion is wrong! There really is a need to perform modifications wherever @@identity or scope_identity() is used because they are not reliable:

after a rollback, the assigned identity is returned while it does not exist
when some other identity is assigned (in any table) between your insert and reading out @@identity you get that other identity

So, my conclusion would be that it really is nescessary to rewrite code whenever you need the newly assigned identity.

Regards,

Hans van Dam

RE: The OUPUT Command

DoktorBlue — Sun, 08 Jul 2007 23:12:00 GMT

Beside some missing spaces in the code, which is confusing, I miss information how the Output option returns its values to the devolper.

RE: The OUPUT Command

josephgabrie — Sun, 08 Jul 2007 21:40:00 GMT

Very useful hint

Regards

The OUPUT Command

Dinesh Asanka — Fri, 01 Jun 2007 10:49:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/dasanka/3041.asp