I think you need to put an UPDLOCK in the derived table in order to guarantee that only one transaction can read the rows. Also, there may as well be...

You will need to return an OUTPUT parameter.

The SP should look something like:

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE  PROCEDURE dbo.spCDI_Add_New_CDI_Data

    @sTelenumber varchar(100)

    ,@sDate smalldatetime

    ,@sTime varchar(20)

    ,@sRings varchar(20)

    ,@scallrecieved datetime

    ,@sName varchar(2000) OUTPUT

AS

SET NOCOUNT ON

SELECT...

Viking,

Does:

SELECT BPE.BusnPartEmpId

FROM dbo.tblBusnPartEmp AS BPE WITH (NOLOCK)

    INNER LOOP JOIN dbo.tblDivBranchBusnPartEmp AS DBBPE WITH (NOLOCK)

        ON BPE.BusnPartEmpId =DBBPE.BusnPartEmpId

    INNER JOIN dbo.tblDivBranch AS TDB WITH (NOLOCK)

        ON TDB.DivBranchId = DBBPE.DivBranchId

WHERE...

COALESCE seems to have some effect. No one had time to work out what in the following thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=323566#bm325392

A quick look at the query plan suggests that the COALESCE is...

Do the UNION ALL in a derived table. Something like:

SELECT CarrierName

    ,COUNT(*) AS Total

FROM (

        SELECT CarrierName

        FROM PreviousCarrierForWC

        UNION ALL

        SELECT txtPriorCarrier

        FROM PreviousCarrierForBA

    ) D

GROUP BY CarrierName

If the Players table does not change much, you may want to hard code like:

SELECT MAX(CASE PlayersID WHEN 1 THEN [Description] END) AS Team

    ,MAX(CASE PlayersID WHEN 2 THEN [Description] END)...

SELECT ER.*

FROM employee_rates ER

    LEFT JOIN employee E

        ON ER.employeeID = E.employeeID

            AND ER.emprate = E.emprate

WHERE E.employeeID IS NULL

or

SELECT *

FROM employee_rates ER

WHERE NOT EXISTS (

        SELECT *

        FROM employee E

        WHERE E.employeeID = ER.employeeID

            AND E.emprate =...

If you have less than about 10000 rows a triangular join, like the following, should be reasonably efficient.

If you have more than 10000 rows you will have to look at...

An index on [Date], Incident should help with the speed.

Two more thoughts for the view:

1. If you have indexes on the Foreign Keys, you could try explicitly using INNER MERGE JOIN instead of INNER JOIN.

2. I tend to avoid...

This assumes comments will never exceed 8000 characters.

-- *** Test Data ***

CREATE TABLE dbo.YourTable

(

    [Date] datetime NOT NULL

    ,Incident int NOT NULL

    ,Comments varchar(100) NOT NULL

)

GO

INSERT INTO dbo.YourTable

SELECT '20070228', 1, 'c1' UNION ALL

SELECT...

This may help:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=347037#bm347099

As your primary key has some meaning you can get away with something like:

-- *** Test Data ***

DECLARE @t TABLE

(

    Code varchar(5) NOT NULL PRIMARY KEY

    ,Prov varchar(25) NULL

)

INSERT INTO @t

SELECT 'BE31',...

This is all in BOL.

Smalldatetime is rounded to the nearest minute.

Milliseconds in datetime are rounded to 0, 3 or 7.

Sorry, that exhausts my suggestions.