Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQl Parameter Conditions

Posted on 2012-08-14
5
Medium Priority
?
378 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
0
Comment
Question by:sanjshah12
  • 2
  • 2
5 Comments
 
LVL 1

Expert Comment

by:nkish
ID: 38296171
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. :) )
0
 

Author Comment

by:sanjshah12
ID: 38297507
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!
0
 
LVL 1

Accepted Solution

by:
nkish earned 1600 total points
ID: 38297684
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.
0
 

Author Comment

by:sanjshah12
ID: 38297783
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!
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 38299185
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)
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question