SQLTeam.com | Weblogs | Forums

Order by dynamic

hi all,

m facing below issues
1.Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@result".

  1. 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

The error is due to @result not being defined in, or passed into, the second dynamic sql string.

I cannot make out what you are trying to do but suspect you do not really want to start defining table types.

@result holds all the records from MasterBrandList, so why not just replace @result with MasterBrandList in dynamic part. The Like in the dynamic SQL will cause table scans as well. Lastly, why is searchvalue being applied to Brandname and BrandCode? Either they know the name (likely) or the code (hopefully internal PK and not likely), but to scan both is probably not a good idea.