June 17, 2014 at 1:20 pm
Hi,
I have a simple query in which I'm retrieving all sales orders of a certain type.
SELECT *
FROM SalesOrders
WHERE SalesOrders.OrderType IN ('10','15','18')
Suppose instead of specifying the OrderType values in the WHERE clause, I want to store them in a variable prior to the SELECT statement and reference the variable in the WHERE (or in a JOIN) instead. What is the best way to handle this?
I greatly appreciate any assistance.
June 17, 2014 at 1:23 pm
One of many methods ....
DECLARE @OrderTypes TABLE
(OrderType CHAR(2))
INSERT @OrderTypes
VALUES ('10'),('15'),('18')
SELECT *
FROM SalesOrders AS SO
INNER JOIN @OrderTypes AS OT
ON SO.OrderType = SO.OrderType
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 18, 2014 at 4:30 am
Table variables can work, but understand that they don't have statistics which could affect their use in something like a JOIN. You might be better off using a temporary table. That does have statistics created on it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply