April 30, 2009 at 9:42 am
I have a webform where the user sees a dropdown with sales column like NetSales, WeeklySales, DailySales and so on. Whatever user selects, I take this value and pass it as a parameter to a stored procedure that would then get the average sales for the column they select from the dropdown. To make this work, I am first trying to get the stored procedure executed. But I have been getting errors.
Here is my sp.
ALTER PROCEDURE SA_STPR_Reports_AverageSales
@startDate datetime,
@endDate datetime,
@SearchTerm varchar(255)
AS
BEGIN
SELECTu.FirstName + ' ' + u.LastName AS Name,
s.WeekOf,
AVG(CAST(@SearchTerm AS decimal)) AS AvgSales
FROMSales s
INNER JOIN User_Stores us ON us.StoreId = s.StoreId
INNER JOIN Users u ON u.UserId = us.UserId
INNER JOIN User_Roles ur ON ur.UserId = u.UserId
WHEREs.WeekOf BETWEEN @startDate AND @endDate
AND@SearchTerm IS NOT NULL
ANDur.RoleId = 3
Group BY u.FirstName, u.LastName, s.WeekOf
END
When I try to execute this sp by passing some parameters on sql server 2005 query window, I get the following error.
SA_STPR_Reports_AverageSales '4/1/2009', '5/1/2009', 'WeeklySales'
Errormessage : Error converting data type varchar to numeric.
April 30, 2009 at 11:13 am
You will not be able to do this without creating some dynamic SQL inside the procedure...
Something like this might work...
ALTER PROCEDURE SA_STPR_Reports_AverageSales
@startDate datetime,
@endDate datetime,
@SearchTerm varchar(255)
AS
BEGIN
DECLARE @SQL NVARCHAR(4000)
SELeCT @sql = '
SELECT u.FirstName + '' '' + u.LastName AS Name,
s.WeekOf,
AVG(CAST( ' + @SearchTerm + 'AS decimal)) AS AvgSales
FROM Sales s
INNER JOIN User_Stores us ON us.StoreId = s.StoreId
INNER JOIN Users u ON u.UserId = us.UserId
INNER JOIN User_Roles ur ON ur.UserId = u.UserId
WHERE s.WeekOf BETWEEN @startDate AND @endDate
AND ' + @SearchTerm + ' IS NOT NULL
AND ur.RoleId = 3
Group BY u.FirstName, u.LastName, s.WeekOf'
EXECUTE @SQL
END
But remember you are modifying the query inside of the procedure. You may see some performance issues based on the size of data you are working with as you are modifying the Where statement with various requests... One thing that may help would be to remove the IS NOT NULL requirement. Average disregards NULL values as per BOL. Also, just as a side note, make sure you think about not just dates, but the time portion of your end date since you are using between.
-Luke.
April 30, 2009 at 11:56 am
Thanks Luke, I will try this and will let you know.
April 30, 2009 at 12:07 pm
Hey Luke,
I get this error when I execute the stored proc.
The name 'SELECT u.FirstName + ' ' + u.LastName AS Name,
s.WeekOf,
AVG(CAST( WeeklySalesAS decimal)) AS AvgSales
FROM Sales s
INNER JOIN User_Stores us ON us.StoreId = s.StoreId
INNER JOIN Users u ON u.UserId = us.UserId
INNER JOIN User_Roles ur ON ur.UserId = u.UserId
WHERE s.WeekOf BETWEEN @startDate AND @endDate
AND WeeklySales IS NOT NULL
AND ur.RoleId = 3
Group BY u.FirstName, u.LastName, s.WeekOf' is not a valid identifier.
April 30, 2009 at 12:13 pm
April 30, 2009 at 12:23 pm
Yeah it was the space and the @sql was to be wrapped around a parenthesis. I did that and now it says it can't find the @startDate and @endDate.
ALTER PROCEDURE SA_STPR_Reports_AverageSales
@startDate datetime,
@endDate datetime,
@SearchTerm varchar(255)
AS
BEGIN
DECLARE @SQL NVARCHAR(4000)
SELeCT @sql = '
SELECT u.FirstName + '' '' + u.LastName AS Name,
s.WeekOf,
AVG(CAST( ' + @SearchTerm + ' ' + 'AS decimal)) AS AvgSales
FROM Sales s
INNER JOIN User_Stores us ON us.StoreId = s.StoreId
INNER JOIN Users u ON u.UserId = us.UserId
INNER JOIN User_Roles ur ON ur.UserId = u.UserId
WHERE s.WeekOf BETWEEN @startDate AND @endDate
AND ' + @SearchTerm + ' IS NOT NULL
AND ur.RoleId = 3
Group BY u.FirstName, u.LastName, s.WeekOf'
EXECUTE (@SQL)
END
April 30, 2009 at 12:34 pm
Oh right... silly me... Sorry I didn't test the syntax cause I didn't have any test data.
The start and end dates will not be in the same scope as the rest of it... You'll need to add them by ending the string ' + @variable + ' continuing the string...
Like with the other variable...
One of the reasons why Dynamic SQL is kinda a pain in the arse...
Another way to do this which may or may not work well in your environment might be to
a) ditch the is null check in your where clause cause it's not affecting the results of your Avg statement. b) write a single select statement that calculates all of your averages and selects them all = to appropriately named variables (Again not sure how well this might perform) c) In an if Statement, associate the appropriate variable to your output parameter
At least you wouldn't need to worry with recompiles and bad plans when the dynamic SQL changes in your proc that way...
-Luke.
April 30, 2009 at 3:32 pm
Thanks for the post Luke. I did try the other way to make this work. Though, it has turned out to be a long query, but I think I am okay with this for now. The performance would be okay since its looking for the average data, so this query won't return too many rows. I will try to update this in the future. Right now, it looks at what parameter(what field) was passed in, based on that it will grab the average of that field. So, if a dropdown has 12 fields, I have to write the same script 12 times the only difference was the field that was passed.
This will work for now. Thanks for trying.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply