-- ****************************************************************************      
-- * 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