چطور میشه به تک تک رکورد ها در یه دستور Select در SP یا View دسترسی پیدا کرد؟

[nilmil_nil]

با سلام به دوستان
من یه دستور Select دارم که می خوام با تک تک رکورد هاش کار کنم
یعنی میخوام محتوای یک فیلدش رو چک کنم و بعد مثلا یه عمل رو انجام بدم !

[Galawij]

یکی از روش ها استفاده از کرسر(Cursor) هست.

[حمیدرضاصادقیان]

سلام.
یک راه هم استفاده از Recursive CTE هست که در نسخه 2005 به بعد قابل استفاده هست.

[pashna]

Recursive is the process in which the query executes itself. It is usedto get results based on the output of base query. We can use CTE as RecursiveCTE (Common Table Expression).






Here, the result of CTE is repeatedly used to get the final resultset.The following example will explain in detail where I am using AdventureWorksdatabase and try to find hierarchy of Managers and Employees.






USEAdventureWorks




GO




WITHEmp_CTEAS(




SELECTEmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate




FROMHumanResources.Employee




WHEREManagerIDISNULL




UNIONALL




SELECTe.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate




FROMHumanResources.Employee e




INNER JOINEmp_CTE ecteONecte.EmployeeID= e.ManagerID




)




SELECT*




FROMEmp_CTE




GO






In the above example Emp_CTE is a Common Expression Table, the baserecord for the CTE is derived by the first sql query before UNION ALL. Theresult of the query gives you the EmployeeID which don’t have ManagerID.






Second query after UNION ALL is executed repeatedly to get results andit will continue until it returns no rows. For above e.g. Result will haveEmployeeIDs which have ManagerID (ie, EmployeeID of the first result). This is obtained by joining CTE resultwith Employee table on columns EmployeeID of CTE with ManagerID of tableEmployee.






This process is recursive and will continue till there is no ManagerIDwho doesn’t have EmployeeID