|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, April 14, 2009 5:38 PM
Points: 9,
Visits: 6
|
|
"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?
|
|
|
|
|
SSCertifiable
Group: Moderators
Last Login: Thursday, March 11, 2010 10:33 AM
Points: 6,371,
Visits: 950
|
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Today @ 2:53 PM
Points: 23,148,
Visits: 6,912
|
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Tuesday, February 23, 2010 3:37 PM
Points: 412,
Visits: 293
|
|
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!
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Monday, March 08, 2010 4:08 AM
Points: 33,
Visits: 55
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 14, 2010 10:00 AM
Points: 4,
Visits: 65
|
|
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
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Monday, November 16, 2009 5:09 AM
Points: 20,
Visits: 13
|
|
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
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Thursday, March 11, 2010 4:09 AM
Points: 272,
Visits: 104
|
|
Hi, You forgot to mention that using the output clause within UPDATE and DELETE statements can result in process blocking and even deadlocks.
|
|
|
|
|
Forum 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
|
|
|
|
|
Ten Centuries
Group: Banned Members
Last Login: Wednesday, November 18, 2009 3:37 AM
Points: 1,032,
Visits: 216
|
|
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.
|
|
|
|
