Click here to Skip to main content
15,065,914 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
create PROCEDURE Sp_MultCompanyProject
@LegalTypeID VARCHAR(200) = NULL,
@CompanyCategoryID VARCHAR(200) = NULL,
@pageno int =1 , -- default page
@pagesize int = 10 -- default 10 items to display
AS
BEGIN
IF( @LegalTypeID = 'null' )
BEGIN
SET @LegalTypeID = NULL;
END;
IF( @CompanyCategoryID = 'null' )
BEGIN
SET @CompanyCategoryID = NULL;
END;
DECLARE @sql NVARCHAR(MAX), @sqlPaging NVARCHAR(MAX);
SET @sql = 'select distinct CompanyID,CompanyName,AliasName from MulitSearch_Portal WHERE 1=1';
IF @LegalTypeID IS NOT NULL
SET @sql = @sql + ' and LegalTypeID IN (' + @LegalTypeID + ')';
IF @CompanyCategoryID IS NOT NULL
SET @sql = @sql + ' and CompanyCategoryID IN (' + @CompanyCategoryID + ')';

exec (' select CompanyID,CompanyName,AliasName from ( SELECT ROW_NUMBER() OVER(ORDER BY CompanyID ) AS Row,* from ( '+@sql+' ) as T ) as pageResult where ( Row between (('+@pageno+' -1)* '+@pagesize+'+1) AND '+@pageno+' * '+@pagesize+' )' );
END;

What I have tried:

MulitSearch_Portal is a views table(views)

My doubt is How to add count of total records in above procedure"Sp_MultCompanyProject "...kindly help
Posted
Updated 6-Jul-16 23:45pm
Comments
Karthik_Mahalingam 7-Jul-16 5:33am
   
you want to return the count of all records based on query?
Thiyagu Arockiasamy 7-Jul-16 5:41am
   
sir i declare but its showing all records, for example
SET @count = 'SELECT COUNT(CompanyID) FROM MulitSearch_Portal

EXEC ( ' select CAST(ROW_NUMBER() OVER(ORDER BY PrjInfoAsOfDate) AS INT) AS Row ,TotalRecords,CompanyID,CompanyName,AliasName,CompanyCategoryID,LegalTypeID,IncorpYear,CompanyCategory,LegalType,
IndstryID,IndstryName,SubIndstryID,SubIndstryName,ProductID,ProductName,ProjectID,ProjectTitle,ProjTypeID,ProjectType,PrjStatusID,ProjectStatus,
EstimateCost,GuesstimateCost,ActualCost,PrjInfoAsOfDate,ProjRegionID,ProjectRegion,PrjStateID,ProjectState,PrjDistrictID,PrjDistrict from ( SELECT ROW_NUMBER() OVER(ORDER BY CompanyID ) AS Row, ('+@count+') AS TotalRecords , * from ( '+@sql+' ) as T ) as pageResult where ( Row between (('+@pageno+' -1)* '+@pagesize+'+1) AND '+@pageno+' * '+@pagesize+' )' );

only 40 records are available in companyId based but display output is Fully records count. i want to specific parameters count values sir

1 solution

add this, this will gives you the count along with the table in two result set.
SQL
exec('select count(*) as TotalCount from ( '+@sql+ ' ) as T')
   
Comments
Thiyagu Arockiasamy 7-Jul-16 5:55am
   
Thanks a lot sir its Working for me
Karthik_Mahalingam 8-Jul-16 2:55am
   
welcome

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900