DECLARE CURSOR

[nguyentaiuyenchi]

Hi all,

I have codes as follows:

SET @strSql='SELECT salaryDate,workingHourID FROM ATC_SALARYSTATUS WHERE staffid='+ ltrim(str(@staffID)) +
' AND SalaryDate >= ''' + CONVERT(varchar,@date_min,101) + ''' AND SalaryDate <=''' + CONVERT(varchar,@date_max,101) + ''''
EXECUTE ('DECLARE curSalary_status CURSOR FOR ' + @strSql)

That mean I want to declare a cursor with select statement that hasnot known before. Sometimes (not always),
I got error:'Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FOR'...

Please show me the way to fix this bug or give me new solution for that case.

Thanks,
UC

Uyen Chi
Software developer

 

[cheyney]

sql doesn't like combining string concatination and execution in the same statement. Try this:


SET @strSql='SELECT salaryDate,workingHourID FROM ATC_SALARYSTATUS WHERE staffid='+ ltrim(str(@staffID)) +
' AND SalaryDate >= ''' + CONVERT(varchar,@date_min,101) + ''' AND SalaryDate <=''' + CONVERT(varchar,@date_max,101) + ''''

SET @strSql = 'DECLARE curSalary_status CURSOR FOR ' + @strSql

EXECUTE (@strSql)


cheyney

 

[swampBoogie]

I don't see that you need dynamic sql.

DECLARE curSalary_status CURSOR FOR
SELECT salaryDate,workingHourID
FROM ATC_SALARYSTATUS
WHERE staffid= ltrim(str(@staffID))
AND SalaryDate >= CONVERT(varchar,@date_min,101)
AND SalaryDate <= CONVERT(varchar,@date_max,101)

In most cases it is possible to get rid of the cursor as well. Depends on what you are doing but using a set based solution often means less and more efficient code.