Perguntas[cursors](dba)

Eu tenho um procedimento armazenado que tem um parâmetro de saída do CURSOR VARYINGtipo. Gostaria de verificar se o cursor de saída pode ser usado pelo código que chamou o procedimento armazenado. Parecia que CURSOR_STATUSera a função certa a ser usada, mas estou obtendo resultados inesperados ao aplicá-la ao meu cursor de saída. A função retorna um valor de -3 dentro do procedimento armazenado que o criou, mas funciona conforme o esperado fora do procedimento armazenado. Veja o código abaixo:

CREATE OR ALTER PROCEDURE dbo.OutputCursorTest  
(@Cursor_OUT CURSOR VARYING OUTPUT)
AS  
BEGIN
    SET NOCOUNT ON;

    SET @Cursor_OUT = CURSOR FORWARD_ONLY STATIC FOR
    SELECT [high]
    from master..spt_values

    OPEN @Cursor_OUT;

    SELECT CURSOR_STATUS('variable', '@Cursor_OUT'); -- this seems to always return -3

    -- possible workaround
    /*
    DECLARE @Cur_Copy CURSOR;
    SET @Cur_Copy =  @Cursor_OUT;
    SELECT CURSOR_STATUS('variable', '@Cur_Copy');
    DEALLOCATE @Cur_Copy;
    */

    RETURN;
END;

GO     

DECLARE @Cur CURSOR;
EXEC dbo.OutputCursorTest @Cursor_OUT = @Cur OUTPUT;
SELECT CURSOR_STATUS('variable', '@Cur'); -- this returns 1 as expected

Estou no SQL Server 2019 CU14 se for importante. Por que CURSOR_STATUSretorna um valor de -3 ("Um cursor com o nome especificado não existe.") dentro do procedimento armazenado?

A consulta a seguir funciona quando executada no console por meio do comando db2. Mas falha ao executar via DBeaver.

BEGIN NOT ATOMIC
  FOR R AS C CURSOR WITH HOLD FOR
    SELECT ROW_NUMBER() OVER() AS INDEX, ID
    FROM A_TABLE a
    WHERE a.ID BETWEEN 1 AND 1000
    ORDER BY a.ID
  DO
    UPDATE A_TABLE SET A_COL = 1 WHERE ID = R.ID;--
    IF MOD(R.INDEX, 100) = 0 THEN
      COMMIT;--
    END IF;--
  END FOR;--
  COMMIT;--
END;

DBeaver dá o seguinte resultado

Erro SQL [42601]: um token inesperado "END-OF-STATEMENT" foi encontrado após "WHERE ID = R.ID". Os tokens esperados podem incluir: "<psm_semicolon>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14

Desejo replicar um conjunto de linhas em uma tabela, mas com manipulação de coluna exclusiva no SQL Server 17. A tabela é:

CREATE TABLE test1 (
    id int CONSTRAINT PK_test1 PRIMARY KEY  CLUSTERED IDENTITY,
    name nvarchar(10) CONSTRAINT UK_test1 UNIQUE NONCLUSTERED,
    value int,
    type varchar(20)
);

O conteúdo da tabela é:

INSERT INTO test1 VALUES ('e_1',1,'A');
INSERT INTO test1 VALUES ('342A',34,'ABC');
INSERT INTO test1 VALUES ('Tame_33',277,'Test10');

Agora desejo replicar essas linhas várias vezes, mas o problema é com a coluna NAME, que é ÚNICA. A solução que fiz até agora é apenas o uso de um cursor, mas isso é ineficiente e estou curioso se isso pode ser feito de maneira mais eficiente?

DECLARE @id int;
DECLARE @name nvarchar(20);
DECLARE @value int;
DECLARE @type varchar(10);
DECLARE @cnt int = 0;stackoverflow
DECLARE @intcnt int = 0;

DECLARE temp_cursor CURSOR FOR
SELECT * FROM test1
OPEN temp_cursor;
FETCH NEXT FROM temp_cursor INTO @id,@name,@value,@type;

WHILE @@FETCH_STATUS = 0
BEGIN
    WHILE @intcnt < 5
    BEGIN
        INSERT INTO test1 VALUES
        ('ins_' + CONVERT(varchar,@intcnt) + '_'  + CONVERT(varchar,@cnt), @value, @type);
        SET @intcnt = @intcnt + 1;
    END 

    SET @intcnt = 0;
    
    FETCH NEXT FROM temp_cursor INTO @id,@name,@value,@type;
    SET @cnt = @cnt + 1;
END

CLOSE temp_cursor;
DEALLOCATE temp_cursor;

Verificou-se que esse erro de código causou um loop infinito na produção:

DECLARE @BatchID INT
DECLARE MyCursor CURSOR FOR
    SELECT BatchID = ...
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @BatchID
WHILE @@FETCH_STATUS = 0
BEGIN
    ...
    IF [condition]
    BEGIN
        ...
        FETCH NEXT FROM MyCursor INTO @BatchID
    END
END
CLOSE MyCursor
DEALLOCATE MyCursor

Supondo que a necessidade de um cursor seja garantida , existe alguma maneira de se proteger contra esse erro (além de mais testes/revisão de código)?

Em outras linguagens, temos FOREACHloops que gerenciam o progresso da iteração para nós, ou FORloops que têm uma reflexão tardia , então existe algum equivalente no SQL Server que evite erros desleixados como extraviar (ou esquecer!)?

Eu nunca vi loops personalizados no SQL precisarem fazer algo sofisticado com o cursor, e WHILEos loops têm o mesmo risco (junto com casos como DELETE #WorkData WHERE ID = @BatchID@BatchID isNULL ), então como esse risco pode ser mitigado programaticamente/limpamente para o caso de uso típico?

Uma abordagem como esta é muito desagradável:

DECLARE @BatchID INT
DECLARE MyCursor CURSOR FOR
    SELECT BatchID = -1--dummy entry to always be skipped
    UNION ALL SELECT BatchID = ...
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @BatchID
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM MyCursor INTO @BatchID
    IF @@FETCH_STATUS = 0
    BEGIN
        ...
    END
END
CLOSE MyCursor
DEALLOCATE MyCursor

Parece-me que verificar @@FETCH_STATUSduas vezes seguidas sem nenhuma instrução de posição/controle do cursor no meio pode ser uma maneira eficaz para o SQL Server adivinhar se houve esse erro, pois minha experiência com cursores nunca viu duas verificações seguidas intencionalmente (sem cursores aninhados pelo menos, mas aqueles ainda têm instruções de controle como OPENe CLOSEentre verificações do @@FETCH_STATUScursor externo).

_{PS O [condition]desta vez foi equivalente a "não é o dia de transição do horário de verão" (que foi neste domingo). Mais erros de fuso horário!}

Estou mais interessado em recursos de linguagem para obter a mesma garantia fornecida automaticamente de como outras linguagens lidam com a cláusula de reflexão tardia de um loop for (por exemplo, o i++infor (int i = 0; i < myArray.Length; i++) ). Quanto menos coisas o indivíduo é responsável, menos lugares existem para cometer um erro, e seria uma tarefa monumental auditar literalmente milhares de procedimentos armazenados em nosso sistema, especialmente porque muitos deles estão iterando dados de forma inadequada em vez de usar lógica baseada em conjunto já.

Estou tentando inserir os 253 principais valores para cada trader de uma tabela de traders temporários na tabela de resultados temporários.

Estou usando o seguinte código para fazer isso.

CREATE TABLE #trad ( Trad varchar(50))    
IF @trad is not null    
INSERT INTO #Trad    
SELECT DISTINCT * FROM dbo.Split(@trad,',') -- split will have traders seperated by commas          
ELSE INSERT INTO #Traders    
Select distinct Trader from TraderMap tm     
where traderorgroup = 'trader'  


CREATE TABLE #result      
(port VARCHAR(50),portdate DATE,P MONEY,V_95 MONEY,      
V_99 MONEY,VLimit MONEY,V_90 MONEY,H_99 MONEY,      
H_975 MONEY,H_95 MONEY,H_90 MONEY,H_80 MONEY,dCnt 
INT,Trad varchar(100))  

DECLARE @traderlist AS Varchar(100)      
DECLARE traderlist CURSOR FOR      
SELECT DISTINCT Traders FROM #traders      
OPEN traderlist      
FETCH next FROM traderlist INTO @traderlist      
WHILE @@FETCH_STATUS = 0      
BEGIN  
INSERT INTO #result (port, portdate, P, V_95, V_99, VLimit, 
dCnt,Trad)      
SELECT  TOP 253 pl.Port,pl.Portdate,pl.dtd_P 
PL,var.V_95,var.V_99, sum(gu.Guid * -1) VLimit,      
Row_number() OVER(partition by pl.TradName ORDER BY Portdate DESC) 
dCnt,pl.TradName      
FROM Trad_PL pl INNER JOIN rep_tab var      
ON pl.Portdate = var.V_Date      
AND pl.Port = var.Port     
AND pl.TradName = var.Trad      
INNER JOIN Trad_Guid gui      
ON pl.Port = gui.Port     
AND pl.TradName = gui.Trad      
WHERE pl.Portdate <= @portdate      
AND pl.Port = CASE WHEN @port = 'Her' THEN 'Herport' ELSE 
@port END      
AND pl.ONA = 'Act'  
AND pl.TradName = @tradlist          
AND var.PType = '0Trad' 
group by  
pl.TradName,pl.Port,pl.Portdate,pl.dtd_P,var.V_95,var.V_99
ORDER BY Portdate DESC
FETCH next FROM tradlist INTO @tradlist      
END      
CLOSE tradlist      
DEALLOCATE tradlist    

Este código é executado por horas. Existe alguma maneira eficiente de obter as 253 principais linhas para cada negociação de Trad_PL e rep_tab.

Estou tentando usar um cursor para limpar tabelas temporárias quando elas não forem mais necessárias. Eu tenho uma pequena tabela que tem os nomes das tabelas temporárias junto com um identificador. O cursor está preso em um loop infinito, mas somente se eu executar certas instruções nele. Se eu apenas imprimir os valores do FETCH, funciona perfeitamente. Aqui está o código.

DECLARE @id bigint;
DECLARE @table_name varchar(max);

DECLARE st CURSOR LOCAL FAST_FORWARD FOR 
SELECT ID, TableName FROM SearchTables WHERE CustomerID IS NULL

OPEN st
FETCH NEXT FROM st INTO @id, @table_name
WHILE @@FETCH_STATUS <> -1 
BEGIN   
    IF(OBJECT_ID(@table_name) IS NOT NULL) 
        EXEC('DROP TABLE ' + @table_name);

    UPDATE SearchTables SET Deleted=1 WHERE ID=@id;

    PRINT CAST(@id AS varchar(max)) + ' ' + @table_name;

    FETCH NEXT FROM st INTO @id, @table_name;
END 

CLOSE st
DEALLOCATE st

Se eu comentar essas linhas

    IF(OBJECT_ID(@table_name) IS NOT NULL) 
        EXEC('DROP TABLE ' + @table_name);

    UPDATE SearchTables SET Deleted=1 WHERE ID=@id;

PRINTgera todos os IDs e nomes de tabela. Se eu não comentá-los, tudo o que recebo é a primeira linha repetidamente até cancelar a consulta. Eu também tentei mudar a IFlinha para, EXEC('DROP TABLE IF EXISTS ' + @table_name)mas isso também não funcionou.

Eu gostaria de saber qual é a substituição geral para um cursor. A implementação geral de um cursor que vejo por aí é

DECLARE @variable INT, @sqlstr NVARCHAR(MAX)

DECLARE cursor_name CURSOR
FOR select_statement --essentially to get an array for @variable 
                     --usually it's a subset of unique ids for accounts, clients, parts, etc

OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @variable
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @sqlstr = N'
     /* some query that uses '+ str(@variable) +' to do dirty work
     such as: go through all our accounts, if it''s some subset (possible new cursor), 
     go through those accounts and connect this way, 
     map those fields and add it to our big uniform table */
     '

     EXEC sp_executesql @sqlstr
FETCH NEXT FROM cursor_name INTO @variable
END

CLOSE cursor_name
DEALLOCATE cursor_name

Como tantas pessoas são anti-cursor (com um aceno para SO: Por que as pessoas odeiam cursores ), qual é a substituição geral para a implementação geral (de preferência SQL Server)?

Eu tento criar uma tabela para armazenar as informações de armazenamento usadas para cada banco de dados.
Como eles estão hospedados em diferentes Clusters/Instâncias, criei uma tabela com todas as Informações de Instâncias/Servidor.
_________________________
|________DBInstance_______|
|____ID___|_Instância_______|
|____1____|Servidor\Instância__|
|____2____|AG-Ouvinte_____|

Em seguida, crio um cursor e envio minha consulta para todos os servidores listados na Instance-Table.

DECLARE @ID nvarchar(2), @Instancename VARCHAR(50), @cmd NVARCHAR(MAX)
DECLARE DBCursor CURSOR FOR 
SELECT ID, Instance 
from DBInstancelist; 

DECLARE @ID nvarchar(2), @Instancename VARCHAR(50), @cmd NVARCHAR(MAX)
DECLARE DBCursor CURSOR FOR 
SELECT ID, Instance 
from DBInstancelist; 

OPEN DBCursor 
FETCH NEXT FROM DBCursor into @id , @Instancename
WHILE @@FETCH_STATUS = 0 
BEGIN
select @cmd = N'INSERT INTO DBSIZE 
select a.*
from OPENROWSET (''SQLNCLI'',''Server=' + @Instancename + ';Trusted_Connection=YES;'',''select 
        [ID] = '+@ID+',
        [Database Name] = DB_NAME(database_id),
        [Type] = CASE WHEN Type_Desc = ''''ROWS'''' THEN ''''Data File''''
                      WHEN Type_Desc = ''''LOG''''  THEN ''''Log File''''
                      ELSE ''''SUMME'''' END,
        [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL ),
        GETDATE() AS Scantime
FROM   sys.master_files
WHERE      database_id > 4
GROUP BY      GROUPING SETS
              (
                     (DB_NAME(database_id), Type_Desc),
                     (DB_NAME(database_id))
              )
ORDER BY      DB_NAME(database_id), Type_Desc DESC'') 
AS a;'
--select @cmd
exec sp_executesql @cmd
--select @cmd
FETCH NEXT FROM DBCursor into @ID, @Instancename
END 
CLOSE DBCursor
DEALLOCATE DBCursor

para as primeiras 4 Instâncias eu recebo a Informação I exceto, para a 5ª Instância entretanto, eu recebo após o Erro.

 "Arithmetic overflow error converting expression to data type int.

Este erro só aparece, se eu adicionar a linha

 "[ID] = '+@ID+',",

mas o ID da instância é necessário para relatórios adicionais.

Por que a conversão está aparecendo e como posso pará-la (Onde está meu erro)?

Estou tentando escrever um cursor. Eu criei uma tabela para capturar o nome de login e a hora de sys.dm_exec_sessions. Agora, preciso escrever um cursor para atualizar o horário de login para o último horário de login se o login já existir e inserir uma linha caso o login não exista na minha tabela. Eu fiz o seguinte, mas infelizmente estou recebendo um erro com a subconsulta retornando mais de um valor para ela. Alguma ideia???

declare @log as varchar(200)
declare @log_time as datetime
declare LoginsSize cursor for
        SELECT  login_name, login_time
        FROM sys.dm_exec_sessions 
open LoginsSize
            fetch next from LoginsSize into @log, @log_time
            while( @@fetch_status = 0)
        begin                                                           
          If (Select Login from [dbo].[LoginsForDBUserList])  = @log 

             Begin
             UPDATE  [dbo].[LoginsForDBUserList]
             SET     LastLoginTime = @log_time
             WHERE   [login]= @log
             END

             Else 

             Begin
             Insert Into [dbo].[LoginsForDBUserList]
             SELECT  login_name, login_time
             FROM sys.dm_exec_sessions
             END

        fetch next from LoginsSize into @log, @log_time
        close LoginsSize
        deallocate LoginsSize

end

Eu tenho duas tabelas assim:
Table Experiment (vou usar apenas um roadID (rID) por enquanto)

rID | latBegin |longBegin |latEnd   |longEnd  
041 | 23.875464|-80.456798|30.549879|-83.5465521  
041 | 33.776563|-81.157526|33.248261|-81.2468425 

Para cada uma das linhas acima, quero que o cursor obtenha o rID, latBegin, longBegin e verifique a distância do lat e do long em
Table ExperimentDetails

rID | lat      |long      |temp |direction|tension    
041 | 33.665632|-81.137952|56.23|L        |54.54      
041 | 23.245632|-80.127952|56.23|L        |23.54  
041 | 23.454555|-80.232456|66.23|L        |13.54  
041 | 23.568763|-80.346586|26.23|L        |63.54  
041 | 23.796545|-80.446586|86.23|L        |83.54   
041 | 23.996545|-80.946586|96.23|L        |22.54   

As etapas para a consulta são:
Etapa 1: Obtenha latBegin e longBegin para um rID específico da Tabela Experimento
Etapa 2: Vá para ExperimentDetails e execute este cálculo para obter a diferença de distância para cada lat e long na tabela Details

Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000  

Etapa 3: obter o valor mínimo para esta nova coluna associada na tabela de detalhes
Etapa 4: obter os valores associados de temperatura, direção e tensão para esse valor mínimo e colocá-lo em uma nova tabela (ainda não concluída> ajuda necessária)
Etapa 5: Obtenha a média de temp, tensão para os 30 pés ou 360 polegadas anteriores do valor mínimo e coloque-o em outra tabela (colunas: rID, latBegin,longBegin,avgTension,avgtemp Ainda não concluído>Ajuda necessária)

Este é o meu código até agora:

      DECLARE @rID int,@latBegin decimal(15,10),@longBegin decimal(15,10),@minV decimal(15,10),@temp decimal(15,10)

    DECLARE MY_CURSOR CURSOR   
      LOCAL STATIC READ_ONLY FORWARD_ONLY  
    FOR   
    SELECT DISTINCT rid,latbeg,longbeg   
    FROM dbo.Experiment  
    WHERE rID='041'  

    OPEN MY_CURSOR  
    FETCH NEXT FROM MY_CURSOR INTO @rID,@latBegin,@longBegin  
    WHILE @@FETCH_STATUS = 0  
    BEGIN   


        SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID Order by rID)  
, @temp=temp

        FROM dbo.ExperimentDetails  
            WHERE rid='041'  

        Insert into dbo.Test(rid,lat,lon,min,temp) values   (@rid,@latBegin,@longBegin,@minV,@temp)  



        FETCH NEXT FROM MY_CURSOR INTO @rID,@latBegin,@longBegin  
    END  

    CLOSE MY_CURSOR  
    DEALLOCATE MY_CURSOR

A tabela de teste fica assim:

rID|lat      |long      |min |temp  
041|23.875464|-80.456798|12.1|96.23
041|33.776563|-81.157526|11.0|96.23

Os dados temporários são repetidos para a última linha, o que não está correto

Deve ficar assim:

rID|lat      |long      |min |temp  
041|23.875464|-80.456798|12.1|83.54
041|33.776563|-81.157526|11.0|54.54

Eu dei o máximo de informações que pude, mas se você precisar de mais, pergunte, agradeço qualquer ajuda, estou batendo minha cabeça nisso desde ontem. Atualmente estou tentando trazer temp, direção e tensão além de @minV, mas não me deixa fazer isso

Para a Etapa 5: O cálculo no cursor se parece com isso em teoria para a tabela Detalhes:

rID |lat       |long      |temp |DISTDIFF  
041 | 23.245632|-80.127952|56.23|372.12    
041 | 23.454555|-80.232456|66.23|300.22   
041 | 23.568763|-80.346586|26.23|50.48   
041 | 23.796545|-80.446586|86.23|12.10

Então ao inserir eu verifico o valor mínimo ou seja 12,10 e insiro os valores associados a essa linha.
Para a etapa 5, preciso encontrar o valor mais próximo de 360 ​​ANTES do valor mínimo não depois (pode haver outros valores mais próximos depois), que nesse caso é 372,12 e depois tirar a média de temp assim:

rID |minlat    |minlong   |lastlat  |lastlong  |Avgtemp  
041 | 23.796545|-80.446586|23.245632|-80.127952|58.73 

Usando Sql Server 2014