Optional Parameters of the select command

[Crazyec]

Hi, I have a drop down list which refreshing the gridview.
I wanna add a item in drop down list which is for "select all". But the select command in the datasource " Where user_id = @user_id " already.

Any Idea to make it optional?
Or I should use two data source and change the gridview's datasource when I use "select all"?
Or I should use stored procedure?

 

[jbenson001]

Use a stored procedure, I always use them, and it works great for a situation like this, which I have delt with many times.

First, add your "Select All" item to the top of the dropdownlist with a value of 0.

Then create a SP:

Create Procedure usp_GetUser
   @UserID int

AS

--The @userflg variable is used to see if a value other
--than 0("Select All") was chosen.  (0 if 0 was choosen, or
--1 if any other value was choosen.)
declare @userflg bit
set @userflg = 1  --Assume a value other than 0 was passed.

--If 0("Select All") was passed, set the flag to 0.
IF @UserID = 0
   set @userflg = 0

Select 
   *
From 
   Users
Where (@userflg = 1 AND user_id = @UserID) OR
      (@userflg = 0)

The Where clause tests if the flag is 1 and if it is, pull back the user matching the ID passed in. Otherwise, ALL rows are passed back.


Hope this helps and let me know if you have any questions.

Jim