This is straightforward as a CLR, the C# code is roughly this

[SqlFunction(FillRowMethodName = "DirListingFillRow", Name = "DirListing", TableDefinition = "filepath nvarchar(max)")]

public static IEnumerable DirListingInit(SqlString path)

{

return Directory.GetFileSystemEntries(path.Value);

}

private...

SELECT m.value('(StaffDetails/NINumber)[1]','VARCHAR(10)') AS NINumber,

b.value('ContractStart[1]','VARCHAR(10)') AS ContractStart,

b.value('(PostLevelDetails/Hours/HoursPerWeek)[1]','DECIMAL(10,2)') AS Hours

FROM @doc.nodes('/SchoolWorkforceMember') AS R(m)

OUTER APPLY m.nodes('ContractOrServiceGroup/ContractOrService') AS A(b)

Maybe this?

SELECT m.value('(StaffDetails/NINumber)[1]','VARCHAR(10)') AS NINumber,

b.value('FirstDayOfAbsence[1]','VARCHAR(10)') AS FirstDayOfAbsence,

b.value('LastDayOfAbsence[1]','VARCHAR(10)') AS LastDayOfAbsence

FROM @doc.nodes('/Message/SchoolWorkforceMembers/SchoolWorkforceMember') AS R(m)

OUTER APPLY m.nodes('Absences/Absence') AS A(b)

See if this helps

select distinct object_name(id)

from sys.sysdepends

where object_name(depid)='mytable'

and objectproperty (id,'IsProcedure') = 1

Also suggest looking at sys.sp_refreshsqlmodule to update the dependencies first (I'm not sure this is foolproof though)

Here's another way

with CTE as (

select myID,myName,myMoney,

sum(myMoney) over(partition by myID) as sm,

row_number() over(partition by myID order...

Another way is to use GROUP BY ALL, although according to BOL this feature may be removed from future versions of SQL Server

SELECT ResourceID, COUNT(ScheduleId) AS Count

FROM ptSchedule

WHERE (Status <>...

with cte as (

Select company,

referral,

referral_date,

row_number() over(partition by company...

Assumes a maximum of two levels of category

select p.manufacturerPartNumber as item,

c.name as Category,

subc.name as SubCategory

from product...

Michael Earl (12/10/2008)

---

Assuming you really only have 6 reactions, a crosstab will work:

[font="Courier New"]CREATE TABLE #Reaction (ReactionID INT, Description VARCHAR(20))

INSERT #Reaction VALUES (1,'Amnesia')

INSERT #Reaction VALUES (2,'Wheezing')

INSERT #Reaction VALUES (3,'Rash')

INSERT #Reaction...

Try ordering by a materialised path. Something like this

WITH TestSteps (ProcessID, ExecProcessID, TestStepID, ComponentActionID,

Narrative...

Try using NULLIF

select * from apptview where

recurDays > 0 and

(DateDiff(dd, apptDateTime, '12/7/2008') > 0) and (

(DateDiff(dd, apptDateTime, '12/6/2008') / NULLIF(recurDays,0)) <

(DateDiff(dd, apptDateTime, '12/7/2008') / NULLIF(recurDays,0)))

and...

Decimal(16,9) allows 7 digits to the left of the decimal place, your number has 9, so

SET @BrutoPrijs = 100000000.0000

will fail

SELECT * FROM mytable

WHERE POLE_ID NOT IN

(SELECT DISTINCT POLE_ID FROM mytable L

WHERE (L.DATE1 IS NULL OR L.DATE2 IS NOT NULL OR L.DIVISION <> 'E') AND L.IGNORE = 0)

...

SELECT @AllMarketsCount = COUNT(*)

FROM .......

WHERE ........

SELECT @UploadedMarketsCount = COUNT(*)

FROM .....

WHERE ......

Try this, you'll need a numbers/tally table

http://www.sqlservercentral.com/articles/TSQL/62867/

declare @mcode varchar(40)

set @mcode= '5857275' -- 585

set @mcode= '00112476' -- 0011246

select top 1 t.mcode

from mytable t

inner join Numbers n on n.Number between 1 and...