Construct a dynamic SQL statement to obtain the return value from the cursor.
Declare @ tablename varchar (200)
Set @ tablename = 'tmpbasecustomer _ 9'
declare @ SQL nvarchar (4000)
set @ SQL = n'
declare @ repeatstr varchar (1000)
declare @ clname varchar (200)
declare @ total int
set @ repeatstr = '''
declare curcustomer cursor for
select name, count (name) as total from '+ quotename (@ tablename) + 'group by name having (count (*)> 1
open curcustomer
fetch next from curcustomer into @ clname, @ total
while @ fetch_status = 0
begin
If (@ total> 1)
begin
set @ repeatstr = @ repeatstr + '','' + @ clname + ''has ['' + Cast (@ total as varchar) + ''] line''
end
fetch next from curcustomer into @ clname, @ total
end
close curcustomer
deallocate curcustomer
select @ nrepeatstr = @ repeatstr
'
declare @ nrepeatstr varchar (1000)
Exec sp_executesql @ SQL,
N' @ nrepeatstr varchar (1000) out ',
@ Nrepeatstr out
Select @ nrepeatstr