November 14, 2008 at 6:34 am
Hi All,
After searching the web for a solution I just seem to be more confused - If someone could throw some light on this I would really appreciate it - as my sql isn't brill as you will see..
I have a sql SP which accepts 2 params:
@SalesOrderID int = 0,
@AllocationEndDate datetime
The select statement contain various joins from orders & allocations tables.
Its the main where clause that I am having issues with - If I pass a SalesOrderID into this SP I want the where to match this ID along with some date checking & a trans type. If I dont pass a SalesOrderID I want the where to just match on the date checking & a trans type.
See below the code which I have & that doesnt work:-
IF @SalesOrderID = 0BeginWhere (M.DateTransactionDue >= '01/01/1900' AND M.DateTransactionDue = '01/01/1900') AND (M.DateTransactionDue <= @AllocationEndDate) AND (M.TransactionTypeID = 1)END
I did try a Case statement I couldn't get that to work either:-
--WHERE --(case @SalesOrderID--when 0 M.DateTransactionDue >= '01/01/1900' AND M.DateTransactionDue = '01/01/1900') AND (M.DateTransactionDue <= @AllocationEndDate) AND -- (M.TransactionTypeID = 1) -- else -1 -- end)
Many thanks
Janet
November 14, 2008 at 6:48 am
It can be achived by building the sql statement dynamically and then executing the SQL statement with sp_executeSQL @statement
Please let me know if you need any more help
November 14, 2008 at 6:54 am
I am calling the stored procedure directly from C# (which is my area of expertise). This will be called from many places within code - so ideally I would like the the 'where clause' to be handled within the stored procedure.
Below are the details of the SP:-
ALTER PROCEDURE dbo.U_SorTransaction_SelectAllBackOrders
(
@SalesOrderID int = 0,
@AllocationEndDate datetime
)
AS
SET NOCOUNT ON;
SELECT M.SalesOrderID,
M.TransactionID,
M.Node,
M.ItemID,
M.Qty,
M.WarehouseID,
M.PackID,
M.SalesBundle,
M.ParentTransactionID,
(case isnull(M.DateTransactionDue, '01/01/1900') WHEN '01/01/1900' THEN '01/01/1900' ELSE M.DateTransactionDue END) as DateTransactionDue,
ISNULL(SA.QtyAllocated, 0) AS QtyAllocated, (CASE (ISNULL(ST.QtyOrdered, 0) - ISNULL(SA.QtyAllocated, 0))
WHEN 0 THEN 0 ELSE 1 END) AS OnBackOrder_AllocationURNDataTable, ISNULL(ST.QtyOrdered, 0) AS Qty_TransactionDataTable,
M.TransactionTypeID
FROM SorTransaction AS M
LEFT OUTER JOIN
(SELECT SorTransactionID, SUM(Qty) AS QtyAllocated
FROM StoAllocationUrn
WHERE (SourceID = 6)
GROUP BY SorTransactionID
UNION
SELECT SorTransactionID, SUM(Qty) AS QtyAllocated
FROM StoAllocationCreditUrn
WHERE (SourceID = 6)
GROUP BY SorTransactionID) AS SA ON SA.SorTransactionID = M.TransactionID
LEFT OUTER JOIN
(SELECT SalesOrderID, SUM(Qty) AS QtyOrdered
FROM SorTransaction
WHERE (TransactionTypeID <> 3)
GROUP BY SalesOrderID) AS ST ON ST.SalesOrderID = M.SalesOrderID
IF @SalesOrderID = 0
Begin
Where
(M.DateTransactionDue >= '01/01/1900' AND M.DateTransactionDue <= @AllocationEndDate) AND M.TransactionTypeID = 1
END
ELSE
Begin
Where
(M.SalesOrderID = @SalesOrderID) AND (M.DateTransactionDue >= '01/01/1900') AND (M.DateTransactionDue <= @AllocationEndDate) AND
(M.TransactionTypeID = 1)
END
November 14, 2008 at 7:04 am
Create PROCEDURE dbo.U_SorTransaction_SelectAllBackOrders
(
@SalesOrderID int = 0,
@AllocationEndDate datetime
)
AS
SET NOCOUNT ON;
Declare @Select as varchar(1000)
Declare @Where as varchar(1000)
set @select='SELECT M.SalesOrderID,
M.TransactionID,
M.Node,
M.ItemID,
M.Qty,
M.WarehouseID,
M.PackID,
M.SalesBundle,
M.ParentTransactionID,
(case isnull(M.DateTransactionDue, ''01/01/1900'') WHEN ''01/01/1900'' THEN ''01/01/1900'' ELSE M.DateTransactionDue END) as DateTransactionDue,
ISNULL(SA.QtyAllocated, 0) AS QtyAllocated, (CASE (ISNULL(ST.QtyOrdered, 0) - ISNULL(SA.QtyAllocated, 0))
WHEN 0 THEN 0 ELSE 1 END) AS OnBackOrder_AllocationURNDataTable, ISNULL(ST.QtyOrdered, 0) AS Qty_TransactionDataTable,
M.TransactionTypeID
FROM SorTransaction AS M
LEFT OUTER JOIN
(SELECT SorTransactionID, SUM(Qty) AS QtyAllocated
FROM StoAllocationUrn
WHERE (SourceID = 6)
GROUP BY SorTransactionID
UNION
SELECT SorTransactionID, SUM(Qty) AS QtyAllocated
FROM StoAllocationCreditUrn
WHERE (SourceID = 6)
GROUP BY SorTransactionID) AS SA ON SA.SorTransactionID = M.TransactionID
LEFT OUTER JOIN
(SELECT SalesOrderID, SUM(Qty) AS QtyOrdered
FROM SorTransaction
WHERE (TransactionTypeID <> 3)
GROUP BY SalesOrderID) AS ST ON ST.SalesOrderID = M.SalesOrderID'
IF @SalesOrderID = 0
Begin
set @Where=' Where (M.DateTransactionDue >= ''01/01/1900'' AND M.DateTransactionDue <= @AllocationEndDate) AND M.TransactionTypeID = 1'
END
ELSE
Begin
set @Where=' Where
(M.SalesOrderID = @SalesOrderID) AND (M.DateTransactionDue >= ''01/01/1900'') AND (M.DateTransactionDue <= @AllocationEndDate) AND
(M.TransactionTypeID = 1)'
END
set @Select=@select + @where
Exec sp_executeSQL @Select
Please try this and let me know if it works in this way
November 14, 2008 at 7:09 am
HI,
I understand what you meant now! dur
But I dont under stand the output when I try & execute it....
Running [dbo].[U_SorTransaction_SelectAllBackOrders] ( @SalesOrderID = 557152, @AllocationEndDate = 11/14/2008 ).
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[U_SorTransaction_SelectAllBackOrders].
November 14, 2008 at 7:15 am
modify these below statement as given below
from
----------
Declare @Select as varchar(1000)
Declare @Where as varchar(1000)
to
-------
Declare @Select as nvarchar(2000)
Declare @Where as nvarchar(1000)
and then try again
November 14, 2008 at 7:25 am
After putting in your last mod, I get 2 different results:-
Pass Zero & 11/14/2008:-
Running [dbo].[U_SorTransaction_SelectAllBackOrders] ( @SalesOrderID = 0, @AllocationEndDate = 11/14/2008 ).
Must declare the variable '@AllocationEndDate'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[U_SorTransaction_SelectAllBackOrders].
Pass 557152 & 11/14/2008:-
Running [dbo].[U_SorTransaction_SelectAllBackOrders] ( @SalesOrderID = 557152, @AllocationEndDate = 11/14/2008 ).
Must declare the variable '@SalesOrderID'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[U_SorTransaction_SelectAllBackOrders].
November 14, 2008 at 8:38 am
Alter PROCEDURE dbo.U_SorTransaction_SelectAllBackOrders
(
@SalesOrderID int = 0,
@AllocationEndDate datetime
)
AS
SET NOCOUNT ON;
Declare @Select as nvarchar(2000)
Declare @Where as nvarchar(1000)
set @select='SELECT M.SalesOrderID,
M.TransactionID,
M.Node,
M.ItemID,
M.Qty,
M.WarehouseID,
M.PackID,
M.SalesBundle,
M.ParentTransactionID,
(case isnull(M.DateTransactionDue, ''01/01/1900'') WHEN ''01/01/1900'' THEN ''01/01/1900'' ELSE M.DateTransactionDue END) as DateTransactionDue,
ISNULL(SA.QtyAllocated, 0) AS QtyAllocated, (CASE (ISNULL(ST.QtyOrdered, 0) - ISNULL(SA.QtyAllocated, 0))
WHEN 0 THEN 0 ELSE 1 END) AS OnBackOrder_AllocationURNDataTable, ISNULL(ST.QtyOrdered, 0) AS Qty_TransactionDataTable,
M.TransactionTypeID
FROM SorTransaction AS M
LEFT OUTER JOIN
(SELECT SorTransactionID, SUM(Qty) AS QtyAllocated
FROM StoAllocationUrn
WHERE (SourceID = 6)
GROUP BY SorTransactionID
UNION
SELECT SorTransactionID, SUM(Qty) AS QtyAllocated
FROM StoAllocationCreditUrn
WHERE (SourceID = 6)
GROUP BY SorTransactionID) AS SA ON SA.SorTransactionID = M.TransactionID
LEFT OUTER JOIN
(SELECT SalesOrderID, SUM(Qty) AS QtyOrdered
FROM SorTransaction
WHERE (TransactionTypeID <> 3)
GROUP BY SalesOrderID) AS ST ON ST.SalesOrderID = M.SalesOrderID'
IF @SalesOrderID = 0
Begin
set @Where=' Where (M.DateTransactionDue >= ''01/01/1900'' AND M.DateTransactionDue <=' + @AllocationEndDate + ') AND M.TransactionTypeID = 1'
END
ELSE
Begin
set @Where=' Where
(M.SalesOrderID = ' + @SalesOrderID + ') AND (M.DateTransactionDue >= ''01/01/1900'') AND (M.DateTransactionDue <=' + @AllocationEndDate + ') AND
(M.TransactionTypeID = 1)'
END
set @Select=@select + @where
Exec sp_executeSQL @Select
=============
Now try this 🙂
November 14, 2008 at 8:46 am
:crying:Soz - still getting 2 errors depending on the param's:-
Running [dbo].[U_SorTransaction_SelectAllBackOrders] ( @SalesOrderID = 557152, @AllocationEndDate = 11/14/2008 ).
Syntax error converting the varchar value ' Where
(M.SalesOrderID = ' to a column of data type int.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[U_SorTransaction_SelectAllBackOrders].
and
Running [dbo].[U_SorTransaction_SelectAllBackOrders] ( @SalesOrderID = 0, @AllocationEndDate = 11/14/2008 ).
Syntax error converting datetime from character string.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[U_SorTransaction_SelectAllBackOrders].
November 14, 2008 at 9:08 am
You don't really need dynamic SQL for this. Try the following as your WHERE clause:
WHERE M.DateTransactionDue >= '01/01/1900' AND M.DateTransactionDue <= @AllocationEndDate) AND M.TransactionTypeID = 1
AND M.SalesOrderID = CASE WHEN @SalesOrderID = 0 THEN M.SalesOrderID ELSE @SalesOrderID END
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 17, 2008 at 1:12 am
Thanks Chris.
I have implemented your code & its works just fine.
Although building dynamically could be useful in other areas, but not in this case.
Cheers to All...
Janet 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply