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:
Post a Comment