Database

WITH t1 AS (
    SELECT  r1.RoadID,r1.BEGIN
    FROM    Roads r1 Left outer join Roads r2
        ON  r1.RoadID = r2.RoadID
        AND r1.BEGIN > r2.BEGIN
        AND r1.BEGIN <= r2.END
    GROUP BY r1.RoadID,r1.BEGIN
    HAVING  Count(r2.BEGIN) = 0
    )
,t2 AS (
    SELECT  r3.RoadID,r3.END
    FROM    Roads r3 Left outer join Roads r4
        ON  r3.RoadID = r4.RoadID
        AND r3.END >= r4.BEGIN
        AND r3.END < r4.END
    GROUP BY r3.RoadID,r3.END
    HAVING  Count(r4.BEGIN) = 0
    )
,ContiguousStretch AS (
    SELECT  t1.RoadID,t1.BEGIN,Min(t2.END) AS END
    FROM    t1 join t2
        ON  t1.RoadID = t2.RoadID
        AND t1.BEGIN <= t2.END
    GROUP BY t1.RoadID,t1.BEGIN
    )
,Gaps AS (
    SELECT  RoadID
    FROM    ContiguousStretch
    GROUP BY RoadID
    HAVING  Count(RoadID) > 1
    )
SELECT  c.RoadID,BEGIN,END
FROM    ContiguousStretch c join gaps g
    ON  c.RoadID = g.RoadID
ORDER BY c.RoadID,BEGIN

WITH ordered AS(
    SELECT  RoadID,BEGIN,END,ROW_NUMBER() OVER(PARTITION BY RoadID ORDER BY BEGIN) as rn
    FROM    roads
    )
SELECT  o1.RoadID,o1.BEGIN,o1.END
FROM    ordered o1,ordered o2
WHERE   o1.RoadID = o2.RoadID
    AND o1.rn = o2.rn -1
    AND o1.END <> o2.BEGIN
UNION
SELECT  o2.RoadID,o2.BEGIN,o2.END
FROM    ordered o1,ordered o2
WHERE   o1.RoadID = o2.RoadID
    AND o1.rn = o2.rn -1
    AND o1.END <> o2.BEGIN

SELECT a.*, b.*
FROM	TableName a,
        TableName b
WHERE a.roadid = b.roadid
AND   b.start = (SELECT MIN(c.start)
                 FROM   TableName c
                 WHERE c.roadid = a.roadid
                 AND   c.Start  > a.start)
AND   a.End <> b.Start
ORDER BY ...

create procedure ptest as
begin
   raiserror( 'message', 10, 10 );
end;
go

sqlcmd -S .\instancename -E -d dbname -i testproc.txt

Msg 8152, Level 16, State 14, Line 11
String or binary data would be truncated.

gavindon wrote:

This report is embedded in an ASP.net app