0

This is my problem. I have a table tbl_archivos with values like this:

Id   desc    namerc
---------------------------
1    arch1   RC201721091701
2    arch2   RC201724091701

I have to pass all the values of the column namerc in my table (above) to a stored procedure like parameter.

Like this :

sp_runproceess_billing 'RC201721091701'

and then the another value RC201724091701.

I am not allowed to use a cursor!

Please help me with this issue.

Thank you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Joseph Marchena
  • 39
  • 6
  • Your google search string is `sq server table value parameters`. – Dan Bracuk Sep 21 '17 at 16:55
  • If sp_runproceess_billing can be refactored as a user function you can call it directly within the select, failing that see [SQL Call Stored Procedure for each Row without using a cursor](https://stackoverflow.com/questions/1656804/sql-call-stored-procedure-for-each-row-without-using-a-cursor) – Alex K. Sep 21 '17 at 16:55

1 Answers1

2

try this solution

DECLARE @t AS TABLE(id INT PRIMARY KEY IDENTITY, namerc VARCHAR(50))
    INSERT INTO @t 
    SELECT DISTINCT namerc FROM tbl_archivos
    ORDER BY tbl_archivos
    DECLARE @index INT = 1
    DECLARE @max INT = (SELECT COUNT(*) FROM @t)
    DECLARE @current_namerc VARCHAR(50)
    WHILE @index <= @max
    BEGIN
       SELECT @current_namerc  = namerc FROM @t WHERE id = @index
       EXEC sp_runproceess_billing @current_namerc
       SET @index = @index + 1
    END
realnumber3012
  • 1,062
  • 6
  • 10
  • 1
    Kind of cheating because you're just replacing a cursor with a while loop which has the same performance impact...but I was going to suggest the same thing. If you're restricted from using cursors because "my boss heard they were bad", then try using a while loop instead. Just don't tell her! – Russell Fox Sep 21 '17 at 17:02