Folk, I have a simple question regarding passing optional criteria in stored procedures. Basically, my users have 8 options -- 5 from drop down lists and 3 from text boxes. Users have to use one option from a particular drop down list before they can use any other options. What I am needing to do is search my database depending on the options the users have selected and I need the sproc to disregard the options the users have not selected. The current stored procedure I have is as follows. As you can see, it only lets user chose one more option other than the compulsory choice option. Is there any way I can pass optional parameters in my stored procedure depending on what the users have or have not selected?


CREATE PROCEDURE PPAlbumSearch
(
    @artist nvarchar(120),
    @title nvarchar(120),
    @label nvarchar(120),
    @category nvarchar(60),
    @event nvarchar(60),
    @rhythm nvarchar(60),
    @level nvarchar(60),
    @type nvarchar(60)
)

AS


SELECT
    AlbumID,
    ProductID,
    SongCDispArtist,
    SongWebTitle,
    SongMedia,
    SongLabel,
    AlbumPrice

FROM
    PPAlbumSongs

WHERE
    AlbumCategory=@category
    AND

    (SongCDispArtist LIKE '%' + @artist + '%'
    OR
    SongWebTitle LIKE '%' + @title + '%'
    OR
    SongLabel LIKE '%' + @label + '%'
    OR
    SongCDispEvent=@event
    OR
    SongCDispRhythm=@rhythm
    OR
    SongCDispLevel=@level
    OR
    SongCDispType=@type)

ORDER BY
    SongCDispArtist
GO
__________________________________________

Thanks very much for your help.

Hi there,

You can pass for the parameter you don't want to use and then use this code in your SELECT statement:

WHERE SongCDispArtist LIKE '%' + @artist + '%' OR @artist is null

When the param is not null, it will filter the SongCDispArtist column, based on @artist. Otherwise, @artist will be null, the second clause is true, and all records are returned.

You can apply the same principle to all your other parameters.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: full nelson by Limp Bizkit (Track 4 from the album: Chocolate Starfish and the Hotdog Flavoured Water) What's This?

There is a little trick to accomplish this and that is to use a NULL parameter value to mean "don't care". That is, if the parameter has a value of NULL, then you don't care what value is in the associated database column - you'll take any value at all.

You then use the COALESCE function for each WHERE clause as:
Note all the conditions are ANDed together.

The way this works is that the COALESCE function returns the first non NULL value in its argument list. If the parameter is not NULL, then the test is to compare the column value to the parameter. If the parameter is NULL, though, then the result of the COALESCE is that the column value is compared to itself, which is always true, so this is in effect a no-op (or "don't care").


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thank you very much guys. I will use your suggestions sometime later tonight US east cost and will let you know the results. Shailesh(first) Mark(last).

Hi Imar
are you interested in the football (soccer) scene? Is Van Basten still playing? He was my favourite striker long time back when I used to be young. I have been completely cut-off from the football world since I moved to the US five years ago.

(Can I mark this thread as Off topic somewhere?? ;) )

No, he's no longer playing. I think he's a trainer somewhere now, although I don't know for which club.

You just missed Euro 2004, the European Championship. We made it to the semi-finals, but were eventually kicked out of the tournament by the Portugese. If you want to catch up: http://www.euro2004.com/index.html

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

(last off-toppic reply)
Thats too bad. I still remember Euro 88 watching it with my Dad in India when Holland played Russia in the finals or was it semi-finals and Van Basten scored two of the most phenomenal goals I have ever seen -- one was a bicycle kick he struck while almost parallel to the goal line from outside the D area. I think he was playing for PHV Eindhoven then. My Dad liked Ruud Gullit and Frank Rikard (forgive the spellings -- it is almost 16 years) but I always thought Marco Van Basten was the best striker then or ever at least in terms of style and panache -- I think the closest anybody came to him was Cannigia of Argentina.
I can't believe Portugal has come thus far and unbelievable still is the fact that Greece beat them in the finals. Greece? Portugal? Who would have thought this ten years ago?
Anyway, no more off topic posts -- apologies.

Ah, those were the days. Yes, I remember 1988 very well too.
Unfortunately, we didn't make it that far this year. Oh well.... ;)

Last off-topic post....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Butterflies & Hurricanes by Muse (Track 10 from the album: Absolution) What's This?

Hi Imar
Tried your optional criteria stored procedure. Well, now it is bringing up everything that belongs to a particularly category -- regardless of whatever other criteria is chosen. Choosing anything else is not making any difference because it is bringing out everything that belongs to that category. "Category" is a set of options from my drop down list ddlCategory. It has four options -- Ballroom & RoundDance, C/W & Clogging & JukeBox, Contra/Quadrille, Square Dance.
After users have made one selection from the category dropdownlist with options named above, they can go ahead and use options from other drop down lists or enter their search in text boxes, namely, artist, label, or title.

So now I select a category say Ballroom & Round Dance, and then choose from another option from a dropdownlist named rhythm, say foxtrot. what it is doing is it brings out every record under the category Ballroom & Round Dance instead of limiting the selection to the rhythm foxtrot.

Here is my code behind for my music search page if you are interested and following it is the code behind for my music results page. Thanks for taking a look and sorry if have been repetitive above. I am sure I am missing something. Well, first let me post the sproc that I changed following your suggestion:

CREATE PROCEDURE PPAlbumSearch
(
    @artist nvarchar(120),
    @title nvarchar(120),
    @label nvarchar(120),
    @category nvarchar(60),
    @event nvarchar(60),
    @rhythm nvarchar(60),
    @level nvarchar(60),
    @type nvarchar(60)
)

AS


SELECT
    AlbumID,
    ProductID,
    SongCDispArtist,
    SongWebTitle,
    SongMedia,
    SongLabel,
    AlbumPrice

FROM
    PPAlbumSongs

WHERE
    AlbumCategory=@category
    AND

    (SongCDispArtist LIKE '%' + @artist + '%' OR @artist is null
    AND
    SongWebTitle LIKE '%' + @title + '%' OR @title is null
    AND
    SongLabel LIKE '%' + @label + '%' OR @label is null
    AND
    SongCDispEvent=@event OR @event is null
    AND
    SongCDispRhythm=@rhythm OR @rhythm is null
    AND
    SongCDispLevel=@level OR @level is null
    AND
    SongCDispType=@type OR @type is null)

ORDER BY
    SongCDispArtist
GO
__________________________________________________ ____
music search page

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace perrysplace
{
    /// <summary>
    /// Summary description for ppmusicsearch.
    /// </summary>
    public class ppmusicsearch : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DropDownList ddlCategory;
        protected System.Web.UI.WebControls.Button btnSearch;
        protected System.Web.UI.WebControls.TextBox txtArtist;
        protected System.Web.UI.WebControls.TextBox txtTitle;
        protected System.Web.UI.WebControls.TextBox txtLabel;
        protected System.Web.UI.WebControls.DropDownList ddlEvent;
        protected System.Web.UI.WebControls.DropDownList ddlRhythm;
        protected System.Web.UI.WebControls.DropDownList ddlLevel;
        protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;
        protected System.Web.UI.WebControls.DropDownList ddlType;
        protected System.Data.SqlClient.SqlConnection cnMusic;
        protected System.Data.SqlClient.SqlCommand cmdSelect;

        public ppmusicsearch()
        {
            Page.Init += new System.EventHandler(Page_Init);
        }

        private void Page_Load(object sender, System.EventArgs e)
        {
            // Put user code to initialize the page here
            if (!IsPostBack)
            {
                cnMusic.Open();
                cmdSelect = new SqlCommand ("Select AlbumCategory From PPAlbumCategory Order By AlbumCategory", cnMusic);
                SqlDataAdapter daCategory = new SqlDataAdapter(cmdSelect);
                DataSet dsCategory = new DataSet();
                daCategory.Fill(dsCategory);
                ddlCategory.DataSource = dsCategory;
                ddlCategory.DataTextField = "AlbumCategory";
                ddlCategory.DataBind();
                ddlCategory.Items.Insert(0,"");
                ddlCategory.SelectedIndex = 0;

                cmdSelect = new SqlCommand ("Select AlbumEvent From PPAlbumEvent Order By AlbumEvent", cnMusic);
                SqlDataAdapter daEvent = new SqlDataAdapter(cmdSelect);
                DataSet dsEvent = new DataSet();
                daEvent.Fill(dsEvent);
                ddlEvent.DataSource = dsEvent;
                ddlEvent.DataTextField = "AlbumEvent";
                ddlEvent.DataBind();
                ddlEvent.Items.Insert(0, "");
                ddlEvent.SelectedIndex = 0;

                cmdSelect = new SqlCommand ("Select AlbumRhythm From PPAlbumRhythm Order By AlbumRhythm", cnMusic);
                SqlDataAdapter daRhythm = new SqlDataAdapter(cmdSelect);
                DataSet dsRhythm = new DataSet();
                daRhythm.Fill(dsRhythm);
                ddlRhythm.DataSource = dsRhythm;
                ddlRhythm.DataTextField = "AlbumRhythm";
                ddlRhythm.DataBind();
                ddlRhythm.Items.Insert(0, "");
                ddlRhythm.SelectedIndex = 0;

                cmdSelect = new SqlCommand ("Select AlbumLevel From PPAlbumLevel Order By AlbumLevel", cnMusic);
                SqlDataAdapter daLevel = new SqlDataAdapter(cmdSelect);
                DataSet dsLevel = new DataSet();
                daLevel.Fill(dsLevel);
                ddlLevel.DataSource = dsLevel;
                ddlLevel.DataTextField = "AlbumLevel";
                ddlLevel.DataBind();
                ddlLevel.Items.Insert(0, "");
                ddlLevel.SelectedIndex = 0;

                cmdSelect = new SqlCommand ("Select AlbumType From PPAlbumType Order By AlbumType", cnMusic);
                SqlDataAdapter daType = new SqlDataAdapter(cmdSelect);
                DataSet dsType = new DataSet();
                daType.Fill(dsType);
                ddlType.DataSource = dsType;
                ddlType.DataTextField = "AlbumType";
                ddlType.DataBind();
                ddlType.Items.Insert(0, "");
                ddlType.SelectedIndex = 0;

                cnMusic.Close();
            }


        }

        public string artistName
        {
            get
            {
                return txtArtist.Text;
            }
        }

        public string albumTitle
        {
            get
            {
                return txtTitle.Text;
            }
        }

        public string albumLabel
        {
            get
            {
                return txtLabel.Text;
            }
        }

        public string albumCategory
        {
            get
            {
                return ddlCategory.SelectedItem.Text;
            }
        }

        public string albumEvent
        {
            get
            {
                return ddlEvent.SelectedItem.Text;
            }
        }

        public string albumRhythm
        {
            get
            {
                return ddlRhythm.SelectedItem.Text;
            }
        }

        public string albumLevel
        {
            get
            {
                return ddlLevel.SelectedItem.Text;
            }
        }

        public string albumType
        {
            get
            {
                return ddlType.SelectedItem.Text;
            }
        }

        public void btnSearch_Click(object sender, System.EventArgs e)
        {
            Response.Redirect("ppmusicresults.aspx?albumArtist =" +
                this.txtArtist.Text + "&albumTitle=" +
                this.txtTitle.Text + "&albumLabel=" +
                this.txtLabel.Text + "&albumCategory=" +
                Server.UrlEncode(this.ddlCategory.SelectedItem.Tex t) + "&albumEvent=" +
                Server.UrlEncode(this.ddlEvent.SelectedItem.Text) + "&albumRhythm=" +
                Server.UrlEncode(this.ddlRhythm.SelectedItem.Text) + "&albumLevel=" +
                Server.UrlEncode(this.ddlLevel.SelectedItem.Text) + "&albumType=" +
                Server.UrlEncode(this.ddlType.SelectedItem.Text));
        }

        public void Page_Init(object sender, EventArgs e)
        {
            InitializeComponent();
        }
}
}

____________________________________________
musicresults page

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace perrysplace
{
    /// <summary>
    /// Summary description for PPMusicResults.
    /// </summary>
    public class PPMusicResults : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DataGrid dgMusic;
        protected System.Web.UI.WebControls.Label lblItems;
        protected System.Web.UI.WebControls.Label ErrorMsg;
        protected System.Data.SqlClient.SqlConnection cnMusic;

        private void Page_Load(object sender, System.EventArgs e)
        {
            // Put user code to initialize the page here
            if (!Page.IsPostBack)
            {
                bindGrid();
            }

        }

        public void bindGrid()
        {
            SqlCommand cmdSelect = new SqlCommand("PPAlbumSearch", cnMusic);

            cmdSelect.CommandType = CommandType.StoredProcedure;

            cmdSelect.Parameters.Add("@artist", Request.QueryString["albumArtist"]);
            cmdSelect.Parameters.Add("@title", Request.QueryString["albumTitle"]);
            cmdSelect.Parameters.Add("@label", Request.QueryString["albumLabel"]);
            cmdSelect.Parameters.Add("@category", Request.QueryString["albumCategory"]);
            cmdSelect.Parameters.Add("@event", Request.QueryString["albumEvent"]);
            cmdSelect.Parameters.Add("@rhythm", Request.QueryString["albumRhythm"]);
            cmdSelect.Parameters.Add("@level", Request.QueryString["albumLevel"]);
            cmdSelect.Parameters.Add("@type", Request.QueryString["albumType"]);

            DataSet dsMusic = new DataSet();
            cnMusic.Open();
            SqlDataAdapter daMusic = new SqlDataAdapter(cmdSelect);
            daMusic.Fill(dsMusic);
            dgMusic.DataSource = dsMusic;
            dgMusic.DataBind();

            cnMusic.Close();

            if (dgMusic.Items.Count == 0)
            {
                ErrorMsg.Text = "No Items Matched Your Search.";
            }
            else
            {
                lblItems.Text = "Page " + ( dgMusic.CurrentPageIndex+1) + " of " + dgMusic.PageCount;
            }
        }
        public void processCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
        {
            if (e.CommandName.Equals("GetMusicDetails"))
            {
                Server.Transfer("PPMusicDetails.aspx?AlbumID=" +
                    e.CommandArgument.ToString());
            }

        }

        private void pageChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEvent Args e)
        {
            dgMusic.CurrentPageIndex = e.NewPageIndex;
            bindGrid();

        }
}
}

Note that the precedence of logical operators means AND is evaluated before OR.

You'll need to add parenthesis to insure your WHERE clauses are evaluated in the correct order. Thus:

a OR b AND c OR d

is evaluated as

a OR (b AND c) OR d

which probably isn't what you want...


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com