quote:

---

I have a dynamic SQL statement executed using EXEC. (It always returns a two-column result set. Each column is of type varchar.)

I want to go through each row in the result set. I can do it by first storing the EXEC result to a table or a temporary table. However, doing so is not acceptable for performance reasons (both involves writing to disk). I tried using a table variable but INSERT does not allow a table variable as source.

Is there a way to go through each row of a result set returned by EXEC without saving the result set to disk? Would it be possible to declare a cursor directly on the result of EXEC? How?

---

CREATE Procedure ocm_persons_list
(
	@process_id		VarChar(10),
	@lock_expiry_time	Int,
	@group_id		Int,
	@number_persons		Int,
	@status_id		Int,
	@retry_limit		Int	=999,
)
As
Begin
	Declare @persons_table	Varchar(50)
	Select @persons_table = 'OCM_Persons_' + Cast(@group_id As VarChar)

        -- Get filtered list of persons based on status.
	-- This EXEC returns a result set. Each row has two varchar columns.

	EXEC ('	Declare @mystatus_id int
		Set @mystatus_id = ' + @status_id + '
		
		If @mystatus_id = 0
		Begin
			Select Top ' + @number_persons + '  
				ocm_person_id, 
				ocm_person_phone
			From  ' +
				@persons_table + '
			Where 
				ocm_status_id = ' + @status_id + ' 
		End
		Else if @mystatus_id = 4 
		Begin
			Select Top ' + @number_persons + '  
				ocm_person_id, 
				ocm_person_phone
			From  ' +
				@persons_table + '
			Where 
				ocm_status_id = ' + @status_id + ' AND
				ocm_calls_outbound_busy - 1 < ' + @retry_limit + '
		End
		Else if @mystatus_id = 3 
                <snip snip snip>
		')

	-- For each person (row in result set), lock it for some time for exclusive use 
        -- by a process.  This step does another filtering on the list of persons.

	EXEC ('	Declare @proc_id		VarChar(10),
			@last_called_datetime	Datetime,
			@rows_changed		Int
		Select 	
			@proc_id = ocm_process_id, 
			@last_called_datetime = ocm_last_called_datetime 
		From  ' +
			@persons_table + ' 
		Where
			ocm_person_id = ''' +  @person_id + '''
	
		-- No process has lock on person
		If @proc_id Is Null
		Begin
			Update ' +
				@persons_table + '
			Set
				ocm_process_id = ' + @process_id + ',
				ocm_last_called_datetime = GETDATE()
			Where
				ocm_person_id = ''' + @person_id + '''
		End

		-- Another process has lock on person for quite some time now
		Else If (DATEDIFF(ms, @last_called_datetime, GETDATE()) > ' + @lock_expiry_time + ')
		Begin
			Update ' +
				@persons_table + '
			Set
				ocm_process_id = ' + @process_id + ',
				ocm_last_called_datetime = GETDATE()
			Where
				ocm_person_id = ''' + @person_id + '''
		End

		-- Given process has lock on person
		Else If @proc_id = ' + @process_id + ' 
		Begin
			<snip snip>
		End

		-- Another process has lock obtained just recently
		Else
		Begin
			<snip snip>
		End')
End
GO

quote:

---

Declare @persons_table Varchar(50)
Select @persons_table = 'OCM_Persons_' + Cast(@group_id As VarChar)

EXEC (' Declare @mystatus_id int
Set @mystatus_id = ' + @status_id + '

If @mystatus_id = 0
Begin
Select Top ' + @number_persons + '
ocm_person_id,
ocm_person_phone
From ' +
@persons_table + '
Where
ocm_status_id = ' + @status_id + '
End
Else if @mystatus_id = 4
Begin
Select Top ' + @number_persons + '
ocm_person_id,
ocm_person_phone
From ' +
@persons_table + '
Where
ocm_status_id = ' + @status_id + ' AND
ocm_calls_outbound_busy - 1 < ' + @retry_limit + '
End
Else if @mystatus_id = 3

---