Another issue nobody noticed ( ):

IT'S NESTED TRIGGER

INSTEAD OF UPDATE trigger makes UPDATE to the same table and fires itself.

I don't see in...

CREATE PROCEDUERE dbo.<Name>

@Text nvarchar(1000), @Subject nvarchar(50), @OrgId int

 Update NotificationTemplate

 Set Text = @Text,

 Subject = @Subject

 Where OrganizationID = @OrgId

 Insert into dbo.NotificationTemplate (OrganizationID, Subject, Text)

SELECT @OrgId,  @Subject, @Text

WHERE NOT EXISTS (select 1...

It's easy.

Type

CREATE VIEW dbo.<name>

AS

and put there whole block of those horrible

Select ...(including region, school, etc)

FROM ....

INNER JOIN ...

INNER JOIN ...

....

without WHERE...

You welcome!

Seems you're over the ditch.

If you are anyway refactoring this SP I would suggest to put all those joins into view...

Congratulations!

It depends.

Small table variables arer placed in memory, so it's really fast to access data in it.

But as soon as size of the table variable becomes significant comparing to available...

SELECT @ErrorNo = @@Error, @inscount = @@Rowcount

Than do whatever checks you need.

I cannot see any difference between 1st option of the code and "region 0" part in 2nd one. Probably I miss some tiny detail, but you must have used...

It's for you to decide.

If count(*) = 4 then 4 rows will be returned by normal select.

As I understand you get empty resultset, and that's a problem. So keep moving...

Yes, copy text of SP to Query Analiser, make it ALTER PROCEDURE, and add execution string at the end.

Now you can play with it, no application involved anymore.

After you have...

Looks all right, it must be data.

Run this:

select count(*)

from pod_Events

and comment all futher joins.

If result will be > 0 uncomment 1st join and rerun.

Finally you'll find the join...

And no errors???

Did you include this part for debug table?

Why it does not create the table, if there are no errors?

OK, what happens when you run it from QA?

I mean

exec stpEventsSearchResult @searchstring = '%%', @dtFrom = 'Jan 1 2000 12:00AM', @dtTo = 'Jan 1 2099 12:00AM', @searchtype = -1,...

Run the query you've got from Profiler in Query Analiser, and it will tell you everything, if you don't beleive in my words.

ContractPayment.Amount - sum (ContractPartyPayment.Amount) >= +0.01

will not use index definetely, operator "-" is a function as well.

So, you have nothing to loose.