Dynamic Sql Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Sql Query

Author

Topic

Shilpa22
Starting Member

37 Posts

Posted - 2010-05-22 : 11:24:52

I need to pass dynamic CSV to a select statement in a procedure.
Below is my code,
CREATE TABLE #temp1 (
QueueName varchar(500)
)
INSERT INTO #temp1
select Queue_ID from dws..dws_queue where performance_group_id =4

declare @tmp varchar(500)
declare @csv varchar(500)

SET @tmp = ''+'('
select @tmp = @tmp + ''''+QueueName+'''' + ', ' from #temp1

set @csv = (select SUBSTRING(@tmp, 0, LEN(@tmp)))+')'
CREATE TABLE #temp (
account_id varchar(30),
query_start_time datetime
)
declare @sql varchar(2000)
set @sql ='INSERT INTO #temp'+ (select j.account_id,j.query_start_time from dws..dws_job_query j inner join dws..dws_request_status r on j.job_id = r.job_id where j.query_status = 4000 and j.data_source_id='TDPROD1' and r.Assigned_Queue in(@csv))

print @sql

Exactly at the r.Assigned_Queue in(@csv)). It errors saying
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Wt is causing the issue
Its very urgent.Pls help me out.

Thanks in Advance
Shilpa

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-22 : 13:47:47

Please show us the output of
print @csv

No, you're never too old to Yak'n'Roll if you're too young to die.

Sommerville
Starting Member

4 Posts

Posted - 2010-05-23 : 00:24:00

Don't you think that you need to have a single quote here-
set @sql ='INSERT INTO #temp'+ (select....
Like this -
set @sql ='INSERT INTO #temp'+ '(select....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-24 : 10:14:54

Becuase @csv has multiple values
Search for Array+SQL Server

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources