conditional select statement

[brutis2ka13]

Hello,
Can someone please help me construct a select statement where there could be multiple parameters that are sent to a SPROC?

I have a form with two calander controlls and two dropdown lists. The user should be able to select the datefrom-to-dateto and/or state and/or eventtitle.

The sproc should be able to process the select statement if the user only selects the state or the dates or the event title or all three.

Any help would be great!

[Mike Hildner]

Not sure if this will help, had to do something similar just the other day. In my case, I had to build a WHERE clause if certain text boxes where filled in. Not sure if this is even the right way to do it, but it seems to work anyway.

string sql = "SELECT [Name], DOB, SSN FROM MasterName " +
"WHERE [Name] LIKE @Name";

// Add the DOB clause if filled in.
if (tbDOB.Text != "")
{
sql += " AND DOB = " + tbDOB.Text;

}

// Add the SSN clause if filled in.
// Not really sure why I had to put single quotes around SSN
// and not DOB, but if you remove, it results in bad SQL.
if (tbSSN.Text != "")
{
sql += " AND SSN = '" + tbSSN.Text + "'";
}

// Add the order by clause.
sql += " ORDER BY [Name], DOB";

If you give a little more details - like the variations you have to come up with, that would help.

[jhermiz]

Not sure if this will help, had to do something similar just the other day. In my case, I had to build a WHERE clause if certain text boxes where filled in. Not sure if this is even the right way to do it, but it seems to work anyway.

string sql = "SELECT [Name], DOB, SSN FROM MasterName " +
"WHERE [Name] LIKE @Name";

// Add the DOB clause if filled in.
if (tbDOB.Text != "")
{
sql += " AND DOB = " + tbDOB.Text;

}

// Add the SSN clause if filled in.
// Not really sure why I had to put single quotes around SSN
// and not DOB, but if you remove, it results in bad SQL.
if (tbSSN.Text != "")
{
sql += " AND SSN = '" + tbSSN.Text + "'";
}

// Add the order by clause.
sql += " ORDER BY [Name], DOB";

If you give a little more details - like the variations you have to come up with, that would help.

Dynamic SQL is very bad practice. This should be a stored procedure. Not only is dynamic sql a problem with sql injection but it destroys the security involved with the entire schema.

[jhermiz]

Hello,
Can someone please help me construct a select statement where there could be multiple parameters that are sent to a SPROC?

I have a form with two calander controlls and two dropdown lists. The user should be able to select the datefrom-to-dateto and/or state and/or eventtitle.

The sproc should be able to process the select statement if the user only selects the state or the dates or the event title or all three.

Any help would be great!

To answer your question involves understand of boolean logic
True OR True = True
True Or False = True
False Or True = True
FALSE or FALSE = FAlse

TRUE AND TRUE = True
True AND false = False
False AND True = False
False and Fale = False

In your case you may or may not be wanting to send parameters to a sproc. Simple, SQL allows you to set parameters as having default values. One of the great things is the default can be set to NULL.

So look at this:

CREATE PROCEDURE select_customers @CustomerID bigint=NULL
--notice the customer id is now a parameterized argument taking
--null as the initial value...

--then do the select
SELECT CUstomerID, CustomerName FROM Customers WHERE
(CustomerID=@CustomerID OR @CustomerID IS NULL)

Thats it...
notice that the result is a boolean true or false. If @CustomerID is null it returns all customers, else it returns just htat one customer.

Hope this helps!

Jon

[Mike Hildner]

Dynamic SQL is very bad practice.Agreed. Thanks for the reminder.