Использование курсора с динамическим SQL в хранимой процедуре

Ответ 1

Курсор будет принимать только оператор select, поэтому, если SQL действительно должен быть динамическим, сделайте декларацию курсора частью инструкции, которую вы выполняете. Чтобы ниже работать, сервер должен будет использовать глобальные курсоры.

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

Если вам нужно избегать использования глобальных курсоров, вы также можете вставить результаты своего динамического SQL во временную таблицу, а затем использовать эту таблицу для заполнения вашего курсора.

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

Ответ 2

Этот код является очень хорошим примером для динамического столбца с курсором, поскольку вы не можете использовать '+' в @STATEMENT:

ALTER PROCEDURE dbo.spTEST
AS
    SET NOCOUNT ON
    DECLARE @query NVARCHAR(4000) = N'' --DATA FILTER
    DECLARE @inputList NVARCHAR(4000) = ''
    DECLARE @field sysname = '' --COLUMN NAME
    DECLARE @my_cur CURSOR
    EXECUTE SP_EXECUTESQL
        N'SET @my_cur = CURSOR FAST_FORWARD FOR
            SELECT
                CASE @field
                    WHEN ''fn'' then fn
                    WHEN ''n_family_name'' then n_family_name
                END
            FROM
                dbo.vCard
            WHERE
                CASE @field
                    WHEN ''fn'' then fn
                    WHEN ''n_family_name'' then n_family_name
                END
                LIKE ''%''[email protected]+''%'';
            OPEN @my_cur;',
        N'@field sysname, @query NVARCHAR(4000), @my_cur CURSOR OUTPUT',
        @field = @field,
        @query = @query,
        @my_cur = @my_cur OUTPUT
    FETCH NEXT FROM @my_cur INTO @inputList
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @inputList
        FETCH NEXT FROM @my_cur INTO @inputList
    END
    RETURN

Ответ 3

Работа с нереляционной базой данных (IDMS любой?) по ODBC-соединению квалифицируется как один из тех случаев, когда курсоры и динамический SQL кажутся единственным маршрутом.

select * from a where a=1 and b in (1,2)

требуется 45 минут для ответа при повторном написании для использования наборов ключей без предложения in в течение менее 1 секунды:

select * from a where (a=1 and b=1)
union all
select * from a where (a=1 and b=2)

Если оператор in для столбца B содержит 1145 строк, использование курсора для создания indidivudal операторов и их выполнение в качестве динамического SQL намного быстрее, чем использование предложения in. Глупый эй?

И да, нет времени в реляционной базе данных, чтобы использовать курсор. Я просто не могу поверить, что столкнулся с экземпляром, где курсорный цикл на несколько порядков быстрее.

Ответ 4

Прежде всего, избегайте использования курсора, если это вообще возможно. Вот некоторые ресурсы для его устранения, когда кажется, что вы не можете обойтись без:

Должно быть 15 способов потерять ваши курсоры... часть 1, Введение

Обработка строк без курсора

Тем не менее, вы, возможно, застряли в одном - я не знаю достаточно от вашего вопроса, чтобы убедиться, что любой из них применим. В этом случае у вас другая проблема - оператор select для вашего курсора должен быть фактическим оператором SELECT, а не оператором EXECUTE. Ты застрял.

Но см. ответ от cmsjr (который пришел, когда я писал) об использовании таблицы temp. Я бы избегал глобальных курсоров даже больше, чем "простые".

Ответ 5

После недавнего перехода с Oracle на SQL Server (предпочтение работодателя) я заметил, что поддержка курсора в SQL Server отстает. Курсоры не всегда злые, иногда требуемые, иногда намного быстрее, а иногда и более чистые, чем попытка настроить сложный запрос путем переустановки или добавления подсказок оптимизации. "Курсоры злы" гораздо более заметны в сообществе SQL Server.

Итак, я думаю, что этот ответ заключается в том, чтобы переключиться на Oracle или дать MS ключ.

• Oracle EXECUTE IMMEDIATE в курсор
• Петля через неявный курсор (цикл for неявно определяет/открывает/закрывает курсор!)

Ответ 6

Есть еще один пример, который я хотел бы поделиться с вами
: D http://www.sommarskog.se/dynamic_sql.html#cursor0

Ответ 7

Другой вариант в SQL Server - выполнить все ваши динамические запросы в табличную переменную в хранимом процессе, а затем использовать курсор для запроса и обработки. Что касается спорных вопросов о курсоре :), я видел исследования, которые показывают, что в некоторых ситуациях курсор может быть быстрее при правильной настройке. Я использую их сам, когда требуемый запрос слишком сложен или просто не по-человечески (для меня;)) возможен.

Ответ 8

этот код может быть вам полезен.

пример использования курсора в SQL Server

DECLARE sampleCursor CURSOR FOR 
      SELECT K.Id FROM TableA K WHERE ....;
OPEN sampleCursor
FETCH NEXT FROM sampleCursor INTO @Id
WHILE @@FETCH_STATUS <> -1
BEGIN

UPDATE TableB
   SET 
      ...