Query runs twice, only works once...

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)

Query runs twice, only works once...

Author

Topic

Linok
Starting Member

34 Posts

Posted - 2006-06-14 : 14:08:23

Hey everyone,

I'me working on this stored procedure that will run a certain query based on whether or not it's two weeks from a date.

ALTER PROCEDURE coglej.GetEventsForDate
@eventdate datetime,
@eventowner varchar (50)

AS 

IF DATEDIFF(ww,GetDate(),@eventdate ) > 2 
BEGIN
PRINT 'Getting EB event price.'
SELECT     dbo.events.event_date, dbo.locations.city, dbo.locations.state, dbo.locations.name, dbo.locations.address1, dbo.locations.address2, dbo.locations.zip, 
                      dbo.skus.sku_id, dbo.skus.event_type, dbo.skus.price, dbo.skus.slots_available
FROM         dbo.events INNER JOIN
                      dbo.locations ON dbo.events.location_id = dbo.locations.location_id INNER JOIN
                      dbo.skus ON dbo.events.sku_id = dbo.skus.sku_id
WHERE     (dbo.events.event_date = CONVERT(datetime, @eventdate)) AND (dbo.skus.owner = @eventowner) AND (dbo.skus.price_type = 'Early')

END
ELSE
BEGIN
PRINT 'Getting regular event price.'
SELECT     dbo.events.event_date, dbo.locations.city, dbo.locations.state, dbo.locations.name, dbo.locations.address1, dbo.locations.address2, dbo.locations.zip, 
                      dbo.skus.sku_id, dbo.skus.event_type, dbo.skus.price, dbo.skus.slots_available
FROM         dbo.events INNER JOIN
                      dbo.locations ON dbo.events.location_id = dbo.locations.location_id INNER JOIN
                      dbo.skus ON dbo.events.sku_id = dbo.skus.sku_id
WHERE     (dbo.events.event_date = CONVERT(datetime, @eventdate)) AND (dbo.skus.owner = '@eventowner') AND (dbo.skus.price_type = 'Regular')

END

Both SELECT statements are identical, except for the price_type. The problem is that the bottom query never returns results. I've been able to run simplified queries such as:

SELECT     sku_id, event_type, price, owner, slots_available, price_type
FROM         dbo.skus
WHERE     (owner = 'DVX') AND (price_type = 'Regular')

NOTE: 'DVX' and 'Regular' are the same things I'm trying to test against in the SP

and pull results just fine. Does anyone have any idea what I'm missing?

Thanks in advance,

Joel

Maux Maux
Starting Member

10 Posts

Posted - 2006-06-14 : 14:28:25

cant you just do it with one select and get rid of the IF?



SELECT     dbo.events.event_date, dbo.locations.city, dbo.locations.state, dbo.locations.name, dbo.locations.address1, dbo.locations.address2, dbo.locations.zip, 
                      dbo.skus.sku_id, dbo.skus.event_type, dbo.skus.price, dbo.skus.slots_available
FROM         dbo.events INNER JOIN
                      dbo.locations ON dbo.events.location_id = dbo.locations.location_id INNER JOIN
                      dbo.skus ON dbo.events.sku_id = dbo.skus.sku_id
WHERE     (dbo.events.event_date = CONVERT(datetime, @eventdate)) 
	AND (dbo.skus.owner = '@eventowner') 
	AND (dbo.skus.price_type = CASE WHEN DATEDIFF(ww,GetDate(),@eventdate )
					<= 2 THEN 'Regular' ELSE 'Early' END )

Linok
Starting Member

34 Posts

Posted - 2006-06-14 : 14:50:18

When I tried your code, it wasn't returning any results at all.

tdietrich
Starting Member

2 Posts

Posted - 2006-06-14 : 16:38:45

It looks like one of the filter conditions in your second query is incorrect.

You have: (dbo.skus.owner = '@eventowner')

I think it should be: (dbo.skus.owner = @eventowner)

-- Tim

Maux Maux
Starting Member

10 Posts

Posted - 2006-06-15 : 11:28:30

I think you got it there Tim


SELECT     dbo.events.event_date, dbo.locations.city, dbo.locations.state, dbo.locations.name, dbo.locations.address1, dbo.locations.address2, dbo.locations.zip, 
                      dbo.skus.sku_id, dbo.skus.event_type, dbo.skus.price, dbo.skus.slots_available
FROM         dbo.events INNER JOIN
                      dbo.locations ON dbo.events.location_id = dbo.locations.location_id INNER JOIN
                      dbo.skus ON dbo.events.sku_id = dbo.skus.sku_id
WHERE     (dbo.events.event_date = CONVERT(datetime, @eventdate)) 
	AND (dbo.skus.owner = @eventowner) -- no single quotes!!!
	AND (dbo.skus.price_type = CASE WHEN DATEDIFF(ww,GetDate(),@eventdate )
					<= 2 THEN 'Regular' ELSE 'Early' END )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources