question

eztop5522 avatar image
eztop5522 asked

Validate data from each row and execute sp

I want to pass each row selecting state/zipcode from table as parameters into sp returning true/false value. On false return insert record into error-table.
tableinline-table-valued-function
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It seems that what you are proposing is to take data in a dataset, then carry out an operation on it one row at a time. This isn't usually the most efficient way of carrying out operations in a relational database like SQL Server. Perhaps you can explain a bit more what you're trying to accomplish, as normally I'd suggest doing an operation once, on the set of data (perhaps as part of a redefined SP) to get a better performing solution?
4 Likes 4 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
Instead, do an [INSERT...SELECT][1] syntax. You can then use a JOIN or a [CROSS APPLY][2] to find those records that don't match your criteria whatever it might. That's the appropriate way to get this done within T-SQL. You can still use the in-line table function that way too. [1]: http://msdn.microsoft.com/en-us/library/ms174335.aspx [2]: https://www.simple-talk.com/sql/t-sql-programming/sql-server-apply-basics/
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Grant. This is an example of what I wrote: Maybe you can give feedback since our shop is validating vendor data for each record in Temp_NewHire2 using State and zip-code entries. Passing the respective fields into SP and validating these fields agains another State/Zip-Code table and returning a Count to determine clean or error was detected. If error then build/Insert the record into Error_NewHire table. Truncate Table [dbo].[TempHire] INSERT into [dbo].[TempHire] (TEmplID, TState, TZipCode) SELECT EmployeeID, State, ZipCode from Temp_NewHire2 where EmployeeID != ' ' select @State=c.State, @Param2=SUBSTRING(c.ZipCode, 1,3), @EmplID=c.EmployeeId from Temp_NewHire2 c Join TempHire d on c.EmployeeId = d.TEmplID Execute SP_StateZipLookup @State, @Param2, @Key, @StateCount IF @StateCount = 0 BEGIN INSERT into dbo.Error_NewHire (a.RecordType, a.ErrorCode) SELECT b.RecordType, (b.@errorCode) FROM TempUMASS_NewHire2 Join TempHire d on b.EmployeeId = d.TEmplID END GO
0 Likes 0 ·
So, instead of building a table, why not just use the SELECT statement that built the table as the FROM clause?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.