	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

11/06/2009 at 08:08AM PST, ID: 24878287 | Points: 500

	[x] Attachment Details

Output variable contains list of values but when calling <field> IN @outputvar, no results are returned

Asked by c0fee in SQL Query Syntax, SQL Server 2005, SQL Server 2008

I have a stored proc with two SELECT statements. The first one outputs the results in a comma delimited list stored in an output variable @agentlist. That variable is then used in the second SELECT statement within the where clause. However, it doesn't seem to work as no results are returned for the second statement, even though I know there are records for those values.

@agentlist will output this value: agent1, agent2, agent3, agent4

Is this failing because I need to encapsulate each agent ID in quotes? If so, how do I do that?

View the Solution FREE for 30 Days

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

           
           
             ALTER PROCEDURE [dbo].[sp_Custom_Get_Agents] (
 @begindatetime	datetime,
 @enddatetime datetime,
 @agentlist varchar(max) output
)		
        
AS 
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF  
/* GET LIST OF AGENTS */  
SELECT @agentlist = REPLACE((Select distinct cName as [data()]
From Membership
Where dintervalstart >= @begindatetime
and dintervalstart < @enddatetime
FOR XML PATH ('')), ' ', ',')   
/*USE LIST OF AGENTS OBTAINED PREVIOUSLY HERE AND GET THEIR AVAILABILITY*/  
SELECT agentID, sum(availability)
From Availability
Where agentID in (@agentlist)  
Group by AgentID
GO
           
         

Related Solutions: Need to output values from sproc in comma delimited format

20091111-EE-VQP-91 - Hierarchy / EE_QW_Related_20080208

1. angelIII983,351
2. aneeshatti...741,674
3. matthews...571,487
4. capricorn1406,214
5. rrjegan17402,769
6. mwvisa1390,808
7. ralmada332,041
8. Sharath_...326,005
9. BrandonG...272,108
10. reb73229,974
11. chapmandew221,436
12. peter57r201,096
13. mark_wills177,032
14. boag2000157,180
15. pcelba154,279
16. sdstuber139,316
17. TextReport134,666
18. acperkins132,452
19. DanielWilson125,383
20. DatabaseMX121,464
21. pivar117,472
22. mrjoltcola116,984
23. LSMCons...114,570
24. RiteshShah114,551
25. pssandhu112,811

1. angelIII3,010,791
2. capricorn11,252,886
3. matthews...1,236,223
4. aneeshatti...1,153,277
5. mwvisa1826,226
6. chapmandew710,108
7. mark_wills694,816
8. BrandonG...557,946
9. peter57r494,820
10. ee_rlee460,453
11. rrjegan17402,769
12. sdstuber397,217
13. DatabaseMX391,015
14. Sharath_...365,805
15. dqmq346,705
16. ralmada334,041
17. GRayL330,160
18. DanielWilson323,456
19. boag2000318,493
20. harfang298,054
21. LSMCons...279,439
22. jimhorn238,445
23. reb73229,974
24. acperkins227,845
25. imitchie220,374