0
I’m learning to use cursors and I’m trying to run a query I’ve mounted within a variable but it’s giving the following error:
"Message 214, Level 16, Status 2, Procedure sp_executesql, Line 1 Procedure expects Parameter '@statement' of type 'ntext/nchar/nvarchar'."
I’m not getting it. It follows below the code I built.
'''
SET NOCOUNT ON;
DECLARE @COLUNA VARCHAR(100), @COLUNAS VARCHAR(MAX), @CLONAR VARCHAR(MAX), @NovoId int, @Id
varchar(11);
--- Inicializar a variavel
SET @COLUNAS = ''
SET @NovoId = ''
SET @CLONAR = ''
SET @Id = '1234'
--sempre declare que o cursor é do tipo LOCAL FAST_FORWARD.
--Local diz que é o scopo da execuçao e FAST_FORWARD significa que ele so vai pra frente.
DECLARE CURCOLUNAS CURSOR LOCAL FAST_FORWARD FOR SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'MeuBanco'
AND TABLE_SCHEMA= 'dbo'
AND TABLE_NAME = 'MinhaTabela'
ORDER BY ORDINAL_POSITION
OPEN CURCOLUNAS
FETCH NEXT FROM CURCOLUNAS INTO @COLUNA
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COLUNAS = @COLUNAS + ',' + @COLUNA
FETCH NEXT FROM CURCOLUNAS INTO @COLUNA --- O into nesta linha
END
SET @COLUNAS= Right(@COLUNAS, len(@COLUNAS)-1);
SET @CLONAR = N'SELECT '+@COLUNAS+' FROM MinhaTabela WHERE Id = '+@Id;
print @CLONAR;
SET IDENTITY_INSERT [MeuBanco].[dbo].[MinhaTabela] ON
EXECUTE sp_executesql @CLONAR;
SET IDENTITY_INSERT [MeuBanco].[dbo].[MinhaTabela] OFF
--SET @NovoId=SCOPE_IDENTITY()
--PRINT @NovoId
CLOSE CURCOLUNAS;
DEALLOCATE CURCOLUNAS;
'''
Thank you Ernesto, now I will try to play this result inside a variable and copy this result to a new record in the same table.
– Robson Cabral