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
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?
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
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.
No one has followed this question yet.