If I want to do a where for various values on a column, is there any other way I can do it apart from using with's?
For example if I want to check if an ID is 10234,10453 or 10239. This is more for ease of code writing
asked
Multiple Lookups
There are a few ways you can do this depending on your exact needs (maybe you can explain your scenario in more detail?) but you could potentially use
SELECT tA.colA, tA.colB FROM TableA as tA WHERE tA.ColID in (10234,10453,10239)
is this being used in an application somewhere? Are those IDs always going to be treated differently?
you mean like :
where x in (10234,10453,10239)
beware of performance however. Exists could be a better choice
I agree with Steinar and FatherJack, and want to give you an additional sample.
If you want to send a list of ID:s to your Stored procedure and the numbe of ID:s may vary, then I would
- create a User defined Table type with one column of datatype int.
- Create the procedure with a parameter of that table type
- Use the parameter to join in your select statement
- Declare a variable of that datatype, fill it with your ID:s and pass it into the procedure
The code for the table type:
CREATE TYPE MyTableType AS TABLE
( ID INT );
GO
The procedure should look something like this:
CREATE PROC spTest (@myParam1 AS MyTableType)
AS
SELECT tA.colA, tA.colB
FROM TableA as tA
INNER JOIN @myParam1 as tB
ON tA.ID=tb.myIntcolumn
This is a clean an simple solution to handle various number of values to search for, BUT it will only work in SQL Server 2008. In earlier versions you have to send a "comma seprated list" and split it into a table and that was a very slow process.
+1 - good answer, although personally I would declare the table type with a unique constraint so that you could not request the same row multiple times
You are absolutely right! I made a copy & paste mistake. :) Copied from BOL and modified it to fit my sample.
