CODEWITHSUNDEEP
sqlsql-serverdrop-down-menuoptional-parameters
Kapil Dev
Kapil Dev

Reputation: 11

Optional query parameters in SQL Server

I want to fetch data from a SQL Server database using values from a dropdown list.

My query is 

select  Age,City,State,Caste,IncomeMin,IncomeMax from Ruser
where (Age between '" + drplistagemin.SelectedItem + "' and '" + drplistagemax.SelectedItem + "') 
and (Religion= '" + drplistreligion.SelectedItem + "')  ");

What I need to understand is how to build this query if the value of the Religion dropdown is optional rather than compulsory?

Upvotes: 0

Views: 2275

Answers (3)

msi77
msi77

Reputation: 1632

In the notation of @Pratik:

SELECT Age,City,State,Caste,IncomeMin,IncomeMax
FROM Ruser
WHERE age BETWEEN @minAge AND @maxAge
AND religion = coalesce(@religion, religion);

Upvotes: 1

marc_s
marc_s

Reputation: 754248

One way to do it would be to use a NULL value for the religion, and translate that into a % for a LIKE comparison in SQL Server.

Also - I would always separate the UI code (event handler etc.) from the actual database access code - so do something like this in a separate DataAccess class (and not stick this directly into the page code-behind) :

public List<RuserResults> GetRuserResults(int minAge, int maxAge, string religion)
{
    string selectStmt = "SELECT Age, City, State, Caste, IncomeMin, IncomeMax FROM Ruser " +
                        "WHERE Age BETWEEN @MinAge AND @MaxAge " + 
                        "AND Religion LIKE @religion";

    // set up your connection and command objects
    using(SqlConnection conn = new SqlConnection("--your-connection-string-here--"))
    using(SqlCommand cmd = new SqlCommand(selectStmt, conn))
    {
        // define the parameters
        cmd.Parameters.Add("@MinAge", SqlDbType.Int).Value = minAge;
        cmd.Parameters.Add("@MaxAge", SqlDbType.Int).Value = maxAge;
        cmd.Parameters.Add("@Religion", SqlDbType.VarChar, 100);

        // if you passed a value for the method parameter - use that value
        if(!string.IsNullOrEmpty(religion))
        {
           cmd.Parameters["@Religion"].Value = religion + "%";
        }
        else  // if no value was passed - just search for all religions
        {
           cmd.Parameters["@Religion"].Value = "%";
        }

        List<RuserResult> results = new List<RuserResult>();

        // open connection, run query, close connection
        conn.Open();

        using(SqlDataReader reader = cmd.ExecuteReader())
        {
           while(reader.Read())
           {
               // read the values, convert to a "RuserResults", and pass it back
               results.Add(ConvertReaderToRuserResult(reader));
           }
        }

        conn.Close();

        // return the results
        return results;
    }
}

And then from your ASP.NET page, you can call this 

int minAge = Convert.ToInt32(drplistagemin.SelectedItem);
int maxAge = Convert.ToInt32(drplistagemax.SelectedItem);
string religion = drplistreligion.SelectedItem;

List<RuserResult> results = GetRuserResults(minAge, maxAge, religion);

// do something with the results returned here....

Upvotes: 0

Pratik
Pratik

Reputation: 1512

I never recommend using direct command methods but you can give try to below query:

SELECT Age,City,State,Caste,IncomeMin,IncomeMax
FROM Ruser
WHERE age BETWEEN @minAge AND @maxAge
AND religion LIKE CASE WHEN @religonVal IS NULL THEN '%' ELSE @religonVal END;

Please note : You can used appropriate values to the variables from stored procedure. Let me know if it works as you want.

Upvotes: 0

Related Questions