The OUTPUT Command

  • Christina Le-215319

    Grasshopper

    Points: 18

    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

    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

    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

    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

    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

    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

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

  • Matt Miller (4)

    SSC Guru

    Points: 124208

    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: 6173

    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

    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

    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

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

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