We help IT Professionals succeed at work.

SQl Parameter Conditions

sanjshah12
sanjshah12 asked
on
Medium Priority
429 Views
Last Modified: 2012-08-30
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
Comment
Watch Question

Commented:
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. :) )

Author

Commented:
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!
Commented:
Okay, so create a stored procedure in the database and call it from the front end (C# or VB).  The result will be the same as you normally run the query.

Here is a sample stored proc using dynamic SQL:
CREATE PROCEDURE dbo.spt_getCompanyInfo
{
	@system varchar(100);
	@service varchar(100);
	@company varchar(100)
}
AS
BEGIN
	-- declare string for query
	DECLARE @query varchar(2000);

	-- set initial part of query string
	set @query = 'SELECT id, company, service, system 
            FROM tbl_records 
            WHERE 1=1 ';

	-- add where clause to query string
	IF (LEN(@system) > 0)
	BEGIN
		set @query = @query + 'and System = ''' + @system + ''' ';
	END

	IF (LEN(@service) > 0)
	BEGIN
		set @query = @query + 'and Service = ''' + @service + ''' ';
	END

	IF (LEN(@company) > 0)
	BEGIN
		set @query = @query + 'and Company = ''' + @company + ''' ';
	END

	-- wrap up query with order by
	set @query = @query + 'order by company ';

	-- run query (will return your data set just like normal)
	exec (@query);
END

Open in new window

Basically, just build the query string like any other string variable.  Then execute it using the EXEC command.
Some notes on the stored procedure:
When initially setting the query string, I establish the WHERE clause using 1=1 so that all parameters passed in will add an AND to the query string (so you don't need to know which is the first or second parameter added to the query.
If you are using any dates or numbers, you'll need to cast/convert them to a string to add it to the query string.
The use of the two single quotes in a row results in putting a single quote being put into the query string, which is required for any SQL statement.

NOTE: IMO, it is better coding practice to put ALL SQL in the database; either in a stored procedure or scalar function.  I find it neater and easier to maintain so if there is a change to the database table, thus requiring a change to your select statement, then you don't need to recompile and redeploy your .net application.

Author

Commented:
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 WarrenApplications Developer

Commented:
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)