Urgent: Problem with the stored procedure

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)

Urgent: Problem with the stored procedure

Author

Topic

sql-buzz
Starting Member

7 Posts

Posted - 2008-08-15 : 16:16:51

When i run this stored proc I get the following exception:

usp_GetExpectedCommision 3, '7/19/2008 12:00:00 AM'

Procedure usp_GetExpectedCommision, Line 15
Conversion failed when converting datetime from character string.

Can somebody tell me how to solve this problem...

---------------------------
ALTER PROCEDURE [dbo].[usp_GetExpectedCommision]

@ProductID int,
@CommissionStartDate DateTime
AS
BEGIN

SELECT
pcv.*, 'EndDate' =
CASE
WHEN pcv.DateEnd = NULL THEN getdate()
ELSE pcv.DateEnd
END
from ProductCommissionVendor pcv
where pcv.ProductID = @ProductID
and pcv.Active = 'true'
and @CommissionStartDate between pcv.DateStart and 'EndDate
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-15 : 16:41:44

quote:

@CommissionStartDate between pcv.DateStart and 'EndDate

You can't do that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sql-buzz
Starting Member

7 Posts

Posted - 2008-08-15 : 16:44:08

So can somebody tell me the solution. It works when I pass the values in a select..It doesnt work as a storedProc with parameters

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-15 : 16:48:41

Show us the code when it works.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sql-buzz
Starting Member

7 Posts

Posted - 2008-08-15 : 16:50:44

IT WORKS WITHOUT STOREDPROC:

SELECT
pbv.*, 'EndDate' =
CASE
WHEN pbv.DateEnd = NULL THEN getdate()
ELSE pbv.DateEnd
END
from ProductBonusVendor pbv
where pbv.PaymentName = 'ADV PROD BONUS'--@PaymentName
and pbv.Active = 'true'
and '7/19/2008 12:00:00 AM'/*@CommissionStartDate*/ between pbv.DateStart and 'EndDate'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-15 : 16:54:48

You can copy your case statement to 'EndDate' in the where clause, or you could use a derived table.

I'm surprised that the code works outside of a stored procedure as you can't use aliases in the where clause like this. You can use them in order bys though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sql-buzz
Starting Member

7 Posts

Posted - 2008-08-15 : 17:00:20

--Can you help me with the syntax..
-I dont get any errors but I dont even get the result

SELECT
pbv.*
from ProductBonusVendor pbv
where pbv.PaymentName = 'ADV PROD BONUS'--@PaymentName
and pbv.Active = 'true'
and '7/19/2008 12:00:00 AM'/*@CommissionStartDate*/ between pbv.DateStart and
CASE
WHEN pbv.DateEnd = NULL THEN getdate()
ELSE pbv.DateEnd
END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-15 : 17:39:38

You could change the CASE to a COALESCE:

SELECT 
	pbv.*
FROM 
	ProductBonusVendor AS pbv
WHERE 
	pbv.PaymentName = 'ADV PROD BONUS'--@PaymentName
	AND pbv.Active = 'true'
	AND '7/19/2008 12:00:00 AM'/*@CommissionStartDate*/ between pbv.DateStart and COALESCE(pbv.DateEnd, CURRENT_TIMESTAMP)

If you are not getting any records, then I assume that no records match your restriction criteria.

Can you verify that '7/19/2008 12:00:00 AM' is converting to the proper datetime? SELECT CAST('7/19/2008 12:00:00 AM' AS DATETIME)

sql-buzz
Starting Member

7 Posts

Posted - 2008-08-15 : 17:52:57

Hey guys,
Thanks for the reply. But the problem was NULL. I wrote IS NULL and it worked:
Here is the working query:

SELECT
pcv.*

from ProductCommissionVendor pcv
where pcv.ProductID = 3
--and pcv.Active = 'true'
and '7/19/2008 12:00:00 AM'
between pcv.DateStart and
CASE
WHEN pcv.DateEnd IS NULL THEN getdate()
ELSE pcv.DateEnd
END
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources