Run Stored Procedure with table data as parameter

I have number of ids in id_table and I need to run this procedure for at least how many rows in table1. I am using while loop to run loop till counts finish in table1 but can anyone tell me how can I change @ID every time.

If anyone can tell me how to do in c# will also be fine.

  • SQL bigint hash to match c# int64 hash
  • Could not load file or assembly 'VSLangProj80'
  • Naming standard for INCLUDE indexes?
  • SQL Server - Count events that happen from 15 min to 14 days from base time
  • Difference between NULL in SQL and null in programming languages
  • Get data from two tables using by data from another table in SQL Database
  • declare @ID INT
    declare @noRun1 INT
    declare @howTime INT
    set @noRun1=1
    set @howTime = (select count(*) from table1)
    set @ID =(select top 1 id from id_table)
    while (@noRun1<=@howTime)
        EXEC proc_run @ID
    set @noRun1=@noRun1+1

  • SQL import from openrowset mixed type correctly
  • Why are table valued parameters to SQL Server stored procedures required to be input READONLY?
  • How to assign an exec result to a sql variable?
  • How do NOT deploy a SQL Server Database project when debugging?
  • Mutiplication aggregate operator in SQL
  • INSERT INTO SELECT strange order using UNION
  • 2 Solutions collect form web for “Run Stored Procedure with table data as parameter”

    try this

        DECLARE @uniqueId int
    DECLARE @TEMP TABLE (uniqueId int)
    -- Insert into the temporary table a list of the records to be updated
    INSERT INTO @TEMP (uniqueId) SELECT uniqueId FROM myTable
    -- Start looping through the records
    -- Grab the first record out
    SELECT Top 1 @uniqueId = uniqueId FROM @TEMP
    PRINT 'Working on @uniqueId = ' + CAST(@uniqueId as varchar(100))
    -- Perform some update on the record
    EXEC proc_run @uniqueId
    -- Drop the record so we can move onto the next one
    DELETE FROM @TEMP WHERE uniqueId = @uniqueId

    So you want to execute a stored procedure for each id in your table?
    Rewrite your selection of id so that you can skip a number of rows. Something like this:

    while (@noRun1 <= @howTime)
        select @ID = id from
            (select id, (ROW_NUMBER() over (order by id)) as numrow from id_table) as tab
        where numrow = @noRun1
        EXEC proc_run @ID
        set @noRun1 = @noRun1 + 1

    If you are using SQL Server 2008+ you can rewrite your stored procedure to accept table-valued parameters, pass the whole list of ids and only execute once. Check out this example:

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.