Loop Through Table Rows and Call Stored Procedure on Every Row

Loop through all the rows of a temp table and call a stored procedure for each row

you could use a cursor:

DECLARE @id int
DECLARE @pass varchar(100)

DECLARE cur CURSOR FOR SELECT Id, Password FROM @temp
OPEN cur

FETCH NEXT FROM cur INTO @id, @pass

WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC mysp @id, @pass ... -- call your sp here
    FETCH NEXT FROM cur INTO @id, @pass
END

CLOSE cur    
DEALLOCATE cur

storing multi row stored procedure data to loop through rows later in c#

assuming that yor conf is an instance of Conf class, you just have to create a List to store data:

public List<Conf> GetData()
{
.....your code

            var list = new List<Conf>();
            SqlDataReader dr = command.ExecuteReader();
            while (dr.Read())
            {
               var conf=new Conf();
                conf.T_id = dr["t_id"].ToString();
                conf.Cm_firstname = dr["cm_firstname"].ToString();
                conf.Cm_lastname = dr["cm_lastname"].ToString();
                conf.Cm_userid = Convert.ToInt32(dr["cm_userid"]);
                conf.AgeOfNotification = dr["AGEOFNOTIFICATION"].ToString();
                conf.DelegateSystem = dr["DELEGATESYSTEM"].ToString();
                conf.P_Id = Convert.ToInt32(dr["P_id"]);
               list.Add(conf);
            }
            dr.Close();
......
return list;  // for the future use
}

you can use it like this

var list=GetData();

Executing a stored procedure on every row in a SQL Server Table

Did you ever try FUNCTION, you can calculate the work days in between two days in your FUNCTION.

SELECT *, dbo.fn_WorkDaysLate (startDate, endDate) AS DaysLate FROM dbo.vQualityControl
--OR
UPDATE A
    SET A.DaysLate = dbo.fn_WorkDaysLate (A.startDate, A.endDate)
FROM dbo.vQualityControl A

---

Related Topics