Imagine we have a stored procedure dbo.MyStoredProcedure that takes @Id as parameter and then does some work.

I have a CSV containing over 1000 rows of different Id's. How do I the best and easiest way execute dbo.MyStoredProcedure for every Id in my CSV file?

I do not particularly want to copy this query:

EXEC dbo.MyStoredProcedure @Id: 75627

for every row in CSV...

CodePudding user response：

You sould ideally work on sets like @larnu alludes to in comments. However if you can't change the proc you're a bit stuck...

If you can import your CSV into a table you can use a CURSOR. Do note the obligatory warning that looping in SQL Server is nearly always not the best solution.

Declare @id INT;

DECLARE IDS CURSOR FOR
SELECT id FROM IdTable;

FETCH NEXT FROM IDS INTO @id;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC dbo.MyStoredProcedure @Id = @id;
    FETCH NEXT FROM IDS INTO @id;
END;

CLOSE IDS;
DEALLOCATE IDS;

Another alternative is to generate an EXEC statement for each ID in a spreadsheet (Excel, Google Sheets, etc).

1. Open your CSV in your favorite spreadhsheet app. Say your data is in Col A and starts in Row 1 (A1)
2. In B1 enter a formula that would generate your EXEC for the id in A1. For Excel that would be ="EXEC dbo.MyStoredProcedure @Id=" & A1
3. Copy B1 down for each row where there is a value in Col A. You now have an EXEC statement in Col B for each of your ids
4. Copy the values from Col B and paste into SSMS