Dynamic selection of table in SQL Query

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 2012 Forums

Analysis Server and Reporting Services (2012)

Dynamic selection of table in SQL Query

Author

Topic

jawad.hassan
Starting Member

6 Posts

Posted - 2013-07-27 : 05:08:17

Hi,
I am using SQL Server 2008 R2 and trying to develop below query for my report development in BIDS.

SELECT A.Date, A.Phone, B.Name, A.Campaign, A.Campaign_ID, A.Dialing_ID, B.Comments, B.Duration
FROM
(
SELECT d.Date, d.Phone, d.Campaign_ID, c.Campaign, d.Dialing_ID,
d.Skill_ID , (d.Key + d.Key_Day) AS Call_ID
FROM Results AS d INNER JOIN
Campaign AS c ON d.Campaign_ID = c.Campaign_ID INNER JOIN
Rule AS r ON d.Rule_ID = r.Rule_ID INNER JOIN
Import_Rule AS ir ON r.Rule_ID = ir.Rule_ID
WHERE (d.Date BETWEEN DATEADD(hour, CONVERT(INT, @StartHour), DATEADD(minute, CONVERT(INT, @StartMinute), @DateFrom))
AND DATEADD(hour, CONVERT(INT, @EndHour), DATEADD(minute, CONVERT(INT, @EndMinute), @DateTo)))
AND d.Campaign_ID IN (@Campaign_List)
) AS A LEFT OUTER JOIN

(
SELECT cd.Date, cd.Agent_ID, cd.Skill_ID,
(cd.Key_Day + cd.Key) AS Call_ID, cd.Duration, cd.Comments,
a.Name, a.Login_ID
FROM Call_Detail AS cd INNER JOIN
Agent AS a ON cd.Agent_ID = a.ID
WHERE (cd.Date BETWEEN DATEADD(hour, CONVERT(INT, @StartHour), DATEADD(minute, CONVERT(INT, @StartMinute), @DateFrom))
AND DATEADD(hour, CONVERT(INT, @EndHour), DATEADD(minute, CONVERT(INT, @EndMinute), @DateTo)))
) AS B ON A.Call_ID = B.Call_ID AND A.Skill_ID = B.Skill_ID LEFT OUTER JOIN
(
SELECT Dialing_ID, Phone
FROM DL_5017
)AS N ON N.Dialing_ID = A.Dialing_ID

In above query @StartHour, @EndHour, @StartMinute, @EndMinute, @DateFrom, @DateTo and @Campaign_List are all
report parameters created in BIDS and attached to their respective Datasets.

The problem i am facing is that i want to make the selection of tables for below query dynamic based on value selected in
@Campaign_List.

SELECT Dialing_ID, Phone
FROM DL_5017_5017
)AS N ON N.Dialing_ID = A.Dialing_ID

I am not getting how to use the selected value of @Campaign_List with case statement for dynamic selection of tables
in above query.

The possible values for @Campaign_List are (5017,5018,5019,5021) etc.

A prompt response in this regard will be highly appreciated.

Thanks.

Regards,
Jawad Hassan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-27 : 08:41:14

you need dynamic sql for that. but its really ugly to use dynamic sql as report source. why do you need so many tables? why names like 5017 etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jawad.hassan
Starting Member

6 Posts

Posted - 2013-07-27 : 11:01:06

To fulfill the report field requirements i need all these tables. These are not the names but the IDs. Any solution for this via dynamic query?

Jawad Hassan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-28 : 03:11:29

do you mean they're values within the table field? or tablename themselves?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jawad.hassan
Starting Member

6 Posts

Posted - 2013-07-28 : 03:44:08

No these are not the tables but the values of report parameter and will be used in where clause as Campaign_ID IN (@Campaign_List).

Jawad Hassan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-28 : 11:30:43

quote:
Originally posted by jawad.hassan

No these are not the tables but the values of report parameter and will be used in where clause as Campaign_ID IN (@Campaign_List).

Jawad Hassan

ok..in that case you could do something like below condition


WHERE ',' + @Campaign_List + ',' LIKE '%,' + CAST(Campaign_ID AS varchar(10)) + ',%'

If you pass comma separated va;ue through parameter

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources