-- ****************************************************************************
-- * DEADLOCK situations:                                                     *
-- *                                                                          *
-- * FIRST CONNECTION    SECOND CONNECTION                                    *
-- * begin tran          begin tran                                           *
-- * delete from a       delete from b                                        *
-- * select * from b     select * from a                                      *
-- * - FIRST connection waits for resource b and holds lock on a              *
-- * - SECOND connection waits for resource a and holds b                     *
-- * WE HAVE A DEADLOCK SITUATION HERE                                        *
-- * After a certain amount of time one of the transaction is chosen to be    *
-- * the victim of the deadlock situation. This transaction is aborted and    *
-- * rolledback.                                                              *
-- ****************************************************************************
exec spDeleteObj 'a', 3, 0
create table a
(
	id int identity(1,1) not null,
	col1 char(10)
)
insert into a values ('1')
exec spDeleteObj 'b', 3, 0
create table b
(
	id int identity(1,1) not null,
	col1 char(10)
)
insert into b values ('1')

-- ****************************************************************************
-- * LOCKING WITH CURSORS:                                                    *
-- *                                                                          *
-- * The STANDARD CURSORS control concurrent access through several options.  *
-- * these controls are PESSIMISTIC and OPTMISTIC.                            *
-- * PESSIMISTIC concurrency:                                                 *
-- *   Two types of pessimistic concurrency control:                          *
-- *  - Readonly which does not permit data updates                           *
-- *  - Scroll locked in which rows are locked when they are fetched inside a *
-- *    user-initiated transaction and no other user can gain access to these *
-- *    rows until the locks are released.                                    *
-- * OPTIMISTIC concurrency:                                                  *
-- *  Does not place locks on the database, allowing multiple users to access *
-- *  the same rows, and must resolve any update conflicts that may occur as  *
-- *  a result of simultaneous updates.                                       *
-- *                                                                          *
-- * The UPDATEABLE CURSORS support data modification statements that update  *
-- * rows through position updates. When positioned on a row in an            *
-- * updateable cursor, update or deleteoperations that target the base table *
-- * rows used to build the current row in the cursor can be performed. The   *
-- * positioned updates are performed on the same connection that opened the  *
-- * cursor. This allows the data modification to share the same transaction  *
-- * space as the cursor, and prevents the updates from being blocked by      *
-- * locks held by the cursor. Positioned updates in a cursor can be          *
-- * performed through Transact-SQL by using the WHERE CURRENT OF clause on   *
-- * an UPDATE statement and can be placed within explicit transactions.      *
-- ****************************************************************************

					

-- ****************************************************************************      
-- * spDeleteObj: Procedure for deleting an object from the database.         *
-- * - @objName - The object to delete                                        *
-- * - @objType - The type of the object                                      *
-- *      - 3 = table                                                         *
-- *      - 4 = procedure                                                     *
-- * - @verbose - 1 show status message; 0 do not show messages               *
-- ****************************************************************************      
if exists (select * from sysobjects where id = object_id('spDeleteObj') 
   and sysstat & 0xf = 4)    
begin    
   DROP procedure spDeleteObj    
end    
go
create procedure spDeleteObj(@objName varchar(50), @objType int, @verbose bit)    
as  
declare @objTypeName varchar(20)    
	if (@objType = 4) begin    
		set @objTypeName = 'procedure'    
	end    
	else if (@objType = 3) begin    
		set @objTypeName = 'table'    
	end    
	if exists (select * from sysobjects where id = object_id(@objName) 
		and sysstat & 0xf = @objType)    
	begin    
		exec('drop ' + @objTypeName + ' ' + @objName)    
		if (@verbose = 1) begin     
			raiserror('%s %s deleted.', 0, 1, @objTypeName, @objName)    
		end    
	end    
else if (@verbose = 1) begin    
	raiserror('%s %s does not exist....', 0, 1, @objTypeName, @objName)    
end    
go

-- ****************************************************************************      
-- * spParseXML: Example procedure for parsing XML with a stored procedure    *
-- * - @xmlDocument  - the contents of the XML document                       *
-- * In the select statement we search for '/root/Category/Product'. Once     *
-- * found we are on the product level. To retrieve the Category data we have *
-- * to move one level back; that is why we use the two dots and the forward  *
-- * slash.                                                                   *
-- ****************************************************************************      
exec spDeleteObj 'spParseXML', 4, 0    
go    
create procedure spParseXML(@xmlDocument varchar(1000))
as
declare @docHandle int  
	exec sp_xml_preparedocument @docHandle output, @xmlDocument    
	select    
		*
	from
		openxml(@docHandle, '/root/Category/Product', 1)    
		with (categoryid   int    '../@CategoryId',     
		productid   int    '@ProductId',     
		categoryname  varchar(50) '../@CategoryName',    
		description  varchar(50) '@Description')    
	exec sp_xml_removedocument @dochandle  
	go    

DECLARE @xmlDoc VARCHAR(8000)    
SET @xmlDoc =    
	'<root>    
		<Category CategoryId="1" CategoryName="General Books">    
			<Product ProductId="1" Description="Cooking for you" />    
		</Category>  
		<Category CategoryId="1" CategoryName="General Books">    
			<Product ProductId="2" Description="Cooking for you" />    
		</Category>    
		<Category CategoryId="1" CategoryName="General Books">    
			<Product ProductId="3" Description="Cooking for you" />    
		</Category>  
		<Category CategoryId="2" CategoryName="Special books">    
			<Product ProductId="4" Description="Cooking for you" />    
		</Category>    
	</root>'    
	exec spParseXML @xmlDoc    
	go

-- ****************************************************************************
-- * SELECT TOP <x> WITH TIES * FROM <table>                                  *
-- *                                                                          *
-- * See example below (table "tiesExample")                                  *
-- * Because we specify that we want the TOP 5 rows we expect no more then    *
-- * 5 rows. BUT: we also specified WITH TIES. Because row 6 in the table     *
-- * (according to the sortorder!) has the same value (a tie) as row 5 it     *
-- * is also returned in the resultset.                                       *
-- ****************************************************************************
exec spDeleteObj 'tiesExample', 3, 0    
go    
create table tiesExample    
(  
	id int,
	price int
)    
set nocount on    
insert into tiesExample values (1, 20)    
insert into tiesExample values (2, 30)    
insert into tiesExample values (3, 40)    
insert into tiesExample values (4, 50)    
insert into tiesExample values (5, 60)    
insert into tiesExample values (6, 60)    
set nocount off  

select 
	top 5 with ties * 
from 
	tiesExample    
order by 
	price asc