SQL Server Determine how many rows were operated on in previous query
By Allen Stoner
Sometimes in a stored procedure or other T-SQL process you need to figure out how many rows were operated on by the last statement. Using the @@RowCount variable makes it quite easy.
select * from tblNames
Print @@RowCount
--will display the number of rows in the tblNames table because there's no where clause
update tblNames set
PhoneNumber = '(123)555-1212'
where
ID = 6
Print @@RowCount
-- should probably display 1, because only one row is to be updated. Better check your data if more than one returned :)
Related FAQs
Microsoft SQL Server 2008 has an XML data for storing such data. A nice thing about using this datatype instead of just a large text column is the ability to query the XML column and retrieve elements and their values. Using this technique you can create a view that makes the XML data look a lot like a regular MS SQL table.
The bold line is the statement to pull the first element with a value of FirstName from within the XML in the xml datatype column.
Often times you want to insert a row into a SQL table that has an identity column and then use the the value of the indentity for the new row in other statements. There are a couple ways to do this in SQl Server.
Here's a quick and dirty query that uses a cursor to loop across all the linked servers on a MS SQL server and check the current database for that string. Resulting output not formated real great, but it will point you in the right direction for finding linked servers.
Here's a quick query to list all the tables in a database joined with their columns and displaying the column types and sizes. Works in MS SQL 2000, 2005 and 2008.
The easiest way to put XML data into an XML datatype column in Microsoft SQL server is to use the INSERT INTO command and just put the XML in as a string.
SQL Server Determine how many rows were operated on in previous query (746 Views)