Dawood Abbas

Dawood Abbas

  • 1.9k
  • 264
  • 86.1k

How to Search Data Col wise with paging in str proc?

Mar 14 2015 4:00 AM
alter PROCEDURE sp_Get_CustInfoSerach2
(@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
,@ColumnName VARCHAR(50)=null
,@Value VARCHAR(50)=null
,@ddlValue VARCHAR(50)=null
,@txtValue VARCHAR(50)=null
,@status varchar(30))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cmd AS NVARCHAR(max)
if @txtValue IS NULL
Begin
SET @Value = ''''+@ddlValue+ ''''
End
else if @ddlValue IS NULL
begin
SET @Value = ''''+@txtValue+ ''''
end
SET @cmd = 'SELECT ROW_NUMBER() OVER
(
ORDER BY C_Register.UserId desc
)AS RowNumber
,C_Register.UserId, C_Register.Name, C_Register.UserName, C_Register.Status,
Packages.PackagePeriod, Packages.PackageName, C_Register.ActivationDate,
Receive_Payment.OldExpiryDate, Receive_Payment.Balance, Receive_Payment.PyingAmount,
Receive_Payment.LastPaidDate, C_Register.LastUpdateTime,
Area.AreaName, C_Register.MobNo, Employee.EmpName, C_Register.Address,C_Register.CreatedDate
INTO'+ #Results+'
FROM C_Register INNER JOIN Receive_Payment ON C_Register.UserId = Receive_Payment.UserId
INNER JOIN Area ON C_Register.AreaId = Area.AreaId
INNER JOIN Employee ON Receive_Payment.EmpId = Employee.EmpId
INNER JOIN Packages ON Receive_Payment.PackageId = Packages.PackageId
where C_Register.AccountExpiry= Receive_Payment.OldExpiryDate And C_Register.Status = '+@status+'And
' + @ColumnName + ' = ' + @Value
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
EXEC(@cmd)
END
 
 
 
 
Thrwoing error :
Msg 207, Level 16, State 1, Procedure sp_Get_CustInfoSerach2, Line 33
Invalid column name '#Results'.
 
How to solve it?