bepster bepster - 1 month ago 5
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?

Answer

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)
Begin
    Fetch Next From cur Into @c_a, @c_b

    If (@@Fetch_Status <> 0) Break

    Execute my_sp @c_a, @c_b
End
Close cur
Deallocate cur