August 26, 2008 at 5:17 am
Hi,
I've created an SP that selects data based on 2 variables that are passed to it. One is a user name (varchar) and the other is a date (datetime).
The SP is as follows:
ALTER PROCEDURE dbo.usp_Stock_Receipts
@receiptDate smalldatetime,
@originatorName varchar(50)
AS
SELECT
sr.DisplayNo,
sr.DateReceipted,
sr.OriginatorName,
sri.ItemCode,
sri.Description,
sri.QuantityReceipted,
s.Title,
srl.BatchID,
srl.ActualCost
FROM
DSDBA.StockReceipts sr JOIN
DSDBA.StockReceiptItems sri ON sr.GUID = sri.ReceiptGUID JOIN
DSDBA.Stores s ON sr.StoreGUID = s.GUID JOIN
DSDBA.StockReceiptItemLocations srl ON sri.GUID = srl.StockReceiptItemGUID
WHERE
sr.OriginatorName = @originatorName AND
sr.DateReceipted LIKE '%@receiptDate%'
GO
When I pass 2008 as the variable I get nothing back even though I know there are several records where the DateReceipted year is 2008.
EXECUTE dbo.usp_ECL_Stock_Receipts '2008', 'John Smith'
GO
I am sure its something simple so just need to be pointed in the right direction.
Many thanks in advance,
Richard
August 26, 2008 at 5:28 am
richard (8/26/2008)
Hi,I've created an SP that selects data based on 2 variables that are passed to it. One is a user name (varchar) and the other is a date (datetime).
The SP is as follows:
ALTER PROCEDURE dbo.usp_Stock_Receipts@receiptDate smalldatetime,
@originatorName varchar(50)
AS
SELECT
sr.DisplayNo,
sr.DateReceipted,
sr.OriginatorName,
sri.ItemCode,
sri.Description,
sri.QuantityReceipted,
s.Title,
srl.BatchID,
srl.ActualCost
FROM
DSDBA.StockReceipts sr JOIN
DSDBA.StockReceiptItems sri ON sr.GUID = sri.ReceiptGUID JOIN
DSDBA.Stores s ON sr.StoreGUID = s.GUID JOIN
DSDBA.StockReceiptItemLocations srl ON sri.GUID = srl.StockReceiptItemGUID
WHERE
sr.OriginatorName = @originatorName AND
sr.DateReceipted LIKE '%@receiptDate%'
GO
When I pass 2008 as the variable I get nothing back even though I know there are several records where the DateReceipted year is 2008.
EXECUTE dbo.usp_ECL_Stock_Receipts '2008', 'John Smith'GO
I am sure its something simple so just need to be pointed in the right direction.
Many thanks in advance,
Richard
Hi Richard,
The logic that you have implemented is a bit faulty. See if you want to fetch the rows pertaining to a particular year, regardless of the month and date, there is no point of accepting a datetime value. You can simply accept an integer and match it with the year of your column.
Refer to the following example:
ALTER PROCEDURE dbo.usp_Stock_Receipts
@receiptYear int,
@originatorName varchar(50)
AS
SELECT
sr.DisplayNo,
sr.DateReceipted,
sr.OriginatorName,
sri.ItemCode,
sri.Description,
sri.QuantityReceipted,
s.Title,
srl.BatchID,
srl.ActualCost
FROM
DSDBA.StockReceipts sr JOIN
DSDBA.StockReceiptItems sri ON sr.GUID = sri.ReceiptGUID JOIN
DSDBA.Stores s ON sr.StoreGUID = s.GUID JOIN
DSDBA.StockReceiptItemLocations srl ON sri.GUID = srl.StockReceiptItemGUID
WHERE
sr.OriginatorName = @originatorName AND
year(sr.DateReceipted) =@receiptYear
GO
August 26, 2008 at 5:41 am
Thanks Arjun, that is useful but I perhaps should have been more thorough with my detailing.
The stored procedure will be executed by a report. The user will pass two parameters, one will be the user name and the other will be a date in the form dd/mm/yyyy. When I use that though, I still don't get any results back. How should I be passing a date to the SP?
August 26, 2008 at 5:48 am
Smalldatetimes have full day, month, year and time included in them. When you pass 2008 to the procedure, it gets interpreted as '2008-01-01 00:00', and so when you do the like, you're essentially doing this comparison:
sr.DateReceipted LIKE '%2008-01-01 00:00%'
which is only going to match the 1st of Jan at midnight, not any date in 2008
Arjun's solution is one way of doing it, though it will prevent any index usage if you have an index on DateReceipted. The below is another option.
ALTER PROCEDURE dbo.usp_Stock_Receipts
@receiptDate char(4),
@originatorName varchar(50)
AS
DECLARE @StartDate datetime, @EndDate datetime
SET @StartDate = CAST(@receiptDate + '-01-01' AS DATETIME)
SET @EndDate = DATEADD(yy,1,@StartDate)
SELECT
sr.DisplayNo,
sr.DateReceipted,
sr.OriginatorName,
sri.ItemCode,
sri.Description,
sri.QuantityReceipted,
s.Title,
srl.BatchID,
srl.ActualCost
FROM
DSDBA.StockReceipts sr JOIN
DSDBA.StockReceiptItems sri ON sr.GUID = sri.ReceiptGUID JOIN
DSDBA.Stores s ON sr.StoreGUID = s.GUID JOIN
DSDBA.StockReceiptItemLocations srl ON sri.GUID = srl.StockReceiptItemGUID
WHERE
sr.OriginatorName = @originatorName AND
sr.DateReceipted >= @StartDate AND sr.DateReceipted < @EndDate
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2008 at 5:50 am
richard (8/26/2008)
How should I be passing a date to the SP?
Bear in mind that datetimes are just that. Dates and Times.
If a user passes 2008/08/18 to your procedure, what do you want returning?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2008 at 5:58 am
GilaMonster (8/26/2008)
richard (8/26/2008)
How should I be passing a date to the SP?Bear in mind that datetimes are just that. Dates and Times.
If a user passes 2008/08/18 to your procedure, what do you want returning?
Thanks Gail. If they were to pass 2008/08/18 I would want everything on that day regardless of time.
August 26, 2008 at 6:03 am
Ok, then try something like this:
ALTER PROCEDURE dbo.usp_Stock_Receipts
@receiptDate datetime,
@originatorName varchar(50)
AS
DECLARE @StartDate datetime, @EndDate datetime
SET @StartDate = dateadd(dd, datediff(dd,0, @receiptDate),0) -- to ensure that the time portion is midnight
SET @EndDate = DATEADD(dd,1,@StartDate)
SELECT
sr.DisplayNo,
sr.DateReceipted,
sr.OriginatorName,
sri.ItemCode,
sri.Description,
sri.QuantityReceipted,
s.Title,
srl.BatchID,
srl.ActualCost
FROM
DSDBA.StockReceipts sr JOIN
DSDBA.StockReceiptItems sri ON sr.GUID = sri.ReceiptGUID JOIN
DSDBA.Stores s ON sr.StoreGUID = s.GUID JOIN
DSDBA.StockReceiptItemLocations srl ON sri.GUID = srl.StockReceiptItemGUID
WHERE
sr.OriginatorName = @originatorName AND
sr.DateReceipted >= @StartDate AND sr.DateReceipted < @EndDate
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2008 at 6:14 am
I get the following error:
Msg 8114, Level 16, State 5, Procedure usp_ECL_Stock_Receipts, Line 0
Error converting data type varchar to datetime.
Thanks for your help so far!
August 26, 2008 at 6:36 am
What parameters are you using?
One problem with using the yyyy/mm/dd format is that it's ambiguous. If I write 2008/04/10 does that mean the 10th of April or the 4th of October.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2008 at 6:56 am
GilaMonster (8/26/2008)
What parameters are you using?One problem with using the yyyy/mm/dd format is that it's ambiguous. If I write 2008/04/10 does that mean the 10th of April or the 4th of October.
I used....
EXECUTE dbo.usp_ECL_Stock_Receipts '25/06/2008', 'John Smith'
GO
Yes, it may be best to have three seperate ones; Day, Month, Year, and then concatenate them.
What do you think?
August 26, 2008 at 7:20 am
SQL's probably gone and interpertted that as the 6th day of the 25th month.
No need for separate parameters, just a couple small changes.
ALTER PROCEDURE dbo.usp_Stock_Receipts
@receiptDate varchar(20),
@originatorName varchar(50)
AS
DECLARE @StartDate datetime, @EndDate datetime
SET @StartDate = dateadd(dd, datediff(dd,0, CONVERT(DATETIME, @receiptDate, 103)),0) -- to ensure that the time portion is midnight
SET @EndDate = DATEADD(dd,1,@StartDate)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2008 at 7:23 am
Or you can pass dates according ISO standard?
EXECUTE dbo.usp_ECL_Stock_Receipts '20080625', 'John Smith'
never fails.
N 56°04'39.16"
E 12°55'05.25"
August 26, 2008 at 12:26 pm
convert the date time to varchar values and trim the extra data off. e.g 2008/09/31
if you convert it, say, CONVERT(Varchar(12),receiptedyear) you should get 09/31/2008.
After getting that, trim all the leading characters until you get to the 2008.
you can set the parameter to the derived character.
See below
ALTER PROCEDURE dbo.usp_Stock_Receipts
@receiptDate smalldatetime,
@originatorName varchar(50)
AS
SET @receiptDate = SUBSTRING(CONVERT(VARCHAR(12),receiptedyear,101),7,4)
SELECT
sr.DisplayNo,
sr.DateReceipted,
sr.OriginatorName,
sri.ItemCode,
sri.Description,
sri.QuantityReceipted,
s.Title,
srl.BatchID,
srl.ActualCost
FROM
DSDBA.StockReceipts sr JOIN
DSDBA.StockReceiptItems sri ON sr.GUID = sri.ReceiptGUID JOIN
DSDBA.Stores s ON sr.StoreGUID = s.GUID JOIN
DSDBA.StockReceiptItemLocations srl ON sri.GUID = srl.StockReceiptItemGUID
WHERE
sr.OriginatorName = @originatorName AND
sr.DateReceipted LIKE '%@receiptDate%'
GO
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply