Some notes on usage Table variables

Hello everybody,

today I want to share some fragment of SQL, that I've used for updating tables in Acumatica.

First of all, I'd like to show table variable that I've created:

DECLARE @SalesOrderInformation TABLE
(
  OrderType nvarchar(2),
  OrderNbr nvarchar(12)
)

It has nothing special, just two fields: ordertype and ordernbr. 

I need them as temporar storage for future update. T-SQL allows to use two kind of temporary tables: table variable and temp table, which starts from #.

I prefer table variables for the following reasons:

1. Performance. Because limited scope, as usually performance fo table variables is faster then temp tables. 

2. Temporary tables instead of table variable in stored procedure will require additional orchestration from SQL which again affects perfromance

3. In table variable you can add if needed some constraint. 

After declaring table, I've used it in the following way:

insert into @SalesOrderInformation 
select distinct o.ordertype, OrderNbr from SOOrder o
where o.customerRefNbr like '%REV' or o.customerRefNbr like '%INV'
 
select * from @SalesOrderInformation 
 
update SOLine set UsrID = null where exists 
	(select * from  @SalesOrderInformation si where si.OrderType = OrderType and si.ordernbr = OrderNbr )

as outcome, I was able quickly clean database from some not needed information, which passed into it in wrong way.

No Comments

Add a Comment