Debug stored procedure with user defined data type

Question

Is it possible to debug a stored procedure with a user defined data type as a parameter?

Edit: which is the syntax to call it? (exec ....)

My type:

CREATE TYPE [dbo].[FacturaInspeccion] AS TABLE(  
    [sIdServicio] [nvarchar](3) NOT NULL,  
    [nIdTipoInspeccion] [int] NOT NULL,  
    [sIdTipoMotivoInspeccion] [nvarchar](2) NOT NULL,  
    [nIdTipoVehiculo] [int] NOT NULL,  
    [nBase] [real] NOT NULL,  
    [nNoPeriodica] [real] NULL,  
    [nTarifaConProyecto] [real] NULL,  
    [nTarifaSinyecto] [real] NULL,  
    [nTasaTrafico] [real] NULL,  
    [nDescuento] [real] NULL,  
    [nTotal] [float] NULL

)
GO

ch3r1f · Accepted Answer · 2011-04-13 21:04:14Z

6

I realised that it's just a table,so I declared the type, inserted the values and called the stored procedure.

Store procedure declaration:

CREATE PROCEDURE [dbo].[spInsertarFactura]
@tableFacturaInspeccion FacturaInspeccion READONLY,
...

Call to the stored procedure:

USE [DATABASE] GO
DECLARE @return_value int

DECLARE @tablaTmp FacturaInspeccion

INSERT INTO @tablaTmp(sIdServicio,nIdTipoInspeccion,sIdTipoMotivoInspeccion, nIdTipoVehiculo, nBase, nNoPeriodica, nTarifaConProyecto, nTarifaSinyecto, nTasaTrafico, nDescuento, nTotal)
VALUES(79, 1, '00', 1, 2, 2, 2, 2, 2, 10, 100)

EXEC @return_value = [dbo].[spInsertarFactura]

And after that, time to debug.

answered Apr 13, 2011 at 21:04

ch3r1f

2954 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lucero · Accepted Answer · 2011-04-12 16:25:25Z

2

Yes you can debug it normally, but you unfortunately don't get a good view on the data in the TVP.

See the following Microsoft Connect items for more info (and vote if you want to get better support for debugging TVPs ;) ).

answered Apr 12, 2011 at 16:25

Lucero

60.4k9 gold badges127 silver badges154 bronze badges

5 Comments

ch3r1f Over a year ago

And how can you send the data type in the "exec ..." ?

artofsql Over a year ago

If you need to pass a table into a procedure I would suggest looking into passing an XML table to a XML parameter in a procedure.

Lucero Over a year ago

@ch3r1f, by declaring a variable of that type (which will give you a table variable), loading data into it, and then pass the variable - nothing special there...

ch3r1f Over a year ago

@Lucero, yes, nothing especial, take a look to my answer. Thanks anyway :)

Lucero Over a year ago

@ch3r1f, next time maybe do ask the question in a less misleading way: your question really was "how to manually invoke a SP with table-valued parameters" or so - the issue with debugging (that you cannot easily look into TVP data) is what I addressed in my answer.

HLGEM · Accepted Answer · 2011-04-13 21:12:20Z

Let me suggest that you add a debug variable to your input varaibles (with a default of 0) then when you want to test the SP, you set the debug to 1 and you can run queries of the data in test mode. For instnce you could add a line that says

IF @debug = 1 Begin Select * from @tableFacturaInspeccion END

That would allow you see that the data you wnated to have input is what you intended it to be for instnce. Or if you are doing a more complex query with a join as part of ana insert, you could see the select stament in the debuig mode and rollback all inserts while you are figuring out how to get the proc correct. I always include a debug or test variaable in anything complex sp because I will want to be able to look at things at differnt points in time in the proc to debug. And until I know it's right, I want any actions rolled back.

Collectives™ on Stack Overflow

Debug stored procedure with user defined data type

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related