DECLARE @CSVString varchar( 255 )
DECLARE @TMP varchar( 255 )

SELECT @TMP = ( SELECT tbl1.csvList FROM tbl1 WHERE  tbl1.id = 5 )
SELECT @CSVString = ( SELECT dbo.QuoteCSV( @TMP ) )
SELECT 
	tbl2.tbl2_id AS ID, 
    	tbl2.tbl2_name AS Name,  
    	tbl2.tbl2_city_name AS City,  
	'SomeOtherValue' AS Type,
	CONVERT( varchar(255), tbl2.tbl2_profile) AS Profile
FROM tbl2
WHERE tbl2.tbl2_id2 = 5
AND 
CONVERT( varchar( 255 ), tbl2.tbl2_id ) IN ((SELECT dbo.QuoteCSV(@TMP)))

 

 
SELECT tbl1.csvList FROM tbl1 WHERE  tbl1.id = 5  

SELECT 
	tbl2.tbl2_id AS ID, 
    	tbl2.tbl2_name AS Name,  
    	tbl2.tbl2_city_name AS City,  
	'SomeOtherValue' AS Type,
	CONVERT( varchar(255), tbl2.tbl2_profile) AS Profile
FROM tbl2
WHERE tbl2.tbl2_id2 = 5
AND CONVERT( varchar( 255 ), tbl2.tbl2_id ) IN (CONVERT( varchar(255), SELECT tbl1.ID FROM tbl1 WHERE  tbl1.id = 5))
 

Select * from employees
WHERE employeeID IN (select employeeID from Orders where OrderDate BETWEEN '7/4/1996' AND '7/10/1996')
 

DECLARE @CSVString varchar( 255 )
DECLARE @TMP varchar( 255 )
DECLARE @SQL VARCHAR (4000)

SELECT @TMP = ( SELECT tbl1.csvList FROM tbl1 WHERE  tbl1.id = 5 )
SELECT @CSVString = ( SELECT dbo.QuoteCSV( @TMP ) )

SELECT @SQL = '
SELECT 
	tbl2.tbl2_id AS ID, 
    	tbl2.tbl2_name AS Name,  
    	tbl2.tbl2_city_name AS City,  
	'SomeOtherValue' AS Type,
	CONVERT( varchar(255), tbl2.tbl2_profile) AS Profile
FROM tbl2
WHERE tbl2.tbl2_id2 = 5
AND CONVERT( varchar( 255 ), tbl2.tbl2_id ) IN ('

SELECT @SQL = @SQL + @CSVString + ')'
EXEC (@SQL)
 

quote:

---

T-SQL is a compiled language, not an interpretative language. As such you need to view how the code looks to the SQL Server database engine. When it is presented with code such as:

WHERE c1 in (@parameter)

the SQL Server database engine is treating the data in @parameter as a single value, even if you happen to be passing a comma-delimited set of values into the stored procedure. To clarify this a bit, let's assume that you have a column holding character information and the strings being stored themselves contain commas. So you have data that looks like:

Washington, George
Adams, John
Jefferson, Thomas

If you passed in for the value of @parameter, "Adams, John", you would want SQL Server to treat the embedded comma as a character in the search string, not as a delimiter between separate elements. Well this in fact is how SQL Server interprets the value of @parameter ... as a **single** element. It may appear to you as a comma-delimited string of values, but SQL Server **must** interpret it as a single element.

---