SQL Server Performance Forum – Threads Archive

using where on a #temp table

Hi, Below is a stored procedure that I am using to create a temp table in order to page through the results within an asp page, as it stands it works fine but the problem is it populates the table with all the records and I only want to retreive values that meet a certain condition, but when I use the where clause it returns no results? so: FROM Employee_cil (works fine)
FROM Employee_cil WHERE Network_ID = @NID(returns no results) is there a way I can amend the SP below so that I only populate the temp table with the revelant records and not all records Thanks for any help
Peter CREATE PROCEDURE dbo.employee_cil_page
(
@Page INT,
@RecsPerPage INT,
@NID VARCHAR(3),
@CID VARCHAR(10),
@EMP VARCHAR(10) )
AS
SET NOCOUNT ON –Create a temporary table
CREATE TABLE #CILTempItems
(
myID INT IDENTITY,
row_id INT,
NETWORK_ID VARCHAR(3),
NODE_ID VARCHAR(5),
CORP_ID VARCHAR(10),
EMP_ID VARCHAR(10),
CIL_ID VARCHAR(20),
CIL_TYPE VARCHAR(1),
CIL_KEY VARCHAR(10),
CIL_DESC VARCHAR(20),
CIL_BILL_FLAG VARCHAR(1),
RENTAL VARCHAR(1),
START_DATE datetime,
END_DATE datetime,
username VARCHAR(50),
action_date datetime,
user_action CHAR(1)
)
— Insert the rows from tblItems into the temp. table
INSERT INTO #CILTempItems
(row_id,NETWORK_ID,NODE_ID,CORP_ID,EMP_ID,CIL_ID,CIL_TYPE,CIL_KEY,CIL_DE
SC,CIL_BILL_FLAG,RENTAL,START_DATE,END_DATE,username,action_date,user_ac
tion) SELECT
row_id,NETWORK_ID,NODE_ID,CORP_ID,EMP_ID,CIL_ID,CIL_TYPE,CIL_KEY,CIL_DES
C,CIL_BILL_FLAG,RENTAL,START_DATE,END_DATE,username,action_date,user_act
ion FROM Employee_CIL
— Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page – 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1) — Now, return the set of paged records, plus, an indiciation of we
— have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #CILTempItems TI
WHERE TI.myID >= @LastRec
)
FROM #CILTempItems
WHERE myID > @FirstRec AND myID < @LastRec
— Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO

---

Please ignore me, I have just realised that I was passing the values to the SP as integers not as strings from the asp page [:I] Having said that I would like to know how to retrieve the total number of records returned from this SP, I am not sure how to bring it into the asp page?

---

You can use select @@rowcount to return the number of rows returned/affected by the last statement. Tom Pullen
DBA, Oxfam GB

---

Thanks Thomas, Would you mind showing me the syntax in relation to my SP, I have tried to add it after my select statement SELECT @totalrows = @@rowcount sql doesn’t throw up an error but I am trying to display the results in the asp as part of the recordset objRS("Totalrows") which throws up an error, I am probably doing this wrong so any pointers would be helpful. Many thanks
Peter

---

You may need to have output parameters specified on your sp. Refer to BOL "stored procedures, parameters" for details. Jon M

---

If I am not mistaken, objRS is your recordset. In this Case, TotalRows must be a field name. However, in your SP, I don’t see any field called TotalRows. Could this be the reason why you are getting error? Do you mean this – objRS("MoreRecords")?

---

You could get the value with objRS("MoreRecords"), but there is a better way to do it without having to COUNT again the number of records. What you should do it get the @@rowcount after inserting the temp table, and asign it to an output variable. CREATE PROCEDURE dbo.employee_cil_page
(
@Page INT,
@RecsPerPage INT,
@NID VARCHAR(3),
@CID VARCHAR(10),
@EMP VARCHAR(10)
@rowcount int = null output
)
SET NOCOUNT ON INSERT INTO #CILTempItems
SELECT…. SELECT @rowcount = @@rowcount SELECT list_of_columns
FROM #CILTempItems
WHERE myID > @FirstRec AND myID < @LastRec Another thing. Consider inserting only row_id + PK of the table, then joining the temp table with your table to get the rows. Depends on number or rows in table, you could also try the table datatype.

---

Thanks bambola, could you show me how I would bring the @rowcount value into an asp page, I am not sure how to do this as it is not part of a recrset, I tried objRS("@rowcount") but that doesnt work an I am not sure how to display an output value Many thanks
Peter

---

you can do it like this. remove the @rowcount from the list of parameters and declare it in the sproc. as a last line add SELECT @rowcount RowCount
From the asp pages after reading the first recordset, move to the next one. it should be
something like set rs = rs.NextRecordset. then read it like rs("RowCount").

---

The problem is to use next recordset I have to clear the old one, but I can’t do this as I need the recordset open in order to page through the results. is there not another way?

---

you can either select first the rowcount asign it into a variable and go to the next recordset, or use getrows() to put the result set into an array and use the array instead of the recordset.
dim rsData
rsData = rs.getrows()

---

Thanks for your help bambola, I have managed to get it to work thanks to your suggestions: I deleclared the following in my SP "@pagenumbers INT = NULL OUTPUT" I then added after my first select statement: "SELECT pagenumbers = COUNT(row_id) FROM #CILTempItems" then in my asp page I reversed the call as you suggested by calling the rowcount first then the main call: pagenum = objRS("pagenumbers") set objRS = objRS.NextRecordset etc.. And it worked fine. Many thanks again for your help
Peter

---

]]>