I think this would be best done either in the middle tier or by using a cursor.

If you have less than 1000 rows something like the following ghastly looking query...

Try sp_executesql with output parameters. Something like:

declare @EmployeeNo int, @AccessLevelID int, @err int

set @queryString = 'select @EmployeeNo = b.employee_no, @AccessLevelID = a.Access_Seq from '

  + @dbName + '..employee a, '...

-- Test data

declare @t table

(

 refNo char(5) not null primary key

)

insert @t

select 'ABC-0' union all

select 'ABC-1' union all

select 'XYZ-0' union all

select 'XYZ-1' union all

select 'XYZ-2' union all

select 'PQR-0' union all

select 'MNO-0'...

Maybe you are trying to do something like:

DECLARE @t TABLE

-- or create a temp table

(

 Policy_ProductId int not null primary key

 ,RowNum int identity not null )

INSERT @t (Policy_ProductId)

SELECT Policy_ProductId

FROM Policy_Product

INSERT INTO...

David,

WOW!

It took me 15 minutes to understand what you did. It is a lot more efficient than my approach.

All methods with their percentage of batch cost are below:

declare @t table

(

 tDate...

Take the query:

select T.tDate as DateFrom

 ,isnull

 (

  (select min(tdate)

  from @t T1

  where T1.price <> T.price

   and T1.tdate > T.tdate) - 1

 , D.MaxTDate) as DateTo

 ,T.price

from @t T cross join

 (

  select min(T5.tdate),...

The select statements are only test data. You will have to adapt the query so it works on your table.

This is more efficient:

select T.tDate as DateFrom

 ,isnull

 (

  (select min(tdate)

  from @t T1

  where T1.price <> T.price

   and T1.tdate > T.tdate) - 1

 , D.MaxTDate) as DateTo

 ,T.price

from @t T cross join

 (

  select min(T5.tdate),...

Try something like:

select *

from dbo.Companies C

 join dbo.Contacts E on C.companyId = E.companyId

where E.contactId = ( select min(E1.contactId)

 from dbo.Contacts E1

 where E1.companyID = C.companyID)

There is probably a better way, but this seems to work.

declare @t table

(

 tDate datetime not null primary key

 ,price int not null

)

insert @t

select '20070101', 100 union all

select '20070102', 100 union all

select...

try:

 INSERT INTO my_table (comments,

                                sr_status,

                                sr_notes_count,

                                sr_activity_count,

                                last_updated_dt)

 SELECT 'some input comments',

             (SELECT max(status) FROM my_table_2 WHERE id = 123),

             (SELECT count(*) FROM my_table_3 WHERE id = 123),

             (SELECT count(*) FROM...

PW's approach is more efficient.

--test data

declare @t table

(

 tName varchar(20) not null

 ,Event varchar(20) not null

 ,tTime datetime not null

)

-- 7.34% of cost

insert @t

select 'Name1', 'Start', convert(datetime, '8/16/06 8:00 AM', 0) union all

select...

--test data

declare @t table

(

 tName varchar(20) not null

 ,Event varchar(20) not null

 ,tTime datetime not null)

insert @t

select 'Name1', 'Start', convert(datetime, '8/16/06 8:00 AM', 0) union all

select 'Name2', 'Start', convert(datetime, '8/16/06 8:15 AM', 0)...

Run the profiler on the bits of the application which do not work and see what is called just before they fail.

Your query looks like it should work. You could simplfy it by trying something like:

SELECT A.t_Code

FROM C_CODES A

 LEFT JOIN Stories S ON S.story_dt BETWEEN '20060810 09:00' AND '20060810 10:00'

    AND ' '...