bepster bepster - 4 months ago 21
SQL Question

T SQL - Passing table variables through a stored procedure

Let's say I have a stored procedure:

exec my_sp ?,?

I can execute this stored procedure by adding two values e.g.

exec my_sp 'value 1','value 2'

Now this is where things get tricky.....

I have a table for example

1 | 2
aa bb
cc dd

and so on.......

Now I want to pass my table values through and create one table of results e.g.

exec my_sp 'aa','bb'

exec my_sp 'cc','dd'

and so on..........

How can I achieve this?


The easiest way to do this (unfortunately) is via a CURSOR. I renamed the columns you had in the question from 1 and 2 to A and B for sanity's sake.

Declare @c_a varchar (10),
        @c_b varchar (10)

Declare cur Cursor For
    Select  A, B
    From    YourTable

Open cur
While (1 = 1)
    Fetch Next From cur Into @c_a, @c_b

    If (@@Fetch_Status <> 0) Break

    Execute my_sp @c_a, @c_b
Close cur
Deallocate cur