February 1, 2012 at 2:16 am
I need to get the total row count for a specific subquery; however, I cannot get access to the subquerry as variable
This procedure executes fine I just need the total row count for that specific sub query.
alter PROCEDURE [dbo].[uspUtbProfessionals_Paging]
@startRowIndex int,
@pageSize int,
@totalCount int out
AS
BEGIN
SET NOCOUNT ON;
SET @startRowIndex = @startRowIndex + 1
Begin
SELECT * FROM (
Select *,
ROW_NUMBER() OVER (ORDER BY ProfessionalID ASC) as RowNum
FROM (
SELECT bp.ProfessionalID,bp.UserID,bp.RoleID,
bp.ProfessionalName, bp.ProfessionalFlags, bp.LogoFile,
bp.Url, bp.ShortDescr, bp.ModifiedDate,
pZips.ZipCode, pZips.ZipCodeID, Person.utbStates.State, Person.utbCitys.City,
bpDesc.ProfessionalDescrFlags, pAddres.AddressLine,
(
[dbo].fn_GetProfessionalCategorys(bp.ProfessionalID)
)as [OtherCategories]
FROM BuildingPros.utbProfessionals bp WITH (NOLOCK) INNER JOIN
BuildingPros.utbProfessionalsUtbAddresses bpAddres ON
bp.ProfessionalID =bpAddres.ProfessionalID INNER JOIN
Person.utbAddresses pAddres WITH (NOLOCK) ON bpAddres.AddressID = pAddres.AddressID AND
bpAddres.AddressID = pAddres.AddressID INNER JOIN
Person.utbZipCodes pZips WITH (NOLOCK) ON pAddres.ZipCodeID = pZips.ZipCodeID AND
pAddres.ZipCodeID = pZips.ZipCodeID AND pAddres.ZipCodeID = pZips.ZipCodeID INNER JOIN
Person.utbStates WITH (NOLOCK) ON pZips.StateCD = Person.utbStates.StateCD INNER JOIN
BuildingPros.utbProfessionalsDescr bpDesc WITH (NOLOCK) ON
bp.ProfessionalID = bpDesc.ProfessionalID INNER JOIN
Person.utbCitys ON pZips.CityID = Person.utbCitys.CityID
)as xBuildingPros)as BuildingProsFinal
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize)- 1
ORDER BY ProfessionalID ASC
SELECT @totalCount = COUNT(ProfessionalID) FROM xBuildingPros
How can i get the @totalCount in the TSql ??
END
END
SET NOCOUNT OFF;
Dam again!
February 1, 2012 at 2:58 am
Formatted for my sanity
ALTER PROCEDURE [dbo].[uspUtbProfessionals_Paging] @startRowIndex INT, @pageSize INT, @totalCount INT out
AS
BEGIN
SET NOCOUNT ON;
SET @startRowIndex = @startRowIndex + 1
BEGIN
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY ProfessionalID ASC) AS RowNum
FROM (
SELECT bp.ProfessionalID, bp.UserID, bp.RoleID, bp.ProfessionalName, bp.ProfessionalFlags,
bp.LogoFile, bp.Url, bp.ShortDescr, bp.ModifiedDate, pZips.ZipCode, pZips.ZipCodeID,
Person.utbStates.STATE, Person.utbCitys.City, bpDesc.ProfessionalDescrFlags, pAddres.AddressLine,
([dbo].fn_GetProfessionalCategorys(bp.ProfessionalID)) AS [OtherCategories]
FROM BuildingPros.utbProfessionals bp WITH (NOLOCK)
INNER JOIN BuildingPros.utbProfessionalsUtbAddresses bpAddres ON bp.ProfessionalID = bpAddres.ProfessionalID
INNER JOIN Person.utbAddresses pAddres WITH (NOLOCK) ON bpAddres.AddressID = pAddres.AddressID
AND bpAddres.AddressID = pAddres.AddressID
INNER JOIN Person.utbZipCodes pZips WITH (NOLOCK) ON pAddres.ZipCodeID = pZips.ZipCodeID
AND pAddres.ZipCodeID = pZips.ZipCodeID
AND pAddres.ZipCodeID = pZips.ZipCodeID
INNER JOIN Person.utbStates WITH (NOLOCK) ON pZips.StateCD = Person.utbStates.StateCD
INNER JOIN BuildingPros.utbProfessionalsDescr bpDesc WITH (NOLOCK) ON bp.ProfessionalID = bpDesc.ProfessionalID
INNER JOIN Person.utbCitys ON pZips.CityID = Person.utbCitys.CityID
) AS xBuildingPros
) AS BuildingProsFinal
WHERE RowNum BETWEEN @startRowIndex
AND (@startRowIndex + @pageSize) - 1
ORDER BY ProfessionalID ASC
END
END
SET NOCOUNT OFF;
OK, what you're asking for is essentially assigning a variable whilst performing a data-retrieval which AFAIK is not possible.
You could return the total count of the set in the result by doing this: -
ALTER PROCEDURE [dbo].[uspUtbProfessionals_Paging] @startRowIndex INT, @pageSize INT, @totalCount INT out
AS
BEGIN
SET NOCOUNT ON;
SET @startRowIndex = @startRowIndex + 1
BEGIN
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY ProfessionalID ASC) AS RowNum, COUNT(*) OVER() AS subQueryCount
FROM (
SELECT bp.ProfessionalID, bp.UserID, bp.RoleID, bp.ProfessionalName, bp.ProfessionalFlags,
bp.LogoFile, bp.Url, bp.ShortDescr, bp.ModifiedDate, pZips.ZipCode, pZips.ZipCodeID,
Person.utbStates.STATE, Person.utbCitys.City, bpDesc.ProfessionalDescrFlags, pAddres.AddressLine,
([dbo].fn_GetProfessionalCategorys(bp.ProfessionalID)) AS [OtherCategories]
FROM BuildingPros.utbProfessionals bp WITH (NOLOCK)
INNER JOIN BuildingPros.utbProfessionalsUtbAddresses bpAddres ON bp.ProfessionalID = bpAddres.ProfessionalID
INNER JOIN Person.utbAddresses pAddres WITH (NOLOCK) ON bpAddres.AddressID = pAddres.AddressID
AND bpAddres.AddressID = pAddres.AddressID
INNER JOIN Person.utbZipCodes pZips WITH (NOLOCK) ON pAddres.ZipCodeID = pZips.ZipCodeID
AND pAddres.ZipCodeID = pZips.ZipCodeID
AND pAddres.ZipCodeID = pZips.ZipCodeID
INNER JOIN Person.utbStates WITH (NOLOCK) ON pZips.StateCD = Person.utbStates.StateCD
INNER JOIN BuildingPros.utbProfessionalsDescr bpDesc WITH (NOLOCK) ON bp.ProfessionalID = bpDesc.ProfessionalID
INNER JOIN Person.utbCitys ON pZips.CityID = Person.utbCitys.CityID
) AS xBuildingPros
) AS BuildingProsFinal
WHERE RowNum BETWEEN @startRowIndex
AND (@startRowIndex + @pageSize) - 1
ORDER BY ProfessionalID ASC
END
END
SET NOCOUNT OFF;
Or you could insert your subquery into a temporary result-set, then get your results from there.
ALTER PROCEDURE [dbo].[uspUtbProfessionals_Paging] @startRowIndex INT, @pageSize INT, @totalCount INT out
AS
BEGIN
SET NOCOUNT ON;
SET @startRowIndex = @startRowIndex + 1
BEGIN
IF object_id('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END
SELECT *, ROW_NUMBER() OVER (ORDER BY ProfessionalID ASC) AS RowNum
INTO #temp
FROM (
SELECT bp.ProfessionalID, bp.UserID, bp.RoleID, bp.ProfessionalName, bp.ProfessionalFlags,
bp.LogoFile, bp.Url, bp.ShortDescr, bp.ModifiedDate, pZips.ZipCode, pZips.ZipCodeID,
Person.utbStates.STATE, Person.utbCitys.City, bpDesc.ProfessionalDescrFlags, pAddres.AddressLine,
([dbo].fn_GetProfessionalCategorys(bp.ProfessionalID)) AS [OtherCategories]
FROM BuildingPros.utbProfessionals bp WITH (NOLOCK)
INNER JOIN BuildingPros.utbProfessionalsUtbAddresses bpAddres ON bp.ProfessionalID = bpAddres.ProfessionalID
INNER JOIN Person.utbAddresses pAddres WITH (NOLOCK) ON bpAddres.AddressID = pAddres.AddressID
AND bpAddres.AddressID = pAddres.AddressID
INNER JOIN Person.utbZipCodes pZips WITH (NOLOCK) ON pAddres.ZipCodeID = pZips.ZipCodeID
AND pAddres.ZipCodeID = pZips.ZipCodeID
AND pAddres.ZipCodeID = pZips.ZipCodeID
INNER JOIN Person.utbStates WITH (NOLOCK) ON pZips.StateCD = Person.utbStates.StateCD
INNER JOIN BuildingPros.utbProfessionalsDescr bpDesc WITH (NOLOCK) ON bp.ProfessionalID = bpDesc.ProfessionalID
INNER JOIN Person.utbCitys ON pZips.CityID = Person.utbCitys.CityID
) AS xBuildingPros
SELECT @totalCount = COUNT(*)
FROM #temp
SELECT *
FROM #temp
WHERE RowNum BETWEEN @startRowIndex
AND (@startRowIndex + @pageSize) - 1
ORDER BY ProfessionalID ASC
END
END
SET NOCOUNT OFF;
February 1, 2012 at 4:15 pm
Thank you very much for taking the time to answer my question with the code ready to go!
I am very grateful!
Erik
Dam again!
February 6, 2012 at 6:17 am
try below code
DECLARE @Tmp TABLE (Id int, RowNum int, TotalRows int);
WITH SelectedItems AS
(SELECT Id, Row1, Row2, ROW_NUMBER() OVER (ORDER BY Row1) AS Position,
COUNT(*) OVER () AS TotalRows
FROM Items
WHERE Row2 = @Row2)
INSERT @Tmp
SELECT Id, Row1, Row2
FROM SelectedItems
WHERE Position BETWEEN @From AND @To
SELECT TOP 1 @TotalRows = TotalRows FROM @Tmp
SELECT * FROM @Tmp
http://stackoverflow.com/questions/581440/select-count-of-subquery-without-running-it-twice
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply