Error passing variable table type to a stored procedure












3















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.










share|improve this question
















bumped to the homepage by Community 17 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • Nevermind, I misread the code..

    – Dan Guzman
    Feb 20 '17 at 23:08













  • I can read the table when I consume the store Procedure within C# code.

    – user2578332
    Feb 20 '17 at 23:14











  • Try dbo.TVARIABLE. Always use schema reference (it's missing from almost every possible instance in your code): sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/…

    – Aaron Bertrand
    Feb 21 '17 at 0:04


















3















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.










share|improve this question
















bumped to the homepage by Community 17 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • Nevermind, I misread the code..

    – Dan Guzman
    Feb 20 '17 at 23:08













  • I can read the table when I consume the store Procedure within C# code.

    – user2578332
    Feb 20 '17 at 23:14











  • Try dbo.TVARIABLE. Always use schema reference (it's missing from almost every possible instance in your code): sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/…

    – Aaron Bertrand
    Feb 21 '17 at 0:04
















3












3








3








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.










share|improve this question
















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.







sql-server t-sql stored-procedures






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Apr 4 '17 at 8:35









marc_s

7,12053849




7,12053849










asked Feb 20 '17 at 22:39









user2578332user2578332

162




162





bumped to the homepage by Community 17 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 17 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • Nevermind, I misread the code..

    – Dan Guzman
    Feb 20 '17 at 23:08













  • I can read the table when I consume the store Procedure within C# code.

    – user2578332
    Feb 20 '17 at 23:14











  • Try dbo.TVARIABLE. Always use schema reference (it's missing from almost every possible instance in your code): sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/…

    – Aaron Bertrand
    Feb 21 '17 at 0:04





















  • Nevermind, I misread the code..

    – Dan Guzman
    Feb 20 '17 at 23:08













  • I can read the table when I consume the store Procedure within C# code.

    – user2578332
    Feb 20 '17 at 23:14











  • Try dbo.TVARIABLE. Always use schema reference (it's missing from almost every possible instance in your code): sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/…

    – Aaron Bertrand
    Feb 21 '17 at 0:04



















Nevermind, I misread the code..

– Dan Guzman
Feb 20 '17 at 23:08







Nevermind, I misread the code..

– Dan Guzman
Feb 20 '17 at 23:08















I can read the table when I consume the store Procedure within C# code.

– user2578332
Feb 20 '17 at 23:14





I can read the table when I consume the store Procedure within C# code.

– user2578332
Feb 20 '17 at 23:14













Try dbo.TVARIABLE. Always use schema reference (it's missing from almost every possible instance in your code): sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/…

– Aaron Bertrand
Feb 21 '17 at 0:04







Try dbo.TVARIABLE. Always use schema reference (it's missing from almost every possible instance in your code): sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/…

– Aaron Bertrand
Feb 21 '17 at 0:04












1 Answer
1






active

oldest

votes


















0














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:[' +@no_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 @T_Variable=@T


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.






share|improve this answer

























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f164948%2ferror-passing-variable-table-type-to-a-stored-procedure%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    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:[' +@no_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 @T_Variable=@T


    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.






    share|improve this answer






























      0














      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:[' +@no_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 @T_Variable=@T


      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.






      share|improve this answer




























        0












        0








        0







        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:[' +@no_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 @T_Variable=@T


        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.






        share|improve this 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:[' +@no_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 @T_Variable=@T


        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.







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Apr 4 '17 at 8:36









        marc_s

        7,12053849




        7,12053849










        answered Feb 21 '17 at 0:01









        user2578332user2578332

        162




        162






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f164948%2ferror-passing-variable-table-type-to-a-stored-procedure%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh