I know how to code the program and my sproc when the number of parameters is known, however I don't know how to do it when the number of parameters varies (especially how to do it in the sproc).
Is anyone familiar with this?
CREATE PROCEDURE dbo.MyStoredProc
(
@Field1 varchar(50) = NULL,
@Field2 varchar(50) = NULL
)
AS
SELECT
*
FROM
MyTable
WHERE
Field1 = COALESCE(@Field1, Field1) AND
Field2 = COALESCE(@Field2, Field2)
GO
so in that example, you could pass in either one of the parameters, neither of the parameters or both of them and the search would be done accordingly. Hope that helps.
Dynamically might be the way to go, only because this is for a Contact Management app. The parameters are basically releationship types. The users have the ability to add more types and there are already 24.
There is an excellent chance I'm going about this the wrong way. What I want them to be able to do is check some boxes that have the appropriate type, click a button and use that to build a Dataset.
I've set it up using your example, but I can see where it might 'break' probably because I didn't think this through clearly enough.
If you have any suggestion on how I best configure this, please don't hesitate.
Thanks for the help!
Robert
'Items Table
ItemID
ItemName
'Items Selected Table
UserID
ItemID
to find out which items the user currently has, just join the two tables and make sure to select all rows from the Items Table, then wherever there is a NULL field, you'll know the item is not selected, otherwise, they have selected it
to update changes to the database, insert a record (if it doesn't already exist) if the item is checked and delete the item from the Items Selected Table if it is not checked.
semi-complicated, but 100% expandible and reliable.
I think I can have an array, but I'm not sure how to build it. I'm also not sure how to build the sproc so that I don't have to physically add a parameter each time (which would be impossible since They can create their own categories and tag contacts).
I must be thinking about the coding part the wrong way. Is there an article on this that anyone knows of?
Thanks for your help Erik. I had never before seen the COALESCE statement.
Robert
If each item that they have selected is a row in a table, you only need to pass one item in at a time, so I'm not sure why you're trying to pass in all of them at once through one stored procedure. You should actually just have one procedure that takes in all the fields for ONE selected item, then call it over and over for however many items are selected.
Sorry, I think this is too specific and I doubt there are any articles on it.
So if I have a CheckedListBox and multiple items are checked, I can have the procedure loop through it?
I think I can figure it out from there. I somehow got turned upside down on this. Thanks for the patience, you're a good man.
Robert
