AnswerBun.com

Error passing variable table type to a stored procedure

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 [email protected]_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.

Database Administrators Asked by user2578332 on January 1, 2021

1 Answers

One 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 procedure SP_NUEVO_VAR, I drop the type TVariable, 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:

USE foxtrot
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
-- Nueva Base de datos
SET NOCOUNT ON
EXEC SP_NUEVO_DB 

-- Script de ejemplo, variables de ejemplo 

DECLARE @no_serie as varchar (20),
        @folio_terminal as int,
        @err_cod as int,
        @folio_solicitud as int
        SET @no_serie='282-716-200'

-- Tabla de ejemplos de nuevas variables

-- Nuevo Registro de terminal ùnica

EXEC SP_NUEVO_TERMINAL   @no_serie, @folio_terminal OUT, @err_cod OUT

PRINT 'No de serie:['   [email protected]_serie+']' PRINT 'Folio terminal:['+CAST(@folio_terminal as varchar(20))+ ']' PRINT '@err_cod:['+CAST(@err_cod as varchar(20))+']'

-- Nuevo Registro de terminal, con sistema operativo, modelo  

EXEC SP_NUEVO_REGISTRO_TERMINAL 'T00001','282-716-200','QTG5001','EOS123','VX610', @err_cod OUTPUT

PRINT 'err_cod:['+CAST(@err_cod as varchar(20))+']'

-- Script de nuevas variables 

DECLARE @T TVARIABLE 
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   @[email protected]

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.

Answered by user2578332 on January 1, 2021

Add your own answers!

Related Questions

Backup Functions in PgAdmin 4 Version 1.6

1  Asked on November 19, 2021 by m-karthika

 

Can not connect to SQL Server from Ubuntu sqlcmd client

1  Asked on November 17, 2021 by carlosrpevertsz

       

Insufficient privilege

1  Asked on November 16, 2021 by vishwanath-gowda-k

     

How do I find the next saturday in MySQL

5  Asked on November 14, 2021 by who-cares

         

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved.