How to use subquery in IN in a dynamic sql for varchar type

Question

Apparently I could not do the following:

DECLARE @sql VARCHAR(2000)

SET @sql = 'SELECT es.* FROM emp_schedule es
WHERE  es.work_status IN (' + (SELECT data_value FROM #absences) + ')'     

exec sp_executesql @sql

column data_value is of type varchar and so is es.work_status. This query will give me error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." which makes sense since it is being used as expression in this one. Any ideas how I could implement this piece. Thanks guys.

does the select need to be a comma separated list? or should it only return 1 value? — nathan gonzalez, Apr 08 '11 at 16:11

manji · Answer 1 · 2011-04-08T16:18:52.640

2

Why not using a join?:

DECLARE @sql VARCHAR(2000)

SET @sql = 'SELECT es.* 
              FROM emp_schedule es
              JOIN #absences a ON es.work_status = a.data_value'

exec sp_executesql @sql

EDIT:

what about this:

SET @sql = 'SELECT es.* FROM emp_schedule es
             WHERE  es.work_status IN (SELECT data_value FROM #absences)'

i.e.: appending the (SELECT data_values... statement inside @sql

edited Apr 08 '11 at 16:18

answered Apr 08 '11 at 16:06

manji

47,442
5
96
103

well that is not the purpose. I posed this query as an example, I need to use it in a large stored proc where I can not afford to change the logic from IN to join. – Tintin Apr 08 '11 at 16:09
+1 that would have been my first recommendation, too - why make life harder than it has to be?? – marc_s Apr 08 '11 at 16:10
so the question is, how to do a group_concat like MySql? look here: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 – manji Apr 08 '11 at 16:12
Guys the condition is imposed in a deep inside where clause with lot of other case and <> OR and AND conditions. I could not use the join condition without re writing the whole stored proc. Moreover, I wanted to know this for future references too. I just dont want to write a function which will split the data from subquery into '' comma separated string. Thanks. – Tintin Apr 08 '11 at 16:13
@user503510, what do you want then? – nathan gonzalez Apr 08 '11 at 16:14
Can you do an INNER JOIN to a derived table to allow for the complex query? Such as: SELECT es.* FROM emp_schedule es INNER JOIN (my_complex_query) tmp ON es.work_status = tmp.data_value – Solomon Rutzky Apr 08 '11 at 16:31

Thomas · Accepted Answer · 2011-04-08T19:33:42.237

2

First, you can do this without concatenation:

Set @Sql = 'Select es.... 
                From emp_schedule As es
                Where es.work_status In ( Select data_value
                                                        From #absenses )'

However, if you must A: do this with dynamic SQL and B: in way where you can statically build the SQL statement instead of just querying directly from the temp table then you can do:

Declare @InParameters nvarchar(max)

Set @InParameters = Stuff(
                        (
                        Select ',' + QuoteName(data_value,'''')
                        From #absenses
                        For Xml Path('')
                        ), 1, 1, '')

Select @Sql = 'Select es...
                From emp_schedule As es
                Where es.work_status In (' + @InParameters + ')'

edited Apr 08 '11 at 19:33

answered Apr 08 '11 at 16:23

Thomas

63,911
12
95
141

Oh my god! Yes I could have directly embedded the whole subquery in string because the temp table already is available in the database. Thank you tons Thomas... – Tintin Apr 08 '11 at 16:48
On an another node, with the second approach the problem is, I will get a string like 'VAC,JURY_DUTY' and what I should be getting is 'VAC','JURY_DUTY' (because the work_status is of type varchar)... any tips on that one? Thanks. – Tintin Apr 08 '11 at 16:50
Little more on that one, you need to append the @InParameters with ''' for initial quote of the varchar. Please could you add SET @InParameters = '''' + @InParameters ; and then I will mark your answer as the answer. Thanks again Thomas. – Tintin Apr 08 '11 at 17:06
@user503510 - Really? On my tests, @InParameters results in values that are all wrapped properly in single quotes including the first value. I.e., you shouldn't need to prepend another single quote to @InParameters. – Thomas Apr 08 '11 at 17:32
If #absences has only 2 values in data_value : VAC and BRT. on print @InParameters, I am getting VAC','BRT'... I have no clue why you are getting it all properly wrapped... – Tintin Apr 08 '11 at 19:19
@user503510 - I fixed the problem. The problem is that I was having Stuff remove the first two characters (which you would if your delimiter was two characters) instead of just one. If you change your call to Stuff to only strip the leading comma, you should get the right results. – Thomas Apr 08 '11 at 19:34

How to use subquery in IN in a dynamic sql for varchar type

2 Answers2

EDIT: