>>>Hi All,
>>>
>>>I work with Visual Foxpro. I have to use Dynamic Where Clause in my SQL Server queries.
>>>
>>>I found two options on the threads :
>>>1) SELECT * FROM Table WHERE (SELECT CASE ...) = (SELECT CASE ...)
>>>2) DECLARE @SqlStatement varchar(4000)
>>> SET @SqlStatement = 'Sql Statement'
>>> EXEC (@SqlStatement)
>>>
>>>I'd like to know which option offer better performance.
>>>Or is there other options ?
>>>
>>>Thanks for your help.
>>
>>They are not equal, but 2 is the best, sure.
>
>Hi Fabio,
>
>You say that they are not equal, about results or performance ?
>
>Thanks for your answers
Both
The second it is more performante ( and SQL can optimize it best),
but the execution happens in a different scope.
For example if you uses the variable @VARIABILE,
you cannot be used it directly into the @SqlStatement string.
DECLARE @SqlStatement varchar(4000),@VARIABILE ...
SELECT @VARIABILE=....
SET @SqlStatement = 'Sql ...@VARIABILE ....Statement'
EXEC (@SqlStatement) <== THIS FAIL
You can think @VARIABILE like a VFP Local variable.
SQL haven't PUBLIC or PRIVATE variables.