Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

The OUPUT Command Expand / Collapse
Author
Message
Posted Monday, July 9, 2007 7:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:28 AM
Points: 257, Visits: 902
"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?


Post #379971
Posted Monday, July 9, 2007 7:32 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 1:37 PM
Points: 6,779, Visits: 1,865
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.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #379973
Posted Monday, July 9, 2007 7:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:24 AM
Points: 33,088, Visits: 15,197
Typo's fixed.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #379987
Posted Monday, July 9, 2007 1:02 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 4:26 PM
Points: 582, Visits: 451
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 @Output
VALUES( 'A005' , 'Jennifer', 500 )

It would have been helpful if OUTPUT INTO were discussed in the article. Otherwise, a great tip!
Post #380058
Posted Tuesday, July 10, 2007 12:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 22, 2013 9:14 AM
Points: 33, Visits: 67

 Its wonderful !

Post #380181
Posted Tuesday, July 10, 2007 12:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 16, 2011 7:01 PM
Points: 4, Visits: 73
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
Post #380411
Posted Tuesday, July 10, 2007 10:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 6, 2011 7:41 AM
Points: 20, Visits: 17

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

Post #380531
Posted Monday, July 30, 2007 5:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 1, 2013 10:44 PM
Points: 272, Visits: 143

Hi,

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

 

Post #386246
Posted Wednesday, October 31, 2007 11:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 16, 2007 2:26 AM
Points: 2, Visits: 3
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
Post #417384
Posted Wednesday, October 31, 2007 11:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: Banned Members
Last Login: Tuesday, July 22, 2014 3:58 AM
Points: 2,622, Visits: 327
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.

Post #417387
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse