quote:

---

Originally posted by StacyOW

Ok if I use the multi valued parameter does it always have to be multi then, because the @StatusValue may sometimes be just one value in the parameter.
Here is what I am doing. It is a search form for the user. They can choose value to search by so if they choose Vendor then they can also choose whether they want Open, Closed or Both for the status field. So of course it works just fine if they choose either Open or Closed but if I want it to bring up both Open and Closed I wasn't sure how to do that. Basically I need the parameter to except 1 value or many values. Is that possible? Can you give me an idea where to find directions for doing such a thing.
Thanks so much for your reply.
Stacy (Newbie)
P.S. this is for a fillby query on a winform not for a report. Or is there a way to send something in the parameter that would allow All values in the field, because that is basically what I am doing. They choose open or closed or I want everything. Which way is easier?

---

quote:

---

Originally posted by StacyOW

I read through your link. I haven't used stored procedures yet. So if I am understanding this I would make stored procedures for all of my different searches or fillby queries then and not use the fillby queries?

Because it looks like your stored procedures are dealing with the parameters and then doing the select query.

Thanks for all your help! Eventually I will understand all this only been working with it for 2 months :)
Stacy
P.S. Using sql server 2008 express r2 if that makes any difference.

---

quote:

---

Originally posted by StacyOW

That was over my head - guess I'm still to much of a newbie to get that to work, so I just made another query and if the user picks "All" then I run that query instead of the other!

Thanks for all your help.

Stacy

---

CREATE PROC dbo.GetPOList
(
@SearchValue varchar(100),
@StatusList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON

SELECT porders.PONum, porders.VendorID, porders.OriginatorID, porders.StatusID, porders.ShippedID, porders.Date, porders.MPONum, porders.DateRequired, 
porders.RefPONum, porders.ShipTo, porders.ShipAddress, porders.ShipCity, porders.ShipPhone, porders.ShipState, porders.ShipZip, porders.Tax, porders.Freight, 
porders.PrintStatus, porders.POTotal, porders.Comments, porders.Description
FROM porders INNER JOIN
items ON porders.PONum = items.PONum INNER JOIN
department ON items.DepartmentID = department.DepartmentID
WHERE (department.Department LIKE @SearchValue + '%') 
AND  ','+ @StatusList + ',' LIKE '%,' + CAST(porders.StatusID AS varchar)(15)) + ',%'


END
GO

CREATE PROC dbo.GetPOList
(
@SearchValue varchar(100),
@StatusList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON

SELECT porders.PONum, porders.VendorID, porders.OriginatorID, porders.StatusID, porders.ShippedID, porders.Date, porders.MPONum, porders.DateRequired, 
porders.RefPONum, porders.ShipTo, porders.ShipAddress, porders.ShipCity, porders.ShipPhone, porders.ShipState, porders.ShipZip, porders.Tax, porders.Freight, 
porders.PrintStatus, porders.POTotal, porders.Comments, porders.Description
FROM porders INNER JOIN
items ON porders.PONum = items.PONum INNER JOIN
department ON items.DepartmentID = department.DepartmentID
WHERE (department.Department LIKE @SearchValue + '%') 
AND  ','+ @StatusList + ',' LIKE '%,' + CAST(porders.StatusID AS varchar(15)) + ',%'


END
GO