Using a cursor with dynamic SQL in a stored procedure

by

A cursor will only accept a select statement, so if the SQL really needs to be dynamic make the declare cursor part of the statement you are executing. For the below to work your server will have to be using global cursors.

Declare @UserID varchar(100)
declare @sqlstatement nvarchar(4000)
--move declare cursor into sql to be executed
set @sqlstatement="Declare  users_cursor CURSOR FOR SELECT userId FROM users"

exec sp_executesql @sqlstatement


OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @UserId

WHILE @@FETCH_STATUS = 0
BEGIN
Print @UserID
EXEC asp_DoSomethingStoredProc @UserId

FETCH NEXT FROM users_cursor --have to fetch again within loop
INTO @UserId

END
CLOSE users_cursor
DEALLOCATE users_cursor

If you need to avoid using the global cursors, you could also insert the results of your dynamic SQL into a temporary table, and then use that table to populate your cursor.

Declare @UserID varchar(100)
create table #users (UserID varchar(100))

declare @sqlstatement nvarchar(4000)
set @sqlstatement="Insert into #users (userID) SELECT userId FROM users"
exec(@sqlstatement)

declare users_cursor cursor for Select UserId from #Users
OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @UserId

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC asp_DoSomethingStoredProc @UserId

FETCH NEXT FROM users_cursor
INTO @UserId

END
CLOSE users_cursor
DEALLOCATE users_cursor

drop table #users

More Related Contents:

  1. How to get sp_executesql result into a variable?
  2. T-SQL Dynamic SQL and Temp Tables
  3. Stored procedure hangs seemingly without explanation
  4. T-SQL stored procedure that accepts multiple Id values
  5. How can I use optional parameters in a T-SQL stored procedure?
  6. T-SQL Decimal Division Accuracy
  7. Selecting a Record With MAX Value
  8. SQL Server – How to lock a table until a stored procedure finishes
  9. What is a “batch”, and why is GO used?
  10. TSQL divide by zero encountered despite no columns containing 0
  11. Passing List to SQL Stored Procedure
  12. Dynamic Sorting within SQL Stored Procedures
  13. T-SQL loop over query results
  14. How to insert default values in SQL table?
  15. How to Round a Time in T-SQL
  16. How do I find a stored procedure containing ?
  17. How to convert a “dd/mm/yyyy” string to datetime in SQL Server?
  18. Dynamic SQL to generate column names?
  19. Return a value if no rows are found in Microsoft tSQL
  20. Using Aliases in Where Clause or an Alternative Option?
  21. Can I use the MERGE statement in SQL Server 2005?
  22. T-SQL: How to use parameters in dynamic SQL?
  23. In SQL Server, what does “SET ANSI_NULLS ON” mean?
  24. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  25. Search for one value in any column of any table inside a database
  26. Check if a varchar is a number (T-SQL)
  27. Truncate Datetime to Second (Remove Milliseconds) in T-SQL
  28. Convert unix epoch timestamp to TSQL datetime
  29. Eliminate and reduce overlapping date ranges
  30. Execute a SQL Stored Procedure and process the results [closed]

Leave a Comment