ramlicious Blogs by Tina & Prabhu

November 17, 2011

User-defined table types

Filed under: Programming,SQL Server — Prabhuram @ 11:25 am

With SQL Server 2008’s user-defined table types, it is easy to pass a collection of rows to a procedure. Moreover, these table types can also be consumed from the programming languages using collection objects. Read here for more about Table valued parameters.

CREATE TYPE dbo.addr AS TABLE
(
	id      NVARCHAR(100),
	city    NVARCHAR(200),
	zip     NVARCHAR(10),
    PRIMARY KEY (id)
)
GO

CREATE PROCEDURE update_addresses
	@emp_addr addr READONLY
AS
BEGIN
    UPDATE EMP
       SET city = a.city
    FROM EMP e INNER JOIN @emp_addr a ON e.emp_id = a.id
END

No Comments

No comments yet.

RSS feed for comments on this post. TrackBack URL

Sorry, the comment form is closed at this time.

Powered by WordPress