Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Server where clause problem

Author  Topic 

maason123
Starting Member

4 Posts

Posted - 2014-07-16 : 06:13:08
I have pretty annoying problem with sql statment. I am using stored procedure (have to) and I can not use dynamic sql.

Let say I have this table (there is blank value instead of NOVALUE - not null):

ID NAME SURNAME MARRIED COMPANY PAYMENTID
---------------------------------------------------
1 Michael Backer 1 Google 1
2 Travis Morgan NOVALUE NOVALUE NOVALUE
3 George Marshall NOVALUE NOVALUE NOVALUE
4 Trevor Rush 1 Microsoft 1
5 John Doe 1 IBM 2

I have two checkboxes on my ASP.NET (C#) site. First is 'Married' and the second is 'Not married'.

If user check only first checbox result are rows with id 1 and 4, if the second checbox is checked then result are rows with id 2 and 3. If both checboxes are checked then result are the whole table (id from 1-4). Keep in mind that I have one serach box which check for some person name, surname, so that is also in sql statment.

How can I manage to do what I want with stored procedure. Any suggestion?

Examples of searching:

User can choose which company and payment is included (always at least one of each) in searching and check checboxes for marital status. I passed company and payment parameters to stored procedure and used it in IN statement.

Example (the most complicated): User include Google and Microosft and all paymentid-s to searching. If both marriage checboxes are checekd (first and second) then results are people with ID 1, 3, and because of second checxbox are checked also 2 and 3 (where company has no value).

Example: User Include all companies and all paymentid-s, and check only second checbox, which says 'not married'. So results are people with ID 2 and 3. In this case you can see that companies and paymentid-s are not important beacuse of blank value.

Example: User Include all companies and paymentsid-s (or not) and checed only those who are married (first checkbox), so results are people who are married and those who works in selected company.

User can in every scenario search after name, surname, etc..

I solved it with three procedures (three possible values if there is two checboxes).

Please help me out.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-16 : 06:26:11
This kind of query - where you may (or may not) have multiple search conditions is best done using dynamic queries for performance reasons. You can write non-dynamic queries as well. See these two articles for details and sample code:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://www.sommarskog.se/dyn-search.html
Go to Top of Page

maason123
Starting Member

4 Posts

Posted - 2014-07-16 : 06:55:47
quote:
Originally posted by James K

This kind of query - where you may (or may not) have multiple search conditions is best done using dynamic queries for performance reasons. You can write non-dynamic queries as well. See these two articles for details and sample code:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://www.sommarskog.se/dyn-search.html


Thank you for your answer and your time. I am getting errors with my dynamic sql. I am using dynamic sql for the first time. Can you write on my example dynamic sql? I really do not have any idea.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-07-16 : 06:59:42
You need to post the existing code to help you

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

maason123
Starting Member

4 Posts

Posted - 2014-07-17 : 04:19:10
I solved it somehow with case when, so I did not need to use dynamic-sql. Thanks anyway.
Go to Top of Page
   

- Advertisement -