CODEWITHSUNDEEP
sqlsql-server
Flood Gravemind
Flood Gravemind

Reputation: 3801

Get all records in SQL depending on user input

I am just learning SQL and came across a difficulty. My data table looks like this

DBO DATA

Id | Name | Surname | Title  | Location
----------------------------------------
1  | xxx  | abc     | def    | London
2  | xxx  | abc     | def    | Oslo
3  | xxx  | abc     | def    | New York

Now I want to get id, name, title and surname and I use query like this:

SELECT 
    Id, Name, Surname, Title 
FROM 
    DATA 
WHERE 
    Location = 'London' -- (this is user input)

But I get problem if the user inputs All or World or an empty string in WHERE clause, I want the query to return all rows. Is it possible to do this with writing another query to handle special scenarios like the above????

I am using ASP.net with a datasource like this 

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
     ConnectionString="<%$ ConnectionStrings:MainConnectionString %>" 
     SelectCommand="SELECT [Name], [Id], [Surname], [Title] FROM [DATA] WHERE [Location] = @Location)" >
   <SelectParameters>
       <asp:ControlParameter ControlID="Label1" Name="Location" 
            PropertyName="Text" Type="String" />
   </SelectParameters>
</asp:SqlDataSource>`

The input depends on the route path of URL localhost:56789/People/London/

The corresponding VB file is 

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If (Request.PathInfo.Length = 0) Then

    Else
        Dim t = Request.PathInfo.Substring(1)
        Label1.Text = t
    End If
End Sub

I know I can do this application side but I am just curios if this is possible in query itself.

Upvotes: 1

Views: 3982

Answers (4)

Karl
Karl

Reputation: 3372

I don't have a SQL Server instance here to confirm this sytax on but you could do something like;

SELECT 
    Id, Name, Surname, Tittle 
FROM 
    data 
WHERE 
   case 
      when <input> = 'All' then 1=1 
      when <input> = 'World' then 1=1
      else location = <input>
   end

How this works is it makes use the fact that each predicate (in effect where clause) has top evaluate to TRUE. 1=1 is always true so you will get all rows in the table. and of course location = will filter.

Upvotes: 2

alecxe
alecxe

Reputation: 474003

If you really want to do it in your query, try this:

SELECT 
    Id, Name, Surname, Tittle 
FROM 
    data 
WHERE 
    Location = <input> OR
    <input> in ('All', 'World', '')

Upvotes: 2

Martin Drautzburg
Martin Drautzburg

Reputation: 5253

You can simply add

or userInput in ('ALL','World','')

to your where clause, where userInput is the input from the user.

Upvotes: 2

Niels B.
Niels B.

Reputation: 6310

What are you saying is that you want to apply some rules on what query to perform based on what the user enters. That is outside the scope of SQL.

It sounds like you are developing an application that takes input from the user. It's the job of that application to examine that input to determine what SQL query to run against the database. So check your input and do a SELECT * FROM DATA in the event your users enter nothing, "all" or "world".

Upvotes: 1

Related Questions