0

Goal: Call the existing stored procedure many times with different sets of values, e.g. Parameter 1 {100000, 100001, 100002....}, ....

dbo.ExistingStoredProc = existing stored procedure that takes 2 parameters {int, varchar(20)}

I use table valued parameters for this task. However, it's a real mess to modify the existing stored procedure to replace the two parameters with one table valued parameter. So I thought about creating another stored procedure with table valued parameter then call the existing stored procedure. Is this possible?

dbo.CallingStoredProc = stored procedure with table valued parameter and calls dbo.ExistingStoredProc

dbo.CallingStoredProc (simplified to 1 parameter)

@TvpParameter tvpMyType READONLY

Declare @sn int

Select @sn = SerialNumber from @TvpParameter

EXEC dbo.ExistingStoredProc @sn

Modified script that executes dbo.CallingStoredProc

DECLARE @TvpParameter AS dbo.tvpMyType

INSERT INTO @TvpParameter (SerialNumber)    VALUES (123456), (654321)

DECLARE @return_value int

EXEC    @return_value = dbo.CallingStoredProc           
@tvpExistingPar = @TvpParameter

SELECT  'Return Value' = @return_value


RESULT
I only get the result for the last item in TVP, which is SerialNumber = 654321.

Tajinder
  • 2,248
  • 4
  • 33
  • 54
icernos
  • 395
  • 3
  • 6
  • 1
    Using a cursor would make sense for what you want to achieve. – ZLK Aug 07 '19 at 00:34
  • Using `TVP` they way you are using, does not have any advantage at all. It is not any better than `while loop` or `cursor` approach. You should modify `ExistingStoredProc` to handle `TVP` – Squirrel Aug 07 '19 at 01:36
  • Is it possible? Yes. You may struggle to identify "last item" - but that is a very different question. – SMor Aug 07 '19 at 13:03
  • I have two choices to achieve my goal. 1) Write an "adapter" stored procedure that calls the existing stored procedure 2) Modify ExistingStoredProc to handle TVP per @Squirrel's suggestion. I went with option #2 because option #1 is only viable if it is trivial. Since I've already struggled with option #1 (@SMor) and can't easily see how cursor can help make CallingStoredProc work (@ZLK) then I went ahead and rewrote ExistingStoredProc. Problem solved! I tried to avoid the painful rewrite but couldn't. Thanks for the input fellas! – icernos Aug 08 '19 at 23:09

0 Answers0