Dynamic SQL

[luvtitans50]

I am missing something in the below code after the w2Year = @Year in order to include the remaining text:

SET @SQLCommand = Left(@SQLCommand,len(@SQLCommand)-1)
SET @SQLCommand = @SQLCommand + ' FROM work_w2audit w inner join empemployee e on e.employeeid = w.employeeid WHERE w2year = @year '


if (@Error = 'TestA')
SET @SQLCommand = @SQLCommand + '(TestA < -(5.00) or TestA > 5.00)'
else if (@Error = 'TestB')
SET @SQLCommand = @SQLCommand + '(TestB < -(5.00) or TestB > 5.00)'
else if (@Error = 'TestC')
SET @SQLCommand = @SQLCommand + '(TestC < -(5.00) or TestC > 5.00)'
else if (@Error = 'TestD')
SET @SQLCommand = @SQLCommand + '(TestD < -(5.00) or TestD > 5.00)'
else if (@Error = 'TestE')
SET @SQLCommand = @SQLCommand + '(TestE < -(5.00) or TestE > 5.00)'
else if (@Error = 'TestF')
SET @SQLCommand = @SQLCommand + '(TestF < -(5.00) or TestF > 5.00)'
else if (@Error = 'TestG')
SET @SQLCommand = @SQLCommand + '(TestG < -(5.00) or TestG > 5.00)'
else if (@Error = 'TestH')
SET @SQLCommand = @SQLCommand + '(TestH < -(5.00) or TestH > 5.00)'
else if (@Error = 'TestI')
SET @SQLCommand = @SQLCommand + '(TestI < -(5.00) or TestI > 5.00)'
else if (@Error = 'TestJ')
SET @SQLCommand = @SQLCommand + '(TestJ < -(5.00) or TestJ > 5.00)'
else if (@Error = 'TestK')
SET @SQLCommand = @SQLCommand + '(TestK < -(5.00) or TestK > 5.00)'
else if (@Error = 'TestL')
SET @SQLCommand = @SQLCommand + '(TestL < -(5.00) or TestL > 5.00)'
else if (@Error = 'TestM')
SET @SQLCommand = @SQLCommand + '(TestM < -(5.00) or TestM > 5.00)'
else if (@Error = 'TestN')
SET @SQLCommand = @SQLCommand + '(TestN < -(5.00) or TestN > 5.00)'
else if (@Error = 'TestO')
SET @SQLCommand = @SQLCommand + '(TestO < -(5.00) or TestO > 5.00)'
else if (@Error = 'TestP')
SET @SQLCommand = @SQLCommand + '(TestP < -(5.00) or TestP > 5.00)'
else if (@Error = 'TestQ')
SET @SQLCommand = @SQLCommand + '(TestQ < -(5.00) or TestQ > 5.00)'
else if (@Error = 'TestR')
SET @SQLCommand = @SQLCommand + '(TestR < -(5.00) or TestR > 5.00)'
else if (@Error = 'TestS')
SET @SQLCommand = @SQLCommand + '(TestS < -(5.00) or TestS > 5.00)'

EXEC(@SQLCommand)

What syntax do I need?

Thanks,

 

[PWise]

i think you need and

 

[PWise]

you do not need the whole long if

SET @SQLCommand = Left(@SQLCommand,len(@SQLCommand)-1)      SET @SQLCommand = @SQLCommand + ' FROM work_w2audit w inner join empemployee e on e.employeeid = w.employeeid WHERE w2year = @year '
SET @SQLCommand = @SQLCommand + ' And ('+ @error + '< -(5.00) or ' + @error + '> 5.00)'

 

[markros]

pwise is right - you're missing AND before a new condition.

Also, you may do

if @Error in ('TestA', ..., 'TestS')

set @SQLCommand = @SQLCommand + ' AND ([' + @Error + '] < -(5.00) OR [' + @Error + '] > 5.00)'

 

[bborissov]

Also, if you are using direct EXEC(@SQLCommand) you are in trouble, just because the variable @Year will be not visible in the EXEC, so you must use sp_executesql and declare and pass that variable as parameter OR to build the value of that variable directly in your DynamicSQL

SET @SQLCommand = @SQLCommand +
   ' FROM work_w2audit w 
          inner join empemployee e on
                 e.employeeid = w.employeeid
     WHERE w2year = ' + CAST(@year as varchar(10))

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.