Different sql strings for a cursor 1

[cmmrfrds]

I am trying to use a different sql string for my cursor depending on some input parameters. I get a compile error on placing the string after the cursor. Here are my strings.

declare @sql varchar(2000)
set @sql = 'Select employeeID, ' +
'(empLastName + '' '' + empFirstName) as empName ' +
'from Employee '

if @employeeIN > 0 begin
set @sql = 'Select employeeID, ' +
'(empLastName + '' '' + empFirstName) as empName ' +
'from Employee WHERE employeeID = @employeeIN '
end

if @supervisor = 1 begin
set @sql = 'SELECT employeeID, ' +
'(Employee.empLastName + '' '' + Employee.empFirstName) as empName ' +
'From Employee where Employee.employeeID IN ' +
'(Select caseManagerEmpID From EmployeeSupervisors ' +
'inner join Employee ON employeeID = supervisorEmpID ' +
'WHERE (empLoginName = suser_sname()) ) ' +
'ORDER BY empName '
end

DECLARE employee_cursor CURSOR FOR
EXEC (@sql)

Can anybody tell me the correct syntax??

Thank you

 

[tlbroadbent]

Try this.

EXEC ('DECLARE employee_cursor CURSOR FOR ' + @sql)

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cmmrfrds]

Terry, that worked perfectly for 2 of the strings. Where I am using a variable on the second string it says the variable is not declared.

employeeIN is passed as a parameter. It is the only parameter used on any of the strings. I tried declaring another variable and set employeeIN to the new variable and used that in the string but it got the same error. Any ideas?

Thank you,
Jerry

 

[tlbroadbent]

You have to let SQL use the contrents of the variable rather than the literal name of the variable. Move it outside the quotes.

If @EmployeIN is a character data type use the following.

if @employeeIN > 0
begin
set @sql =
'Select employeeID, ' +
'(empLastName + '' '' + empFirstName) as empName ' +
'from Employee WHERE employeeID = ''' + -- 3 single quotes
@employeeIN + '''' -- 4 single quotes
end

If @EmployeIN is a numeric data type use the following.

if @employeeIN > 0
begin
set @sql =
'Select employeeID, ' +
'(empLastName + '' '' + empFirstName) as empName ' +
'from Employee WHERE employeeID = ' + @employeeIN
end Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cmmrfrds]

Thanks Terry that was it.