Calling procedure over and over.... can''t work it.
-
FYI: ***** Answer = Case Statement ******
This has been racking my brain for the past few weeks now and I can't seem to find a workable version.
I am doing a SalesHistory query for my company, and I want to retrieve the Customers and how much money we made off of them. Along with that, I want to call the same thing over again, but change the date range. So now it would return, yesterdays, years total, last years total, etc...
And I would like to call this function 6 times in the end.
Customer Todays$ Yesterday$ CurrentYear$ LastYear$ Etc...
My first attempt was to use a dynamically stored procedure. But my varchar is only 4000, ran out of room when using 'EXEC sp_executesql'
My second attempt was to mix in views to cut down on the code, but views with Unions in them slowed things down.
My third attempt was to write it all in ASP - TSQL code (which was the faster by FAR), but I get an error saying I am using over 256 tables. (since i'm recalling the same function so much).
So what can I do ? I'm going around in circles on this one.
-
What about UDF?
dbo.SalesAmount(@CustomerId, @PeriodStart, @PeriodEnd)
SELECT CustomerId,
dbo.SalesAmount(CustomerId, @Today, @Today + 1) as Todays$,
dbo.SalesAmount(CustomerId, @Today - 1, @Today) as Todays$,
dbo.SalesAmount(CustomerId, dateadd(mm, -(Datepart(mm, @Today) + 1, dateadd(dd, -(Datepart(dd, @Today) + 1, @Today)), @Today) as CurrentYear$,
...
FROM Customes
_____________
Code for TallyGenerator -
Post your table structure and your codes and explain your current approach. We should be able to help you to come up with some solution.
-
"And I would like to call this function 6 times in the end."
Why ?
There is a very simple set based solution using CASE.
Using the Northwind demo database:
-- The Sales Summary Table
create table CustomerSales
(CustomerIdnchar(5) not null
,OrderDatedatetime not null
,SaleAmtmoneynot null
, constraint CustomerSales_P primary key (CustomerId, OrderDate)
)
-- Populate the Sales summary table
insert into CustomerSales
(CustomerId
,OrderDate
,SaleAmt
)
SELECT Orders.CustomerId
,Orders.OrderDate
, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100)
AS SaleAmt
FROM Orders
JOIN "Order Details"
ON Orders.OrderID = "Order Details".OrderID
GROUP BY Orders.CustomerId
,Orders.OrderDate
go
-- Stored procedure logic with CASE
Declare @todayDate datetime
,@oldestDate datetime
set @todayDate = '1998-04-02 00:00:00.000'
set @oldestDate = cast( ( YEAR(@todayDate) - 1 ) as char(4) ) + '-01-01'
select customerId
, SUM( CASE when OrderDate = @todayDate then SaleAmt else 0 end ) as TodaySales
, SUM( CASE when OrderDate = @todayDate - 1 then SaleAmt else 0 end ) as YesterdaySales
, SUM( CASE when Year(OrderDate) = year(@todayDate) then SaleAmt else 0 end ) as currentyearSales
, SUM( CASE when Year(OrderDate) = year(@todayDate) - 1 then SaleAmt else 0 end ) as PriorYearSales
from CustomerSales
where OrderDate between @oldestDate and @todayDate
andcustomerid = 'QUICK'
group by CustomerId
SQL = Scarcely Qualifies as a Language
-
>My third attempt was to write it all in ASP - TSQL code (which was the faster by FAR), but I get an error <
Fast error producing statements are always to be prefered ...
CREATE PROCEDURE giveMePain
AS
--To keep things simple and ingenious ;-):
SET NOCOUNT ON
DECLARE @Today DATETIME,
@YesterDay DATETIME,
@FirstDayOfYear DATETIME,
@FirstDayOfLastYear DATETIME
SET @Today = DATEADD(dd,0,DATEDIFF(dd,0,GETDATE()))
SET @YesterDay = DATEADD(dd,-1,@Today)
SET @FirstdayofYear = DATEADD(dd,-DATEPART(dayofyear,@ToDay),@ToDay)
SET @FirstdayofLastYear = DATEADD(yyyy,-1,@FirstdayofYear)
SELECT
CustomerID,
SUM(CASE WHEN SalesDate >= @Today THEN Amount ELSE 0 END) AS Today$,
SUM(CASE WHEN SalesDate >= @Yesterday AND SalesDate < @Today THEN Amount ELSE 0 END) AS Yesterday$,
SUM(CASE WHEN SalesDate >= @FirstdayofYear THEN Amount ELSE 0 END) AS CurrentYear$,
SUM(CASE WHEN SalesDate >= @FirstdayofLastYear AND SalesDate < @FirstdayofYear THEN Amount ELSE 0 END) AS LastYear$
FROM CustomerSales
GROUP BY
CustomerID
This is the basic way. There's of course no need to use variables but this gives you the chance to test seperately and keeps the date calculation out of the statement.
If this is basically what you want, this can of course be improved regarding flexibility. How do you specify your date ranges? ad hoc?
_/_/_/ paramind _/_/_/ -
Oh, Carl Federl, seems like same thoughts in parallel
would not have posted otherwise.
_/_/_/ paramind _/_/_/ -
Wow the Case Statement is very powerful. Thank you guys so much!! I've been racking my brain over this for the past few months trying to speed up how I originally did it. (Using a temp table, calling the procedures 1 at a time and either updates or inserting the records into the temp table).
The Case Statement way is the way to go forsure. Very fast and very simple. One call and search through the table. That’s why I like this board, it gave me a whole new spin on how to look at the query and worked beautifully. Thanks again guys.
When I put this into a query, I always come up with the dilemma of the varchar running out of the 4000 characters, because I use a lot of IF statements. Below is my old Query and I was wondering what is the trick to create a dynamic stored procedure when there is a ton of IF statements. Right now I just split up the SELECT, FROM, WHERE statements then joined them at the end. Is it better just to do all of this in ASP??
-------------------------------------------------------
CREATE PROCEDURE [dbo].[SalesHistory]
@InvoiceDate1 datetime = NULL,
@InvoiceDate2 datetime = NULL,
@PlantType nvarchar(1) = NULL,
@CustomerNumber nvarchar(6) = NULL,
@CustomerName nvarchar(30) = NULL,
@CustomerShipTo nvarchar(6) = NULL,
@CustomerType nvarchar(25) = NULL,
@RMNumber nvarchar(7) = NULL,
@TMNumber nvarchar(7) = NULL,
@PlantNumber nvarchar(25) = NULL,
@ProductGroup nvarchar(25) = NULL,
@PartNumber nvarchar(25) = NULL,
@PartDescription nvarchar(30) = NULL,
@SalesOrderNumber nvarchar(8) = NULL,
@CustomerPONumber nvarchar(30) = NULL,
@ProductCode nvarchar(4000) = NULL,
@SICCode nvarchar(4000) = NULL,
@Columns nvarchar(200) = NULL,
@Sort nvarchar(100) = NULL,
@Roll nvarchar(1) = NULL,
@PCodeGrp nvarchar(1) = NULL
--WITH RECOMPILE
AS
DECLARE
@sql nvarchar(4000),
@sqlFrom nvarchar(4000),
@sqlWhere nvarchar(4000),
@sql_CYDR nvarchar(4000),
@sql_LYDR nvarchar(4000),
@sql_CYTD nvarchar(4000),
@sql_LYTD nvarchar(4000),
@sql_TPRE nvarchar(4000),
@param nvarchar(2000),
@CurDate datetime, -- Current Date
@LastDate datetime, -- Last Year Date
@InvoiceDate1_CYDR datetime, -- Current Year to Date Range
@InvoiceDate2_CYDR datetime,
@InvoiceDate1_LYDR datetime, -- Last Year to Date Range
@InvoiceDate2_LYDR datetime,
@InvoiceDate1_CYTD datetime, -- Current Year to Date
@InvoiceDate2_CYTD datetime,
@InvoiceDate1_LYTD datetime, -- Last Year to Date
@InvoiceDate2_LYTD datetime,
@InvoiceDate1_CYA datetime, -- Current Year Annualized
@InvoiceDate2_CYA datetime,
@InvoiceDate1_TPRE datetime, -- Total Previous Year
@InvoiceDate2_TPRE datetime,
@FiscalYear int
set ansi_warnings off
Select @sql = '', @sqlFrom = '', @sqlWhere = '', @param = ''
SELECT @CurDate = Getdate()
SELECT @LastDate = DateAdd(yy, -1, @CurDate)
Select @FiscalYear = 0
If Month(@CurDate) < 7
Select @FiscalYear = 1
-- Current Year to Date Range
SELECT @InvoiceDate1_CYDR = @InvoiceDate1
SELECT @InvoiceDate2_CYDR = @InvoiceDate2
-- Last Year to Date Range
SELECT @InvoiceDate1_LYDR = DateAdd(yy, -1, @InvoiceDate1_CYDR)
SELECT @InvoiceDate2_LYDR = DateAdd(yy, -1, @InvoiceDate2_CYDR)
-- Current Year to Date
SELECT @InvoiceDate1_CYTD = '7/1/'+ convert(varchar, Year(@CurDate)-@FiscalYear)
SELECT @InvoiceDate2_CYTD = Convert(varchar,@CurDate,101)
-- Last Year to Date
SELECT @InvoiceDate1_LYTD = '7/1/'+ convert(varchar, Year(@LastDate)-@FiscalYear)
SELECT @InvoiceDate2_LYTD = Convert(varchar,@LastDate,101)
-- Total Previous Year
SELECT @InvoiceDate1_TPRE = @InvoiceDate1_LYTD
SELECT @InvoiceDate2_TPRE = '6/30/' + convert(varchar, Year(@InvoiceDate1_LYTD)+1)
CREATE TABLE dbo.#temp_Sales
(
CustomerNumber varchar(6),
CustomerName varchar(30),
CustomerShipTo varchar(6),
PlantNumber varchar(2),
ProductCode varchar(5),
ProductGroupCode varchar(1),
ProductGroupCodeDef varchar(30),
CYDR float,
LYDR float,
CYTD float,
LYTD float,
TPRE float
 
-- ******************************
-- SUMMARY MANUFACTURING SALES
-- ******************************
IF (@PlantType <> 'W')
begin
SELECT @sql = 'INSERT #temp_Sales (CustomerNumber, CustomerName, CustomerShipTo, PlantNumber, ProductCode, ProductGroupCode, ProductGroupCodeDef, CYDR, LYDR, CYTD, LYTD, TPRE )
SELECT S.CustomerSoldTo, ISNULL(AR.CustomerName, S.CustomerNameSoldTo), S.CustomerShipTo, S.PlantNumber, ISNULL(MPC.CorporateNumberCode,''-blk-''),
ISNULL(WPC.ProductGroupCode,''Z''), ISNULL(PGC.ProductGroupDescription,''-null-''), '
SELECT @sqlFrom = 'FROM DataWarehouse.dbo.MfgShipments S
LEFT OUTER JOIN CustomerMaster.dbo.ARMaster AR ON S.CustomerSoldTo = AR.CustomerNumber
LEFT OUTER JOIN DataWarehouse.dbo.MfgProductCodes MPC ON S.ProductCode = MPC.ProductCode AND MPC.QuantityBreaks = ''''
LEFT OUTER JOIN DataWarehouse.dbo.WhseProductCodes WPC ON MPC.CorporateNumberCode = WPC.ProductCode
LEFT OUTER JOIN Reference.dbo.R_ProductGroupCode PGC ON WPC.ProductGroupCode = PGC.ProductGroupCode '
SELECT @sqlWhere = ' WHERE (S.ActualTransactedDate BETWEEN @xInvoiceDate1 AND @xInvoiceDate2) AND (S.QuantityOrdered <> 0) '
IF @CustomerNumber IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (S.CustomerSoldTo = @xCustomerNumber) '
IF @CustomerName IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (AR.CustomerName LIKE ''%''+ @xCustomerName + ''%'' ) '
IF @CustomerShipTo IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (S.CustomerShipTo = @xCustomerShipTo) '
IF @CustomerType IS NOT NULL
begin SELECT @sqlFrom = @sqlFrom + 'LEFT OUTER JOIN CustomerMaster.dbo.WhseDivBillTo DBT ON S.CustomerSoldTo = DBT.CustomerNumber '
SELECT @sqlWhere = @sqlWhere + 'AND (' + dbo.Split(LTRIM(RTRIM(@CustomerType)),'DBT.CustomerType',',') + ' ) ' end
IF @RMNumber IS NOT NULL OR @TMNumber IS NOT NULL OR @SICCode IS NOT NULL
begin SELECT @sqlFrom = @sqlFrom + 'LEFT OUTER JOIN CustomerMaster.dbo.CustMasterDetail CM ON S.CustomerSoldTo = CM.CustomerNumber '
IF @RMNumber IS NOT NULL
begin SELECT @sqlFrom = @sqlFrom + 'LEFT OUTER JOIN StaffProfiles.dbo.StaffProfiles SPRMAuto ON CM.RMAuto = SPRMAuto.VoiceMailNumber LEFT OUTER JOIN StaffProfiles.dbo.StaffProfiles SPRMHyd ON CM.RMHyd = SPRMHyd.VoiceMailNumber LEFT OUTER JOIN StaffProfiles.dbo.StaffProfiles SPRMTruck ON CM.RMTruck = SPRMTruck.VoiceMailNumber '
SELECT @sqlWhere = @sqlWhere + ' AND EXISTS (SELECT ProductCode FROM Reference.dbo.ProductResponsibility PR INNER JOIN StaffProfiles.dbo.StaffProfiles SP ON PR.DivisionType = SP.DivisionType WHERE SP.ClockNumber = @xRMNumber AND ProductCode = MPC.CorporateNumberCode) '
SELECT @sqlWhere = @sqlWhere + ' AND (SPRMAuto.ClockNumber = @xRMNumber OR SPRMHyd.ClockNumber = @xRMNumber OR SPRMTruck.ClockNumber = @xRMNumber ) ' end
IF @TMNumber IS NOT NULL
begin SELECT @sqlFrom = @sqlFrom + 'LEFT OUTER JOIN StaffProfiles.dbo.StaffProfiles SPTMAuto1 ON CM.TMAuto1 = SPTMAuto1.VoiceMailNumber LEFT OUTER JOIN StaffProfiles.dbo.StaffProfiles SPTMAuto2 ON CM.TMAuto2 = SPTMAuto2.VoiceMailNumber LEFT OUTER JOIN StaffProfiles.dbo.StaffProfiles SPTMHyd1 ON CM.TMHyd1 = SPTMHyd1.VoiceMailNumber
LEFT OUTER JOIN StaffProfiles.dbo.StaffProfiles SPTMHyd2 ON CM.TMHyd2 = SPTMHyd2.VoiceMailNumber LEFT OUTER JOIN StaffProfiles.dbo.StaffProfiles SPTMMobile ON CM.TMMobile = SPTMMobile.VoiceMailNumber LEFT OUTER JOIN StaffProfiles.dbo.StaffProfiles SPTMTruck ON CM.TMTruck = SPTMTruck.VoiceMailNumber LEFT OUTER JOIN StaffProfiles.dbo.StaffProfiles SPTMClimate ON CM.TMClimate = SPTMClimate.VoiceMailNumber '
SELECT @sqlWhere = @sqlWhere + ' AND EXISTS (SELECT ProductCode FROM Reference.dbo.ProductResponsibility PR INNER JOIN StaffProfiles.dbo.StaffProfiles SP ON PR.DivisionType = SP.DivisionType WHERE SP.ClockNumber = @xTMNumber AND ProductCode = MPC.CorporateNumberCode) '
SELECT @sqlWhere = @sqlWhere + ' AND (SPTMAuto1.ClockNumber = @xTMNumber OR SPTMAuto2.ClockNumber = @xTMNumber OR SPTMHyd1.ClockNumber = @xTMNumber OR SPTMHyd2.ClockNumber = @xTMNumber OR SPTMMobile.ClockNumber = @xTMNumber OR SPTMTruck.ClockNumber = @xTMNumber OR SPTMClimate.ClockNumber = @xTMNumber ) ' end
IF @SICCode IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (' + dbo.Split(LTRIM(RTRIM(@SICCode)),'CM.SICCode2',',') + ' ) ' end
IF @PlantNumber IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (' + dbo.Split(LTRIM(RTRIM(@PlantNumber)),'S.PlantNumber',',') + ' ) '
IF @ProductGroup IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (' + dbo.Split(LTRIM(RTRIM(@ProductGroup)),'WPC.ProductGroupCode',',') + ' ) '
IF @PartNumber IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (S.PartNumber LIKE @xPartNumber + ''%'' ) '
IF @PartDescription IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (MPC.ProductDescription LIKE ''%''+ @xPartDescription +''%'' ) '
IF @SalesOrderNumber IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (S.OrderNumber = @xSalesOrderNumber ) '
IF @CustomerPONumber IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (S.PurchaseOrderBillTo LIKE @xCustomerPONumber +''%'' ) '
IF @ProductCode IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (' + dbo.Split(LTRIM(RTRIM(@ProductCode)),'MPC.CorporateNumberCode',',') + ' ) '
SELECT @sqlWhere = @sqlWhere + 'GROUP BY S.CustomerSoldTo, ISNULL(AR.CustomerName, S.CustomerNameSoldTo), S.CustomerNameSoldTo, S.CustomerShipTo, S.PlantNumber, ISNULL(MPC.CorporateNumberCode,''-blk-''), ISNULL(WPC.ProductGroupCode,''Z''), ISNULL(PGC.ProductGroupDescription,''-null-'') '
SELECT @param =
'@xInvoiceDate1 datetime,
@xInvoiceDate2 datetime,
@xCustomerNumber nvarchar(6),
@xCustomerName nvarchar(30),
@xCustomerShipTo nvarchar(6),
@xRMNumber nvarchar(7),
@xTMNumber nvarchar(7),
@xPartNumber nvarchar(25),
@xPartDescription nvarchar(30),
@xSalesOrderNumber nvarchar(8),
@xCustomerPONumber nvarchar(18)'
Select @sql = Replace(@sql,'''','''''')
Select @sqlFrom = Replace(@sqlFrom,'''','''''')
Select @sqlWhere = Replace(@sqlWhere,'''','''''')
SELECT @sql_CYDR = @sql +' SUM(S.NetAmount), 0, 0, 0, 0 '+ @sqlFrom + @sqlWhere
EXEC('EXEC sp_executesql N''' + @sql + ' SUM(S.NetAmount), 0, 0, 0, 0 ' + @sqlFrom + @sqlWhere + ''', N''' + @param +''', '''+ @InvoiceDate1_CYDR +''', '''+ @InvoiceDate2_CYDR +''', '''+ @CustomerNumber +''', '''+ @CustomerName +''', '''+ @CustomerShipTo +''', '''+ @RMNumber +''', '''+ @TMNumber +''', '''+ @PartNumber +''', '''+ @PartDescription +''', '''+ @SalesOrderNumber +''', '''+ @CustomerPONumber +''' ')
end
-
Why do you want to have it dynamic? Why not just compile it?
_/_/_/ paramind _/_/_/ -
If it is because of 'unknown' IF-statements, do the following:
WHERE
(@SICCode IS NULL OR @SICCode = dbo.Split(... ,CM.SICCode2))
AND (@PlantNumber IS NULL OR @PlantNumber = dbo.Split( ...,CM.Plantnumber))
AND (@ProductGroup IS NULL OR @ProductGroup =
... and so on, I think you get the idea. Instead of testing the parameter for request, simply alter the condition in such it evaluates to true in case it is omitted,
BUT though it works, one good advice: you'd better replace the parameter nulls with some never-existing field value as a default, e.g. -1.
If the respective fields never take the value of -1 (just an example, there may be different defaults for different parameters)
you could test for -1 instead of nulls, so you don't run into situations, where you may have failed TO PASS the parameter correctly (from some application).
WHERE
(@SICCode = -1 OR @SICCode = dbo.Split(...)
AND (@PlantNumber = -1 OR @PlantNumber = dbo.Split(...)
AND (@ProductGroup = -1 OR @ProductGroup = ...
Does that solve the problem? 😉
_/_/_/ paramind _/_/_/ -
The reason I wanted to make it dynamic is because most of the time, they only search the records by a few varibiles, but there is about 20 to pick from.
Select @sql = 'Select CustomerNumber from SalesHistory where 1=1 '
IF @PartNumber IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (S.PartNumber LIKE @xPartNumber + ''%'' ) '
IF @PartDescription IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (MPC.ProductDescription LIKE ''%''+ @xPartDescription +''%'' ) '
IF @SalesOrderNumber IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (S.OrderNumber = @xSalesOrderNumber ) '
IF @CustomerPONumber IS NOT NULL
SELECT @sqlWhere = @sqlWhere + 'AND (S.PurchaseOrderBillTo LIKE @xCustomerPONumber +''%'' ) '
Exec @sql + @sqlWhere
How else can you say that for the where clause ??
paramid, I'm not sure what you mean, how will that effect my query ?
WHERE
(@SICCode IS NULL OR @SICCode = dbo.Split(... ,CM.SICCode2))
AND (@PlantNumber IS NULL OR @PlantNumber = dbo.Split( ...,CM.Plantnumber))
AND (@ProductGroup IS NULL OR @ProductGroup =
-
>paramid, I'm not sure what you mean, how will that effect my query ?<
In case a parameter is not specified (indicated by being NULL or alternatively some default) that part of the where clause will always evaluate to true, which means it has no effect on your selection. (mind the parantheses though) Don't worry about performance (especially compared to dynamic sql). I'm pretty sure, the query optimizer will never evaluate the second part of one where condition if the first is already true and it will always evaluate the simple expression - that is the variable check (@parameter IS NULL) first.
If this is still not clear, feel free to contact me on msn-messenger.
_/_/_/ paramind _/_/_/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply