asked on

SQl Parameter Conditions

Hi,

I am using the sqldatasource to to bind to a gridview, using some QueryStringParameters such as:

                <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                    SelectCommand="SELECT [id], [Company], [Service], [System] FROM [tbl_records] WHERE (([System] = @System) AND ([Service] = @Service) AND ([Company] = @Company))">
                    <SelectParameters>
                        <asp:QueryStringParameter DefaultValue="nothing" Name="System" QueryStringField="System"
                            Type="String" />
                        <asp:QueryStringParameter DefaultValue="nothing" Name="Service" QueryStringField="Service"
                            Type="String" />
                        <asp:QueryStringParameter DefaultValue="nothing" Name="Company" 
                            QueryStringField="Company" Type="String" />
                    </SelectParameters>
                </asp:SqlDataSource>

Open in new window

However I would like to make the QueryStringParameter optional so that the querystring could be just the room and service, or just room or a combination of any.

can I add a if statement to the sql?

Regards,

Sanjay

nkish

I have handled this in two ways.
1. You can create a Stored Procedure in the database that contains your SQL statement. Then, using dynamic SQL, you can check if the parameters have a value. If they do, you'll include them in the SQL and run it.
2. Add the parameters in C# during the [Selecting] method (you'll need to remove them from the SqlDataSource control properties to achieve this). Then in the [Selecting] method, you can use C# to check for the values and set only the parameters that have values. (NOTE: Although, the SqlDataSource might still be expecting all the parameters here.)

I can provide further examples on either of these if you need. (The dynamic SQL is easier than it sounds, I promise. :) )

sanjshah12

ASKER

Thanks for your response, I don't have a preference to either method and am happy to create a strored proc so would be very greatful if you provided an example preferably in VB.Net.

Thanks in advance!

ASKER CERTIFIED SOLUTION

nkish

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sanjshah12

ASKER

Thanks for your quick response,

from the stored proc I understand how this works and also you are correct in keeping the coding within the DB not on the page (especially as you need to recomplie it if it is a web application!)

I'll test and get back tio you if I have any problems!

Alan Warren

Hi sanjshah12,

Handle the null querystring params in the SQL by defining the input params with a default value of null. Then in the where condition if the input param is null return records where the field value equals the field value itself, if not null, return records matching the input param. This could even be extended to return records 'Like' the input param if required.

Not sure about the defaultvalue=Nothing predicate, that may not be needed at all but have left it in for you.

Something like this may do the trick for you:

<asp:SqlDataSource ID="MainFindSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SJs_Main_Find" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="nothing" Name="System" QueryStringField="System"
Type="String" />
<asp:QueryStringParameter DefaultValue="nothing" Name="Service" QueryStringField="Service"
Type="String" />
<asp:QueryStringParameter DefaultValue="nothing" Name="Company"
QueryStringField="Company" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

And the Sproc:

CREATE PROCEDURE [dbo].[SJs_Main_Find]

       @System VARCHAR(50) = NULL -- modify these types to suit
       ,@Service VARCHAR(50) = NULL
       ,@Company VARCHAR(50) = NULL

AS
BEGIN

      SELECT [id], [Company], [Service], [System]
      FROM [tbl_records]
      WHERE [System] = case when ISNULL(@System,'') = '' then [System] else @System end
      AND [Service] = case when ISNULL(@Service,'') = '' then [Service] else @Service end
      AND [Company] = case when ISNULL(@Company,'') = '' then [Company] else @Company end

-- Using Like comparisons
-- WHERE [System] Like case when ISNULL(@System,'') = '' then '%' else '%' + @System + '%' end
END

Alan ";0)