WHERE Field = ''

If you have multiple spaces then something like LTRIM(Field) = '' OR LEN(Field) = 0 (this is not performance friendly on large datasets depending on the data...

@temprowcount is null and as such concatenating anything to it will yeild null (if ansi nulls is enabled)

In esence both of your query string variables will always be null.

[Code]

DECLARE...

you might want to create a reporting database (readonly snapshot / mirror / etc )

And put indexes on the table in that database

A cube would also be a choice.

Basically, it...

using a numbers / tally table

--drop tally table

DROP TABLE dbo.Numbers;

GO

-- Now re-create it and fill it with sequential numbers starting at 1

SELECT TOP 100000 IDENTITY(INT,1,1) AS Number

INTO dbo.Numbers

FROM master.INFORMATION_SCHEMA.COLUMNS i1

CROSS...

Just a note.

In sql 2000 you could install an add in to be able to debug ( been a long time).

In 2005 you can debug the procedures ect in visual...

Make an SSIS package with a transfer SQL server Objects task.

Make sure the column that is foreign keyed to the large tables are indexed in the large tables.

Check for triggers on the tables impacted.

you can get and install the client tools for sql 2000 (Enterprise manager)

It can be installed along side SQL management Studio.

Then use Enterprise Manager instead of SMS to interact with...

Distinct will not work for you, after I looked at your Select closer.

Add this join into your query.

JOIN (SELECT

MAX(EmpID) as EmpId,

SSN

FROM Health.dbo.EmployeeInfo

GROUP BY SSN ) as LastEmpForSSN ON...

instead of inserting directly into your table you can fill a temp table and then just insert the max id for a SSN into the real table.

or it looks like...

The view could be faster if it is a materialized / indexed view, but in my experience there are so many constraints on when you can use a materialized view...

Create your temp table such that it matches your procedure results and then you can fill it.

This does not work multiple levels deep however.

[Code]

CREATE PROCEDURE TestSelect

AS

SET NOCOUNT ON

SELECT...

sure.

you just have to join to itself

SELECT DISTINCT

tmp.ShipmentLineItemID

FROM #testSLIS tmp

LEFT JOIN #testSLIS sts ON tmp.ShipmentLineItemID = sts.ShipmentLineItemID

AND sts.statuscode = 'SHI'

WHERE tmp.statuscode...

SELECT

...

FROM shipmentline sl

LEFT JOIN shipmentLineStatus sts ON sl.ShipmentLineItemID = sts.ShipmentLineItemID

AND sts.statuscode = 'SHI'

WHERE sts.ShipmentLineItemID IS NULL --does not exist

not exists could also be used, as well as not in (which...

the having statement will go after your group by

WHERE t.Active = 0 AND ....

GROUP BY

t.ConsultantID

, t.ConsultantName

...