If cpindex is a biging, you shouldn't have to convert it. 

CREATE PROCEDURE dbo.viewReport

@myStaff nvarchar (50),

@myCoalition nvarchar (50)

AS

DECLARE @mainID bigint

DECLARE @secondaryID bigint

SELECT @mainid = CPIndex FROM CoalPol WHERE Staff=@myStaff...

I'm not really clear on what you're trying to do, but your convert isn't converting cpindex, it's trying to convert the string you've given it as the second argument.

Try:

CREATE PROCEDURE...

Size isn't the determiner of where data should go.  If the data you want to add relates to the primary key of the base table, the whole primary key, and...

Hmmm, best practice is to keep DBMS and web server on different boxes.  It's possible that you're having memory contention problems and that cycling the SQL Server frees things up,...

Are the web server and the SQL Server on the same box?  Can you run profiler while this is going on and trace the sql being sent to the server? ...

Performance tuning for a SQL DBMS involves a lot of other activities and analysis.  In my experience, most performance problems revolve around the SQL being used and the way the...

DBArtisan and RapidSQL from Embarcadero both analyse dependencies when creating scripts.  They're nice tools, but pretty pricey.

Try:

IF @AndOr = 'Or'

BEGIN 

INSERT INTO #AndOr blah blah blah

 SELECT     blah blah blah

 FROM         blah blah blah

 WHERE     blah blah blah

 ORDER BY blah blah blah

 INSERT INTO #AndOr blah blah blah

 SELECT     blah blah blah

 FROM         blah blah...

DBMS servers love memory.  SQL Server (and most others) will take all they can get as soon as they can use it.  Unless you specifically set the max memory, SQL...

Hmmm, the only thing another table might give you is more granular security, but you can accomplish the same effect by insulating direct access to the underlying table through views. ...

What business rule should the design support?  Can the customer only have one credit card? 

Without the business rules and the data, it's tough to guess whether any particular design...

Sometimes it just takes another pair of eyes.  We've all stared at a non-functional piece of t-sql and missed the obvious.  It keeps us all humble.

There's no 'BEGIN' to match the 'END'.  What do you expect the 'END' to be 'ending'?

Why not stick the part numbers to be queried in a temp table and make it a 4 table join?

If memory contention is really the problem, you might try setting the max memory size for the sql server to a value low enough to leave some room for IIS. ...