You probably need to put the filters in the JOINs so that all the flights will show.

Something like:

SELECT [...]

FROM flights f,

LEFT JOIN person bs

ON f.bs_pilot = bs.number

LEFT JOIN person...

DENSE_RANK() OVER (PARTITION BY [AREAID] ORDER BY [BUILDINGID]) AS BUILDINGINDEX,

or you can use Application Locks with the procedure name as the resource.

http://msdn.microsoft.com/en-us/library/ms189823.aspx

You can use CASE but be VERY VERY careful.

You should check with the business what they want to happen when EXPECTEDDURATION is zero. It could be that there has been...

Also, I suspect you would be better having one SP for each module with all the SPs using static SQL.

Something like the following may help:

SELECT -- DISTINCT ?

vc.charge_id,

vc.service_item_id,

...

Or use the windowed functions:

To get your exact result:

-- *** Test Data ***

DECLARE @t TABLE

(

ID int NOT NULL

,ModelID int NOT NULL

,ClientId int NOT NULL

)

INSERT INTO @t

SELECT 1, 1, -1

UNION ALL...

1. A procedure has the settings for QUOTED_IDENTIFIER and ANSI_NULLS from when it is created.

(ie They need to be defined before creating the procedure.) I see no need for setting...

Try something like:

(SELECT COUNT(DISTINCT AcctNo) FROM @tmpTable WHERE AcctNo<= reg.AcctNo) AS RowNo,

or:

-- *** Test Data ***

DECLARE @A TABLE

(

AB_Id char(5) NOT NULL

,A_Code char(4) NOT NULL

,Unit varchar(10) NULL

,Line_Id int NOT NULL

,PK_Id int NOT NULL

)

INSERT INTO @A

SELECT 'FC002', '0041', '%', 1, 1

UNION ALL...

Maybe:

SELECT S.ClientNumber

,S.CaseNumber

,T.ReferralSourceType

,T.ReferralSourceDesc

FROM RequestForServices S

JOIN SourceOfReferral R

ON S.ReferralSource = R.ReferralSource

JOIN ReferralSourceTypes T

ON S.ReferralSourceType = T.ReferralSourceType

Or with the row difference method:

-- *** Test Data ***

DECLARE @t TABLE

(

ID int NOT NULL

,Data int NOT NULL

)

INSERT INTO @t

SELECT 1, 1

UNION ALL SELECT 2, 1

UNION ALL SELECT 3, 3

UNION...

In relational theory, tables are unordered sets so the Party column has no order unless you use an ORDER BY clause.

(ie SQL does not understand...

This is a nested join. I would enforce the use of brackets and formatting:

FROM scheme.cocontypm

INNER JOIN scheme.coconcdsm

ON scheme.cocontypm.contract_type = scheme.coconcdsm.contract_type

LEFT JOIN scheme.cfanalm

ON scheme.coconcdsm.analysis1 = scheme.cfanalm.analysis_code

RIGHT JOIN scheme.ophdcontm

ON...

-- *** Test Data ***

DECLARE @t TABLE

(

YCol varchar(10) NOT NULL

)

INSERT INTO @t

SELECT 'A01'

UNION ALL SELECT 'B02'

UNION ALL SELECT 'C03'

UNION ALL SELECT '2'

-- *** End Test Data ***

SELECT

CASE

WHEN YCol LIKE...