Try something like this:

drop table #table1

go

drop table #table2

go

create table  #table1 (id int identity(1,1), name varchar(10))

insert #table1 values('jeff')

insert #table1 values('mary')

insert #table1 values('edward')

create table #table2 (id int, name varchar(10))

insert #table2 values(1, 'james')

--if...

Some basic optimzation techniques to check:

Are the datatypes for each column used in a join the same?  Data conversion causes indexing problems.

Are there indexes indexes on JobPosting_ID in both tables...

Try this:

select COALESCE(cnts.cnt, 0) 'Received Applicants', j.JobPosting_ID,  j.Job_Title, j.Job_Location, j.Job_ZipCode

from Job_Posting_Table j

left outer join (select count(*) cnt, jp.JobPosting_ID

                 from Job_Posting_Table jp

                 cross join Appliant_Table a

                 where distance(jp.Job_ZipCode, a.Applied_ZipCode) < a.Applied_Distanct

                ...

This is untested because the zip data does not match the job and applicant data but try something like this:

drop table Zip

go

create table Zip(Zip_Code int,  Longitude decimal(10,6),  Latitude decimal(10,6))

go

insert Zip...

Try something like this.  It may be possible to enhance it to not use a loop.

----------------------------------

declare @Table1 table (task varchar(6))

insert @Table1 values ('Task C')

insert @Table1 values ('Task D')

insert @Table1 values...

According to BOL, the TEXT datatype can store up to 2³¹-1 (2,147,483,647) bytes of data.

What error are you getting?

You can also try:

declare @Table1 table (someval int, fname varchar(20))

declare @Table2 table (someval int, fname varchar(20))

insert @Table1 values(1, 'Jeff')

insert @Table1 values(2, 'James-1')

insert @Table2 values(2, 'James-2')

insert @Table2 values(3, 'Ed')

select coalesce(t1.fname, t2.fname)...

My first bit of advice would be to stop using the TOP 100 PERCENT hack to order the view.  The ordering should be done in the select from the view,...

The reason that it is failing is that some errors cause the batch to terminate, instead of the statement.  In this case, each EXEC is considered a batch.  Here is...

Another example of how to trap the error:

drop table customer

go

create table customer(adddate datetime NOT NULL)

go

drop procedure sp_addNewCustomerAndAccount

go

Create procedure sp_addNewCustomerAndAccount

as

insert Customer Values(NULL)

RETURN @@ERROR

go

DROP PROCEDURE [dbo].[spBATCH_FILE_PROCESS]

go

CREATE PROCEDURE [dbo].[spBATCH_FILE_PROCESS]

AS

declare @Err int

BEGIN...

The following example causes a rollback:

drop procedure sp_addNewCustomerAndAccount

go

Create procedure sp_addNewCustomerAndAccount

as

RAISERROR ('ERROR', 10, 1)

go

DROP PROCEDURE [dbo].[spBATCH_FILE_PROCESS]

go

CREATE PROCEDURE [dbo].[spBATCH_FILE_PROCESS]

AS

declare @Err int

BEGIN TRAN

---Run some queries, etc, and then:

EXEC sp_addNewCustomerAndAccount

select...

Yes it does.  You may want to try something like:

Select

, Person

, First

, Last

, DATEDIFF(day, x.Last, x.First) AS Days_Between

, CASE WHEN DATEDIFF(day, x.Last, x.First) >= 30 THEN 'Mark this person' ELSE...

Ray has the answer for handling the transactions in the procedure. 

Check out the SQL BOL topic for 'nested transactions' for a detailed explanation.  The following are quotes from it:

"Committing inner...

Paramind is suggesting using a table structure like this:

create table Reading

(

  ReadingID bigint Identity(1,1),

  timetaken datetime,

  locationid int

--add other qualifying data

)

create table ReadingData

(

  ReadingID bigint,

  Value float

)

--then the following is very...