The OUTPUT Command

Question

Post reply

The OUTPUT Command

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Christina Le-215319 Grasshopper Points: 18 More actions · Answer 1

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=0501041

DECLARE @UpdatedTable TABLE

(

UpdatedTableID INT,

OldData VARCHAR(20),

NewData VARCHAR(20)

)

UPDATE OutputTest

SET Name = UPPER(Name)

OUTPUT

Inserted.KeyID,

Deleted.Name,

Inserted.Name

INTO @UpdatedTable

SELECT * FROM @UpdatedTable

Results:

UpdatedTableID OldData NewData

6 Jim JIM

7 Markus MARKUS

8 Rod ROD

DoktorBlue SSC Enthusiast Points: 107 More actions · Answer 2

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!

Kindest Regards,

DoktorBlue

Ronald Green SSC-Addicted Points: 432 More actions · Answer 3

Hi,

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

vijay_download Grasshopper Points: 24 More actions · Answer 4

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.

vijay

vijay_download@yahoo.co.in

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 5

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 0

cmd.Parameters.Add(paramPAF); // parameter 1

cmd.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.Fill

conn.Close(); // alternatively, conn.Dispose

grdNorthwind.DataSource = dsNorthwind.Tables[0];

It is the Fill method of the DataAdapter object that executes the stored procedure.

vijay_download Grasshopper Points: 24 More actions · Answer 6

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.Command

Set rsPending.ActiveConnection = cnnDefault

rsPending.CommandType = adCmdText

rsPending.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

Anipaul SSC-Insane Points: 24681 More actions · Answer 7

Anipaul

SSC-Insane

Points: 24681

June 6, 2008 at 4:33 am

#825345

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

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.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Tom Garth SSCertifiable Points: 6193 More actions · Answer 9

vijay_download (11/1/2007)
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.Command
Set rsPending.ActiveConnection = cnnDefault
rsPending.CommandType = adCmdText
rsPending.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

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 If

By the way... GOOD article. GOOD discussion.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

FVC Mr or Mrs. 500 Points: 598 More actions · Answer 10

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.

harij73 Grasshopper Points: 22 More actions · Answer 11

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 stored

b) limitations

c) performance gain when comparing to triggers

Otherwise a very valuable article, for the beginners to read with.

Thanks