SQL 2005 - TSQL Enhancements
--------------------------------
--SQL 2005 - TSQL Enhancements--
--------------------------------
------------------
--Error Handling--
------------------
--In SQL 2000, you had to check and handle @@Error after each statement
--In SQL 2005, we have TRY, CATCH (but no FINALLY)
CREATE PROCEDURE [dbo].[ErrorProc]
AS
DECLARE @Value int;
BEGIN TRY
SELECT @Value = 200;
PRINT 'Value set to 200';
--Force Error
SELECT @Value = @value / 0;
PRINT 'Value divided by 0';
END TRY
BEGIN CATCH
PRINT 'In Error Handler'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_SEVERITY() AS ErrorSeverity,
@Value
END CATCH
GO
EXEC ErrorProc
------------------------------------------------------
--New Datatype size for varchar, nvarchar, varbinary--
------------------------------------------------------
--It is now possible to use (MAX) as a datasize for varchar, nvarchar, varbinary
--Eg. varchar(MAX), nvarchar(MAX), varbinary(MAX)
--These *replace* text, ntext and image datatypes, although they remain fo backwards
--compatibility
--
--These can be used as local variable
DECLARE @MaxNvarchar nvarchar(MAX)
SELECT @MaxNvarchar = REPLICATE('This is big data ', 250)
SELECT @MaxNvarchar = @MaxNvarchar + '*** Steve *** '
SELECT @MaxNvarchar = @MaxNvarchar + REPLICATE('And lots more text ',200)
SELECT @MaxNvarchar = @MaxNvarchar + REPLICATE('And lots more text ',200)
SELECT @MaxNvarchar = @MaxNvarchar + '*** End ***'
SELECT
@MaxNvarchar AS [Data]
,DATALENGTH(@MaxNvarchar) AS [DataLength]
,LEN(@MaxNvarchar) AS [Len]
,CHARINDEX('End', @MaxNvarchar) AS [EndPos]
GO
--Becareful though. Not all functions operate over more than 8000 characters
DECLARE @MaxNvarchar nvarchar(MAX)
SELECT @MaxNvarchar = REPLICATE('This is big data ', 250)
SELECT @MaxNvarchar = @MaxNvarchar + '*** Steve *** '
SELECT @MaxNvarchar = @MaxNvarchar + REPLICATE('And lots more text ',400)
SELECT @MaxNvarchar = @MaxNvarchar + '*** End ***'
SELECT
@MaxNvarchar AS [Data]
,DATALENGTH(@MaxNvarchar) AS [DataLength]
,LEN(@MaxNvarchar) AS [Len]
,CHARINDEX('End', @MaxNvarchar) AS [EndPos]
--Varbinary(MAX) allows binary data upt o 2Gb
DECLARE @MaxVarBinary varbinary(MAX)
--XML datatype - more on this later
DECLARE @xmldata xml
----------------------------
--Common Table Expressions--
----------------------------
--Show Tables
select * from emp;
with emph(id, name, mgrid, lvl, rootmgrid)
as
(
--anchor member
select id, name, mgr_id, 0, id
from emp
where mgr_id IS NULL
union all
--RECURSIVE STEP
select e.id, e.name, e.mgr_id, lvl + 1, eh.rootmgrid
from emp e
join emph eh
on eh.id = e.mgr_id
)
select * from emph where rootmgrid = 1
SELECT * FROM emp
---------------------
--RANKING FUNCTIONS--
---------------------
--Row Number--
SELECT
[name]
,[age]
,ROW_NUMBER() OVER(ORDER BY [name]) AS namerownumber
,ROW_NUMBER() OVER(ORDER BY [age]) AS agerownumber
FROM
emp
ORDER BY
[name];
--Paging--
WITH myemp AS
(
SELECT
[name]
,[age]
,ROW_NUMBER() OVER(ORDER BY [name]) AS namerownumber
FROM
emp
)
SELECT
*
FROM
myemp
WHERE
namerownumber BETWEEN 4 AND 6
--Ranking--
with emprank
as
(
select
row_number() over(order by id) AS [Row],
rank() over (order by sales desc) AS [Rank],
rank() over (partition by country order by sales desc) AS [RankByCountry],
dense_rank() over (order by sales desc) AS [DenseRank],
ntile(5) over (order by sales desc) AS [FifthPercentile],
name,
country,
age,
sales
from
emp
)
select * from emprank
----------------------------------------------
--Replacing the need for cursors/while loops--
----------------------------------------------
--This example shows the creation of running total sales
--The main purpose, is to demonstrate how much easier the code is with 2005
--In 2000, you would have to insert into a temp table with an identity value
--Then create a cursor over the temporary table, iterating over each row
--updating the new total, to a running total value of sales
drop table #totals
select row_number() over (order by id) rn,
name, country, sales into #totals
from emp;
with rt (rn, name, country, sales, runtot)
as
(
select rn, name, country, sales, sales
from #totals
where rn = 1
union all
select t.rn, t.name, t.country, t.sales, t.sales + rt.runtot
from #totals t
join rt on t.rn = rt.rn + 1
)
select * from rt
drop table #totals
------------------
--Pivot function--
------------------
select * from sales
pivot( sum(total) for period in ([Q1], [Q2], [Q3], [Q4])) p
----------------------------------------------------
--Output affected rows during update/delete/insert--
----------------------------------------------------
--drop procedure updSales
create procedure updSales
as
--Create table to store modified rows
DECLARE @tab TABLE (id int, name varchar(15), oldsales int, newsales int)
BEGIN TRAN
--Modify some rows
UPDATE
emp
SET
sales = sales + 50
OUTPUT
inserted.id,
inserted.name,
deleted.sales,
inserted.sales
into @tab
WHERE
sales >= 400
--Rollback transaction, as test proc and don't really want to alter db
ROLLBACK TRAN
--Return affected rows
select * from @tab
GO
SELECT * FROM emp
EXEC updSales
GO
-----------
--New TOP--
-----------
SELECT
*
FROM
Sales
DECLARE @rows int;
SELECT @rows = 3;
SELECT TOP(@rows)
*
FROM
Sales
--It is also possible to use this on INSERT/UPDATE/DELETEPublished Saturday, February 11, 2006 3:17 PM by sbates
Filed under: SQL 2005 - Code samples, SQL 2005 - What's New
