hi all,
m facing below issues
1.Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@result".
- i want to acheive the result based on order by as per the request given by the user.
request will be multiple at time like
brancode asc , brandname desc OR
brandname asc ,brancode desc OR
brandname asc ,brancode asc OR
brandname desc ,brancode desc
declare @column as int=1,
@direction as nvarchar(5)='ASC',
@searchvalue as nvarchar(100) = 'Acer',
@searchrecordcount int,
@sortcolumn nvarchar(100) ,
@query nvarchar(max) ,
@sql nvarchar(max)
declare @result TABLE
(
RowID int not null primary key identity(1,1),
brandname nvarchar(100),
brandcode nvarchar(10)
)
declare @interimresult TABLE
(
RowID int not null primary key identity(1,1),
brandname nvarchar(100),
brandcode nvarchar(10)
)
DECLARE @Order Table
(
col nvarchar(100),
direction nvarchar(100)
)
if(@column=0)
set @sortcolumn='BrandName'
else if(@column=1)
set @sortcolumn='BrandCode'
insert into @Order
select @sortcolumn,@direction
set @query=
N'
SELECT BrandName, BrandCode
FROM MasterBrandList
ORDER BY '+@sortcolumn+' '+@direction+';';
insert into @result
EXEC sp_executesql @query
select * from @result
set @sql = N'select distinct brandname, brandcode from @result
where brandname like ''%'+@searchvalue+'%'' or brandcode like ''%'+@searchvalue+'%''
order by '+@sortcolumn+' '+' '+' '+@direction+';';
insert into @interimresult
EXEC sp_executesql @sql
select * from @interimresult
select @searchrecordcount=COUNT(*) from @interimresult