I'm having a noob problem passing a data table type to a stored procedure.
Here is the implementation :
DECLARE @T TVARIABLE
INSERT INTO @T VALUES ( '#var1','1')
INSERT INTO @T VALUES ( '#var1','1')
INSERT INTO @T VALUES ( '#var2','2')
INSERT INTO @T VALUES ( '#var3','3')
INSERT INTO @T VALUES ( '#var4','4')
INSERT INTO @T VALUES ( '#var5','5')
INSERT INTO @T VALUES ( '#var6','6')
INSERT INTO @T VALUES ( '#var7','7')
SELECT * FROM @T
EXEC SP_NUEVO_VAR @T
Here is the type I have created:
CREATE TYPE [dbo].[TVARIABLE] AS TABLE
(
variable varchar(25),
valor varchar(25)
)
Here is the stored procedure:
CREATE PROCEDURE SP_NUEVO_VAR
@T_Variable TVARIABLE READONLY
AS
BEGIN
BEGIN TRAN SAV_VAR
BEGIN TRY
DECLARE @Output_T TVARIABLE
DECLARE @variable as varchar(50),
@valor as varchar(50),
@folio_terminal as int,
@new_var as int,
@sp as varchar(50),
@LOG AS VARCHAR(Max)
DECLARE VARIABLES CURSOR FOR SELECT * FROM @T_Variable;
SET @sp='SP_NUEVO_VAR'
EXEC SP_LOGDB 'START',@sp
SELECT @folio_terminal = folio_terminal FROM terminal
--WHERE no_serie=@no_serie
SET @LOG='@folio_terminal:['+CAST(@folio_terminal AS VARCHAR(10))+']'
EXEC SP_LOGDB 'START',@sp
OPEN VARIABLES
FETCH NEXT FROM VARIABLES INTO @variable,@valor
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @new_var=NEXT VALUE FOR dbo.seq_variable;
INSERT INTO dbo.variables (id_var, folio_terminal, variable, valor)
VALUES (@new_var, @folio_terminal, @variable, @valor)
FETCH NEXT FROM VARIABLES INTO @variable,@valor
END
COMMIT TRAN SAV_VAR
END TRY
BEGIN CATCH
ROLLBACK TRAN SAV_VAR
END CATCH
END
COMMIT
GO
When I run the implmentation I get this error:
Mens 2766, Nivel 16, Estado 4, Procedimiento SP_NUEVO_VAR, Línea 12
La definición del tipo de datos definido por el usuario 'TVARIABLE' ha cambiado.
The error in English is that the type of data defined by the user 'TVARIABLE' has changed.
When I use this stored procedure from C#, the stored procedure works FINE, but running the procedure in T-SQL is not working.
Best Answer
Sorry, I haven't noticed about something:
I use a script to erase all the database, to start all from zero, this script is useful to test my stored procedures, so in the stored procdure
SP_NUEVO_BD
(in the code below), I drop the stored procedureSP_NUEVO_VAR
, I drop the typeTVariable
, then I create the type, then I create the stored procedure.Something is happening that I cannot run the full script, but when I run it individually it works:
Here is the script below:
Yu can see that the implmentation I refer in the first post is at the end of the script, when I executed indivudually it works fine, but when I run the complete script it doesn´t, considering I make all the process of deleting the stored procedure and the type, then I recreate it.