CREATE PROCEDURE [dbo].[usp_Index_ListStatements]

@AccountNumber as nvarchar(19),
@FromDate as datetime,
@ToDate as datetime
AS
SET NOCOUNT ON

DECLARE @TablePrefix as int
DECLARE @TmpTable TABLE 
(
StatementDate datetime ,
AccountNumber nvarchar(19) COLLATE Latin1_General_CI_AS NOT NULL
)
SELECT TablePrefix INTO #tmpPrefix FROM Statements.dbo.directory WHERE rundate>=@FromDate AND rundate <=@ToDate

DECLARE TableC CURSOR FOR
SELECT TablePrefix FROM #tmpPrefix
OPEN TableC
FETCH NEXT FROM TableC
INTO @TablePrefix
WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @TmpTable (StatementDate,AccountNumber)
		EXEC ('SELECT StatementDate, AccountNumber FROM Statements.dbo.[' + @TablePrefix + '_headers] WHERE AccountNumber=' + @AccountNumber)

		FETCH NEXT FROM TableC
		INTO @TableIndex
	END
CLOSE TableC
DEALLOCATE TableC
SELECT * FROM @TmpTable

quote:

---

Originally posted by sachinsamuel

Since the insert need to fired dynamicly, which are stored in the "Statements.dbo.directory" I don't think there is any other way which u r doing. But We can improve the way u r doing.

1) U can use table data types rather than temporary table.
2) U can use the sp_executeSQL rather than exec, which will help u to use the cache.

Let me know in case of any improvement.

Regards

Sachin

---

quote:

---

Originally posted by ehorn

How many potential tables can TableC cursor return?
Have you considered UNION?

---

quote:

---

Originally posted by RickD

Whoops, forgot about that... Leave it as a pre-created #tmp table...

---