help with dynamic sql - 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)

help with dynamic sql

Author

Topic

snafu7x7
Starting Member

2 Posts

Posted - 2007-07-30 : 13:38:22

I have what 'should' be an easy query but its stumping me so I'm guessing I'm overlooking something simple. I have a data model for Users, Products, Orders and OrderItems (in other words, a User can place one to many orders, an order contains one to many order items and each order item corresponds to a Product PK). I want a query that will return me a list of users that have NOT purchased a given set of products. Since I need to be able to pass in a list of productIds I used dynamic SQL as follows. However it is not working as I want it to...any insight? Much thanx in advance!

CREATE PROCEDURE [dbo].[GetUsersByProductPurchaseExclusion]
@ProductIdsList varchar(1000)
AS

DECLARE @SQL varchar(1200)
IF(LEN(@ProductIdsList) >0)

BEGIN
Select @SQL = 'SELECT dbo.Users.* FROM dbo.OrderItems INNER JOIN dbo.Orders ON dbo.OrderItems.OrderId = dbo.Orders.Id INNER JOIN dbo.Users ON dbo.Orders.UserId = dbo.Users.Id '
Select @SQL = @SQL + ' WHERE ProductId NOT IN (' + @ProductIdsList +')'
PRINT @SQL
Exec ( @SQL)

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-30 : 13:44:04

http://www.sqlteam.com/article/using-a-csv-with-an-in-sub-select

Tara Kizer
http://weblogs.sqlteam.com/tarad/

snafu7x7
Starting Member

2 Posts

Posted - 2007-07-30 : 14:27:37

quote:
Originally posted by tkizer

http://www.sqlteam.com/article/using-a-csv-with-an-in-sub-select

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Excellent article Tara, I've implemented it just as described and will use that method in the future when I need to pass in a list of ids. I've converted my proc to this:

ALTER PROCEDURE [dbo].[GetUsersByProductPurchaseExclusion]
@ProductIdsList varchar(1000)
AS
SELECT dbo.Users.*, dbo.OrderItems.ProductId FROM dbo.OrderItems INNER JOIN dbo.Orders ON dbo.OrderItems.OrderId = dbo.Orders.Id INNER JOIN dbo.Users ON dbo.Orders.UserId = dbo.Users.Id
WHERE ProductId NOT IN (SELECT IntValue from dbo.CsvToInt(@ProductIdsList))

However it doesn't fix my problem which is undoubtedly a logic error. What appears to be happening is this. Say I have User.Id = 1 and that user has purchased ProductId's : 1000, 1001, and 1002. If I call my proc like this:

GetUsersByProductPurchaseExclusion 1001

The proc will correctly filter out the relationship bewteen user.id=1 and orderItems.ProductId=1001 but since it returns true for the other 2 productIds I still get two rows. What I want it to do is say 'User.Id=1 has purchased ProductId=1001 so don't EVER return that user in the result set' Does that make sense?

thanx

S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources