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 ....)
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
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.
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 ;) ).
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.