Monday, March 19, 2012

Need alittle help with sql statemtn stored procedure

Here is what I am trying to do, If i type to query the stored procedure with two parameters and the two parameters are null, return all rows, else do the query based on the two parameters. Thanks in advance.

Create PROCEDURE [dbo].[Admin_RetrieveCustomerOrders] @.Yearnumint,@.monthnumintASBEGINSET NOCOUNT ON;SELECT o.intOrderId, o.vcCustomerID, o.decTotal, o.dtOrdered,u.vcFirstName, u.vcLastName,(SELECTCOUNT(d.intOrderID)FROM tblOrderDetails dWHERE d.intOrderID = o.intOrderID)AS TotalProductsFROM tblOrders oINNERJOIN tblUserInformation uON o.vcCustomerID = u.vcCustomerIDWHERE YEAR(o.dtOrdered)=@.yearnumANDMONTH(o.dtOrdered)=@.monthnumORDER BY o.dtOrderedDESC END

Thanks Again

Joshua

I think this may work

Essentially you need to modify you WHERE clause to also allow null for the parameters

WHERE (YEAR(o.dtOrdered)=@.yearnumOR @.yearnumisNULL)AND (MONTH(o.dtOrdered)=@.monthnumOR @.monthnumisNULL)
 
Give the above ago and let us know if it works. 

No comments:

Post a Comment