Wednesday, September 1, 2010

SQL - User Defined Table Types

So a very interesting feature in SQL Server 2008 is user-defined table types. This is basically a custom variable type. Similary to a typedef in C. You can create user-defined table type as so...


create type CustomerUdt as table
(
Id int,
CustomerName nvarchar(50),
PostalCode nvarchar(50)
)


and then utilize that in a stored procedure like so...


create procedure InsertCustomerInfo
(
@CustomerInfoTvp as CustomerUdt readonly
)
as
insert into [Customers]
select * from @CustomerInfoTvp


this will do a bulk insertion for you... very nice!

the schema that you setup in the user-defined table type has to be the same as [Customers] in the example above.

This works best in the case where you are make multiple calls to the database for each row of data to separate tables. You can combine user-defined table types in one stored procedure like so...


create procedure InsertNewOrder
(
@OrderTvp as OrderUdt readonly,
@OrderDetailTvp as OrderDetailsUdt readonly
)
insert into [Orders]
select * from @OrderTvp
insert into [OrderDetails]
select * from @OrderDetailTvp


That makes one call to the database. Easy peasy.

iPhone Development - Day 11 Review

I have resumed my studies utilizing the Apple documentation. Whoever thought that documentation from the creators would be so easy to understand and read? It might also because I have attempted to read three books now on the subject with little help, but the documentation clears up some techniques and concepts. We'll see how this goes ... great a separate attempt at this stupid project, of course I am excited to learn mobile development, but damn, is it this hard for real?