I was just trying out the use of nested transactions when I saw your reply. Too bad. I must agree the TRY..CATCH seems to be pretty worthless in this case....

The conversion error (severity 16) puts your transaction into an uncommittable state. From BOL:

If an error generated in a TRY block causes the state of the current transaction to be...

Paul, nice solution! However, the first day of the month is missing. Either subtract 1 from DayOffset.n as the parameter for the dateadd function or use a zero-based DayOffset (0-30)...

Damn, this is definitely not my day! Bad testing. Sorry.

Peter

You can speed up things a little by doing a 'preselect' on the raw integer values:

SELECT

*

FROM

DateTable

WHERE

DATE1 + 204 > DATE2

AND...

Nice tweak, which probably saves you a couple of clock cylces.

Peter

Paul White (1/11/2010)

---

Quite so - it's just that the result of the ABS(SIGN(B)) is only used in the ORDER BY part of the ROW_NUMBER ranking function, so I would have...

Thanks, Paul.

The...

BTW, I change the testdata as follows because the parentid's were not correct.

select 1, 'Electronics', 0 union all

select 2, 'TV', 1 union all

select 3, 'Panasonic', 2 union all

select 4, 'LG',...

You can use a recursive CTE for this:

WITH CTE(CategID, FullCategName, OrderString) AS

(

SELECT

CategID, cast(CategName as varchar(max)),

cast(cast(CategID as char(5)) as varchar(max))...

Just a slight modification to Lynn's solution : replace

order by B with order by abs(sign(B))

Here's the full script:

create table dbo.TestTable (

A char(2),

B...

You can use dynamic SQL to do this i.e.

set language English

declare @qry nvarchar(max)

select @qry = 'delete from ' + datename(month, getdate())

exec (@qry)

Use set language to be sure you get the...

I'm also at SP3, but BOL has to be updated separately. See the readme for SP3.

Peter

Edit: added hyperlink

I just downloaded the latest version of BOL 2005 (november 2008). The text didn't change. The topic 'sp_executesql (Transact-SQL)' is annotated as follows: Updated: 5 December 2005. So the quote...

Here's what my BOL (september 2007) says:

[ @stmt = ] stmt

Is a Unicode string that contains a Transact-SQL statement or batch. stmt must be either a Unicode constant or a...