Here's my stored procedure in SQL Server 2000:
CREATE PROCEDURE [spGetClients] @CurrentPage int, @PageSize int, @TotalRecs int output AS
select * into #TempTable from clients where 1 = 2
alter table #TempTable add id int IDENTITY PRIMARY KEY
--fill the temporary table with data
INSERT into #TempTable
SELECT *
FROM Clients
SELECT *
FROM #TempTable
where id > 3
--return total # recs available as an output parameter
SELECT @TotalRecs = COUNT(*) FROM Clients
GO
Records are inserted ok into #TempTable and each record is assigned a unique ID (the new field added). My problem is that the SELECT * FROM #TempTable WHERE id > 3 gives me an error "Invalid column name 'id'".
Is this a SQL bug? If I take out the WHERE clause on the SeLECT statement, it shows me the records with their ids.
Thanks!
CREATE PROCEDURE [spGetClients] @CurrentPage int, @PageSize int, @TotalRecs int output AS
select * into #TempTable from clients where 1 = 2
alter table #TempTable add id int IDENTITY PRIMARY KEY
--fill the temporary table with data
INSERT into #TempTable
SELECT *
FROM Clients
SELECT *
FROM #TempTable
where id > 3
--return total # recs available as an output parameter
SELECT @TotalRecs = COUNT(*) FROM Clients
GO
Records are inserted ok into #TempTable and each record is assigned a unique ID (the new field added). My problem is that the SELECT * FROM #TempTable WHERE id > 3 gives me an error "Invalid column name 'id'".
Is this a SQL bug? If I take out the WHERE clause on the SeLECT statement, it shows me the records with their ids.
Thanks!