Share This!

Tuesday, February 12, 2013

Temp Tables in SQL Server

We all know you can create variables in SQL Server...
declare @customer varchar(20)
set @customer='a customer'

...but what if there is a need to store more complex data?
As it turns out, there is an easy way to accomplish that as well.

declare @csrlist Table(customer varchar(20), CSR varchar(25), counts int)

--get the counts of customer service reps orders for each customer.
insert into @csrlist (customer, csr, counts)
(
select distinct customer, Csr, COUNT(1) counts
from Purchase_Order
where Csr is not null
group by customer, csr

The resulting in-memory table can be inserted to, deleted from, updated, just like any real data table.
--find the CSR with the most orders for each customer
insert into @csrlist2 (customer, CSR)
    (select customer, Csr from @csrlist A where counts=(select MAX(counts) from @csrlist B where a.customer=b.customer))


Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

DropdownList: SelectedValue does nothing.

 I have a ComboBox set as a DropDownList, and the SelectedValue property, which ostensibly will find and select the selected value, does not.

cbSalesman.SelectedValue = salesman;  This is probably because you would need to give it not just a string to look for, but an actual object that is a member of the Items list.  That would require ugly gyrations along the lines of the following.
  1. get the string to look for
  2. get the index of that string in the dropdown (using Items.IndexOf)
  3. grab the item at that index
  4. feed it to the "SelectedValue" property.
Instead of all that, why not just do this.

cbSalesman.SelectedIndex = cbSalesman.Items.IndexOf(salesman);


...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Contact Us

Name

Email *

Message *