How do I select all records from sql database using parameters default value?

Question

1.00/5 (1 vote)

See more:

What i have is an checkboxlist showing all students for selection of an batch..
Now i have 3 dropdownlist for filtering students according to subjects,percentage,etc...
Is there any keyword (like NULL) or trick to be set as default of select parameter to select all records from database initially then filter according to user input...

Posted 7-Apr-14 10:54am

Pawan Wagh

Add a Solution

2 solutions

Solution 2

You can pass NULL value to parameter and check in store procedure for that value. If it is NULL then not to pass that parameter in query.

If you have more number of parameters, so you can use dynamic SQL.

Eg.

ALTER PROCEDURE [dbo].[TestSP]

@Parameter1 VARCHAR(15) = NULL,
@Parameter2 VARCHAR(15) = NULL,
@Parameter3 VARCHAR(15) = NULL
AS

BEGIN

Declare @wherecond varchar(200)
SET @wherecond = ''
if (@Parameter1 != NULL)
SET @wherecond = ' AND parameter1 = @parameter1'
endif
if (@Parameter2 != NULL)
SET @wherecond = ' AND parameter2 = @parameter2'
endif

-- Final Query
select * from <your table=""> where 1=1+@wherecond

END

Posted 7-Apr-14 18:43pm

Bh@gyesh

Comments

Pawan Wagh 8-Apr-14 0:57am

I understood what u were suggesting me.. thank you for that. but since I am working in asp.net and importing all students name from database in checkboxlist using wizard.....
so I don't know how to achieve this...

Bh@gyesh 8-Apr-14 1:08am

If I understood correctly, you want all records from db (students information) and filter them i ASP.NET side, true?

Pawan Wagh 8-Apr-14 5:49am

YES

Bh@gyesh 8-Apr-14 6:16am

So you can do one thing.
Select all records in datatables in asp.net. then filter records according to conditions.
Please refer following links :

http://stackoverflow.com/questions/13012585/how-i-can-filter-a-datatable
http://www.csharp-examples.net/dataview-rowfilter/

Please mark as answer if you found this useful..

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard C Bishop · Accepted Answer · 2014-04-07T11:34:00

If I understand correctly you can use a stored procedure and if the parameter you pass it is Null, then it will still run the stored procedure. See below:

ALTER PROCEDURE [dbo].[spYourStoredProcedure]
	-- Add the parameters for the stored procedure here
	@Parameter VARCHAR(15) = NULL	
AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	--SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END

The above line of code will be a value of NULL if nothing is passed to the stored procedure. If the Parameter value is NULL, then you check for that before you decide which query to run.