SQLServerCentral.com / SQL Server 2008 / T-SQL (SS2K8) / Finding row number which caused the error in Table value parameter insertion / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 16:34:34 GMT20RE: Finding row number which caused the error in Table value parameter insertionhttp://www.sqlservercentral.com/Forums/Topic1490576-392-1.aspxIn your stored proc, why not simply check for rows which will cause an error (datalength &gt; 2, in this case) , select them and return a message to the client?Thu, 05 Sep 2013 05:17:29 GMTschleepRE: Finding row number which caused the error in Table value parameter insertionhttp://www.sqlservercentral.com/Forums/Topic1490576-392-1.aspxAre you able to put a try catch block around your SP and use SCOPE_IDENTITY()[url]http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k%28SCOPE_IDENTITY_TSQL%29;k%28SQL11.SWB.TSQLRESULTS.F1%29;k%28SQL11.SWB.TSQLQUERY.F1%29;k%28DevLang-TSQL%29&rd=true[/url]Wed, 04 Sep 2013 07:33:29 GMTDennis PostRE: Finding row number which caused the error in Table value parameter insertionhttp://www.sqlservercentral.com/Forums/Topic1490576-392-1.aspx[quote][b]mister.magoo (9/3/2013)[/b][hr]Surely this is a case for validation at the front end? There always used to be a rule that applications validate input...[/quote]Yes and no. First of all, it depends on the kind of application. It may be an ETL application, and in that case you don't validate in the client, because all you have is files. (And I more or less assume that this is ETL.)But even if the data comes from user entry, the business rules may be too complicate to warrant to duplication of them in the front-end. Things like mandatory fields, foreign keys etc are simple to enforce, but you may have more complex dependencies.Wed, 04 Sep 2013 01:18:13 GMTErland SommarskogRE: Finding row number which caused the error in Table value parameter insertionhttp://www.sqlservercentral.com/Forums/Topic1490576-392-1.aspxSurely this is a case for validation at the front end? There always used to be a rule that applications validate input...Tue, 03 Sep 2013 16:22:19 GMTmister.magooRE: Finding row number which caused the error in Table value parameter insertionhttp://www.sqlservercentral.com/Forums/Topic1490576-392-1.aspxYeah, that works too. And slicing the data in half is certainly a possibility. If you insert millions of rows, and you only have a single errors this may be more efficient than to do a loop one-by-one. Particularly, if you are lucky and the first half is good. (Then you know that you need to slice the second half already.)Tue, 03 Sep 2013 14:13:19 GMTErland SommarskogRE: Finding row number which caused the error in Table value parameter insertionhttp://www.sqlservercentral.com/Forums/Topic1490576-392-1.aspxWe are handling this from frontend ..if any error comes we rollback and divide the number of rows and insert it...so this process will go on until all the rows insert.Tue, 03 Sep 2013 06:01:35 GMTSQL006RE: Finding row number which caused the error in Table value parameter insertionhttp://www.sqlservercentral.com/Forums/Topic1490576-392-1.aspxAs you long as your stored procedure is not part of an outer transaction, it is doable:[code="sql"]BEGIN TRY MERGE ...END TRYBEGIN CATCH DECLARE cur CURSOR STATIC LOCAL FOR SELECT .. FROM @tvp OPEN cur WHILE 1 = 1 BEGIN FETCH cur INTO @var1.... IF @@fetch_status &lt;&gt; 0 BREAK BEGIN TRY MERGE .... END TRY BEGIN CATCH PRINT 'Errors for ' + .... END CATCH END DEALLOCATE curEND CATCH[/code]But if your procedure is part of a greater transaction, this may not work as the error may doom the transaction and you cannot do the fallback.And of course it is a burden to maintain two code paths..Tue, 03 Sep 2013 05:14:05 GMTErland SommarskogRE: Finding row number which caused the error in Table value parameter insertionhttp://www.sqlservercentral.com/Forums/Topic1490576-392-1.aspx[quote][b]Erland Sommarskog (9/2/2013)[/b][hr]Unfortunately, this is one of the drawbacks with working set-based. If you insert rows one a time, finding the bad data is trivial. But that is of course far less efficient. A possible strategy is to trap the error with TRY-CATCH and resort to a loop in case of an error.There is very good Connect request for this problem on [url=https://connect.microsoft.com/SQLServer/feedback/details/774754/new-virtual-table-errors-it-would-analogous-to-the-deleted-and-inserted-tables#details]https://connect.microsoft.com/SQLServer/feedback/details/774754/new-virtual-table-errors-it-would-analogous-to-the-deleted-and-inserted-tables#details[/url] that you could vote for.[/quote]Thanks for the reply Erland SommarskogI voted that feature ...microsoft should add that feature.you said to use TRY-CATCH ...but the problem is any error happens it rollback all the transaction..i am not using any transaction may be it is using batch transaction which causing it rollback.Any workaround or i have to use loop to insert the data(Performance issue)Mon, 02 Sep 2013 21:28:19 GMTSQL006RE: Finding row number which caused the error in Table value parameter insertionhttp://www.sqlservercentral.com/Forums/Topic1490576-392-1.aspxUnfortunately, this is one of the drawbacks with working set-based. If you insert rows one a time, finding the bad data is trivial. But that is of course far less efficient. A possible strategy is to trap the error with TRY-CATCH and resort to a loop in case of an error.There is very good Connect request for this problem on [url=https://connect.microsoft.com/SQLServer/feedback/details/774754/new-virtual-table-errors-it-would-analogous-to-the-deleted-and-inserted-tables#details]https://connect.microsoft.com/SQLServer/feedback/details/774754/new-virtual-table-errors-it-would-analogous-to-the-deleted-and-inserted-tables#details[/url] that you could vote for.Mon, 02 Sep 2013 15:58:48 GMTErland SommarskogFinding row number which caused the error in Table value parameter insertionhttp://www.sqlservercentral.com/Forums/Topic1490576-392-1.aspxHiI am trying to write a Bulk import procedure, i am using table value parameter to insert thousands of rows in the table.The problem is if any error happens,i am not able to know which row in the table value parameter caused it.I am not using transaction in stored procedurehere is the code[code="sql"]--Table creation CREATE TABLE DataImport (id int identity(1,1),Name varchar(2))Insert INTO DataImportvalues('aa'),('bb'),('cc'),('dd')--SELECT * FROM DataImport--Table type creationCREATE TYPE udt_Table AS TABLE (ud_name varchar(3))--stored procedure CREATE PROC Usp_DataImport@TVP [dbo].[udt_Table] READONLYASBEGIN DECLARE @Insert Table (ActionType nvarchar(10),NewestName varchar(3),OldestName vaRCHAR(3)) MERGE DataImport AS [TARGET] USING @TVP AS [SOURCE] ON [TARGET].Name = [SOURCE].ud_name WHEN MATCHED THEN UPDATE SET Name = [SOURCE].ud_name WHEN NOT MATCHED THEN INSERT (Name) VALUES([SOURCE].ud_name) OUTPUT $ACTION, Inserted.Name,Deleted.Name INTO @Insert; SELECT * FROM @InsertEND--Executing Procedure--First proc Exection sucessfully doneDECLARE @dd AS dbo.udt_Table INSERT INTO @ddvalues('ee'),('aa'),('XX'),('GG'),('66')EXEC Usp_DataImport @TVP = @dd--Second proc Exection fail this will generate error due to varchar length (String validation can be done frontend to avoid this problem...i am using this to generate the error)Here i want to get at which row error happens...so that i will return to application to make changes and then insertDECLARE @dd AS dbo.udt_Table INSERT INTO @ddvalues('ee'),('aa'),('XX'),('GG'),('GGG')('66')EXEC Usp_DataImport @TVP = @dd[/code]ThanksMon, 02 Sep 2013 04:08:07 GMTSQL006