Hello there!
I would like to retrieve a recordset but for every row I need to pass a new argument that is the return value in a specific table in the DB.
The only way is to assign the retrieved value to a variable and then continue the sql query with parameter this variable.
The problem is that I can not assign to that variable each time a new value (during the select execution). Any ways to achieve that?!

Best,
Theodore.

Sorry, I am not sure what you are trying to achieve. Can you explain it a bit clearer? May be with some examples?

_________________________
-Vijay G
Strive for Perfection

Hi again!
Thanks for the interest but I found the solution!
I re-state the problem:
Suppose you have the following table but the COUNTRY and WRITER can be retrieved only by ID (this is going to be the variable I will describe later)
ID COUNTRY WRITER
1 Greece
1 Kostas
2 Ireland
2 Fiona

Solution:

-- drop table in case it exists
drop table movie_table
-- create table
CREATE TABLE movie_table
(
Country varchar(200),
Writer varchar(200),
)


-- Declare the variables to store the values returned by FETCH.
DECLARE @system_name_variable varchar(50)

DECLARE authors_cursor CURSOR FOR
SELECT id
FROM table_1

OPEN authors_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

    FETCH NEXT FROM authors_cursor
    INTO @system_name_variable

     -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
     WHILE @@FETCH_STATUS = 0
     BEGIN
       -- Concatenate and display the current values in the variables.
      insert into movie_table
      select
             Country = (select country
                           from table_1 where id =@system_name_variable),
             Writer = (select writer
                           from table_1 where id=@system_name_variable),
      FROM table_1

        -- This is executed as long as the previous fetch succeeds.
        FETCH NEXT FROM authors_cursor
        INTO @system_name_variable
     END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

select * from movie_table
go
--------------------------------------------------------------

Hope this helps guys!
Regards,
Theodore.