Hi everyone,

I am a complete noob with stored procedures. I was asked to modify the
following stored procedure so that it includes the following
conditions:

IF InvWarehouse.DateLastSale is null then use
InvWarehouse.DateLastStockMove

else

IF InvWarehouse.DateLastStockMove is null then use
InvWarehouse.DateLastSale

else

IF InvWarehouse.DateLastStockMove is null and
InvWarehouse.DateLastSale is null then need to compare to see which
one is the most current and use that one.


This logic needs to be incorporated into the following stored
procedure:

CREATE procedure dbo.sp_SlowMovingInventory_PopulateAdmFormData
as
Begin
Declare @StockCode Varchar(30),
@Count Integer

Declare SlowMoving_Cursor Cursor for Select Distinct StockCode from
InvWarehouse where
Warehouse in ('M1','M2','M4','MS') AND
QtyOnHand <> 0 AND
(DateLastStockMove is Null or (datediff(dd,DateLastStockMove,
getdate()) > 728) )


Open SlowMoving_Cursor
Fetch Next from SlowMoving_Cursor into @StockCode
While @@Fetch_Status = 0
Begin

Select @Count = count(*) from AdmFormData where
FormType = 'STK' and
KeyField = @StockCode and
FieldName = 'DATE'
If @Count = 0
Begin
Insert into AdmFormData (FormType,KeyField,FieldName,DateValue)
Values ('STK',@StockCode,'DATE',getdate())
End
Else if @Count = 1
Begin
Update AdmFormData Set DateValue = getdate() Where
FormType = 'STK' and
KeyField = @StockCode and
FieldName = 'DATE'
End

Fetch Next from SlowMoving_Cursor into @StockCode
End
Close SlowMoving_Cursor
Deallocate SlowMoving_Cursor
End
GO


Any help will be much appreciated

[posted and mailed, please reply in news]

Andrei (slesarka@yahoo.com) writes:
> I am a complete noob with stored procedures. I was asked to modify the
> following stored procedure so that it includes the following
> conditions:
>
> IF InvWarehouse.DateLastSale is null then use
> InvWarehouse.DateLastStockMove
>
> else
>
> IF InvWarehouse.DateLastStockMove is null then use
> InvWarehouse.DateLastSale
>
> else
>
> IF InvWarehouse.DateLastStockMove is null and
> InvWarehouse.DateLastSale is null then need to compare to see which
> one is the most current and use that one.

(This was posted to comp.databases.ibm-db2, but I would expect your
syntax work on DB2. This answer applies to MS SQL Server anyway.)

If both are NULL, then you cannot use any of them. I suppose that you
mean both are non-NULL?

So replace:

> (DateLastStockMove is Null or (datediff(dd,DateLastStockMove,
> getdate()) > 728) )

With:

datediff(dd, CASE WHEN coalesce(DateLastStockMove, '19000101') >
coalesce(DateLastSale, '19000101')
THEN DateLastStockMove
ELSE DateLastSale
END, getdate()) > 728

The coalesce() function returns the first non-NULL value in the list.

> CREATE procedure dbo.sp_SlowMovingInventory_PopulateAdmFormData

The sp_ prefix is reserved for system procedures, and SQL Server first
looks for such a procedure in the master database, so you should use
this for your own procedures.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp