I am attempting to retrieve records based on security I have set in a SQL table.
I have a field named ufilter that contains the filter to be applied to the data set.
In this case, the filter is: schoolnum = '032'
I keep receiving the error 'Incorrect syntax near 'schoolnum'.
I tested the SQL that is generated to the @cmd variable and the correct records are returned.
Any guidance would be most appreciated.
ALTER proc spPrincipalTest
DECLARE @ufilter nvarchar(200)
DECLARE @uexpdate datetime
DECLARE @CMD nvarchar(200)
select @ufilter=ufilter, @uexpdate=uexpdate from UFILTERS where uid=@uid
IF ISNULL(@ufilter, 'ZZTOP') = 'ZZTOP'
select * from dvASTUCurrentShort where 1 = 2
IF @ufilter = 'NONE'
select * from dvASTUCurrentShort order by Lastname, Firstname
set @cmd = 'select * from dvASTUCurrentShort where ' + @ufilter
exec sp_executesql @cmd, @ufilter
select * from dvASTUCurrentShort where schoolnum='032'
Incorrect syntax near 'schoolnum'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[spPrincipalTest].