I have created a stored procedure
I have declared a table
Create PROCEDURE [dbo].[sp_search_report]
(
@start_index int ,
@pagesize int ,
@sort_by varchar(50),
@out_status int out,
@totalcount int out,
@query varchar(2000)
)
AS
SET NOCOUNT ON
declare @query1 varchar(2000);
declare @finalquery nvarchar(2000);
--declare @lastquery nvarchar(2000);
declare @q1 int;
declare @q2 int;
SET @q1 = ((@start_index -1) * @PageSize + 1)
SET @q2 = ((((@start_index -1) * @PageSize + 1) + @PageSize) - 1)
declare @records table
(
rowNumber int not null identity(1,1) primary key,
empid varchar(100),
balance int
)
/* Did some insert here in @records table and select * from @records is fetching results as well. The problem is with the query below: */
SET @query1='select ROW_NUMBER() OVER (ORDER BY a.empname desc) AS rowNumber,
a.empid , a.empname, t.balance
from @records t left outer join tbl_emp a on a.empid =t.empid
where '
SET @finalquery = 'SELECT * FROM ( '+ @query1 + @query + ' ) as t1 WHERE RowNumber BETWEEN ' + cast(@q1 as char)+' AND '+ cast (@q2 as CHAR)
exec (@finalquery)
What I have tried:
The error is
Must declare the table variable "@records".
How can I fix it? Help me out