help to convert query to dynamic sql

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

Transact-SQL (2000)

help to convert query to dynamic sql

Author

Topic

PIERCA
Starting Member

2 Posts

Posted - 2005-03-29 : 14:13:51

Hello i have this query

can someone help me to transform this query in dynamic sql ? as the clause WHERE IN do not handle more than 1 parameters

select pro_name, month(dis_dat) as month_pp ,sum(distrib_first) as tot_draw_first
from
(SELECT x.iss_no, x.pro_name,x.distrib, x.dis_dat, sum(ret_qty) AS unsold,
CASE WHEN DIS_DAT BETWEEN @periodfrom AND @periodto THEN distrib end as DISTRIB_FIRST

FROM
(SELECT iss_no, pro_name, dis_dat, distribution.pro_cod, sum(dis_qty) AS distrib
FROM distribution
INNER JOIN publisher on product.pub_cod = publisher.pub_cod
WHERE
(product.pro_cod IN (@product) and DIS_DAT BETWEEN @periodfrom AND @periodto)
GROUP BY distribution.iss_no, pro_name, dis_dat, distribution.pro_cod )
AS x(iss_no, pro_name, dis_dat, pro_cod, distrib)
JOIN returns ON returns.pro_cod = x.pro_cod AND returns.iss_no = x.iss_no
GROUP BY x.iss_no, x.pro_name, x.dis_dat, x.distrib
) AS sales_comp

group by pro_name , month(dis_dat) order by pro_name
GO

thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-03-29 : 15:55:57

Hello, I'm feeling suicidal. Can someone help me buy a gun and load it for me.

(Sorry, I couldn't resist. And no, I don't care for Dynamic SQL as a solution but that's me...)

OTOH, you could disassemble the @Product list into separate rows of a temp table or table variable (See BOL for CharIndex) and then either join to that table or use a subquery of that table to supply the "IN" list.

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

amachanic
SQL Server MVP

169 Posts

Posted - 2005-03-29 : 16:20:43

www.sommarskog.se/dyn-search.html

---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-29 : 17:56:49

GREAT link, Adam.

- Jeff

amachanic
SQL Server MVP

169 Posts

Posted - 2005-03-29 : 18:18:49

Check out his other articles while you're there. I post his articles as responses to probably 20% of the questions I see on newsgroups. 40% get the ASPFAQ Etiquette link ( http://www.aspfaq.com/etiquette.asp?id=5006 ) ... and the other 40% get shafted; I reply to them myself!

---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources