Monday, December 20, 2010

SQL 2008 Table data type

My developer ask me question about table data type.
Google and found this new features sql server 2008-user defined table type and table valued parameters and this Passing table valued parameters in SQL Server 2008

We have the same scenario but with additional step
When we execute the store procedure with pass in parameter, we want
1. Check whether the data already exists in table.
2. If exists, update the data with new value
3. If not exists, insert the data

In this store procedure, we have this :

CREATE procedure insertintocustomer(@Cust_ID int, @Cust_Name varchar(50),@Cust_Surname varchar(50),@Cust_Email varchar(50))
as
begin

If exists (select 1 from customers where cust_id=@Cust_ID)
Begin
update customers
set cust_surname=@Cust_Surname, Cust_Email=@Cust_Email
where cust_id=@Cust_id
END
else
BEGIN
insert into customers
values(
@Cust_ID, @Cust_Name,@Cust_Surname,@Cust_Email)
END

End


So we do this
alter procedure newcustomer(@Customer_details customertype READONLY)

as

begin

update Customers
set Cust_Name=b.Cust_name,
Cust_Surname=b.Cust_surname,
Cust_Email=b.Cust_email
from @Customer_details b
where Customers.Cust_ID=b.Cust_Id

insert into customers
select * from @Customer_details a
where not exists (Select 1 from Customers b where a.cust_id=b.Cust_ID)

end

No comments: