Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

searching the database sql server dynamically

Posted on 2012-03-21
6
Medium Priority
?
157 Views
Last Modified: 2012-04-18
Hi there,

i have an asp.net website with the 3 tier, and i have connect sql server database with it. now im implementing the search crateria, i have put 3 checkboxlist controls on the page. and also have two text boxes one for searching postcode and other for any business like Mcdonalds or pizza hut etc, and a search button.
my question is that when a user enter a post code or a business name or any of them can be empty as well, and also when the user select any of the checkbox in the checkbox list , as he can select either one or two or as many as he wants, so how i will make a stored procedure in sql server depending on the checkboxes selected in the checkboxlist. like if two checkboxes selected then the stored procedure should return me the result only for them two checkboxes, if more then return for them more its depends on the checkboxlist.

Any help appreciated as i am stuck now.

Thanks
0
Comment
Question by:shah36
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 37746801
If you are doing this in a Stored Procedure you would pass all the variables to the Stored Procedure (if not do the following logic in the ASP code).

Then build out a string with your select statment, somethign like this (SQL CODE):

DIM SQLCode varchar(MAX)
-- doing 1=1 because easier when adding dynamic "AND" statements to force a single where no matter what
Set SQLCODE = "Select FieldList From Table WHERE 1=1"

if ISNULL(checkboxvariable, '') <> ''
    SET SQLCode = SQLCODE + 'AND fieldfromTable = ''' + checboxvariable + '''
   -- using 3 single quotes above for escape sequence so it forces 1 single quote to appear int he string


-- do the same for all the variable fields above

Then

EXEC(SQLCode)
0
 
LVL 20

Accepted Solution

by:
BuggyCoder earned 2000 total points
ID: 37746802
CREATE TABLE [dbo].[SalesHistory]
  (        
        @PostCode varchar(10),        
        @BusinessName varchar(10),
        @Val1 varchar(10),
        @Val2 varchar(10),
        @Val3 varchar(10),
  )
  GO              

  SET NOCOUNT ON             
  BEGIN
  DECLARE @SelectStatement NVARCHAR(2000)
  DECLARE @FullStatement NVARCHAR(4000)     
  SET @WhereStatement = '';

  SET @SelectStatement = 'SELECT * FROM SalesHistory '
  
  If(@PostCode<>null)
  BEGIN
  SET @WhereStatement = @WhereStatement + "PostCode='" + @PostCode + "' ";
  END

  If(@BusinessName<>null)
  BEGIN
  SET @WhereStatement = @WhereStatement + "AND BusinessName='" + @BusinessName + "'";
  END
  --And So On for other variables
  if(len(@WhereStatement)>0)
  BEGIN
  SET @WhereStatement = " Where " + @WhereStatement
  END

  SET @SelectStatement=@SelectStatement + @WhereStatement;
  print @SelectStatement;
  EXECUTE sp_executesql @SelectStatement;
  END

Open in new window


Basically where are generating dynamic query here:-
http://www.zdnetasia.com/generate-dynamic-sql-statements-in-sql-server-62040277.htm
0
 

Author Comment

by:shah36
ID: 37746850
Hi thanx brad and buggycoder for the reply.

is it possible i implement it on sql server side , in a stored procedure of sql server.

as i have mentioned im working on a 3 tier so i need to keep seperate the database from the business logic..so i need to implement it in sql server and then use the parameters to access it.

any help ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37746857
CREATE Procedure usp_fetchdata
        @PostCode varchar(10),        
        @BusinessName varchar(10),
        @Val1 varchar(10),
        @Val2 varchar(10),
        @Val3 varchar(10),
AS

 
  BEGIN

  SET NOCOUNT ON;

  DECLARE @SelectStatement NVARCHAR(2000)
  DECLARE @FullStatement NVARCHAR(4000)     
  SET @WhereStatement = '';

  SET @SelectStatement = 'SELECT * FROM SalesHistory '
  
  If(@PostCode<>null)
  BEGIN
  SET @WhereStatement = @WhereStatement + "PostCode='" + @PostCode + "' ";
  END

  If(@BusinessName<>null)
  BEGIN
  SET @WhereStatement = @WhereStatement + "AND BusinessName='" + @BusinessName + "'";
  END
  --And So On for other variables
  if(len(@WhereStatement)>0)
  BEGIN
  SET @WhereStatement = " Where " + @WhereStatement
  END

  SET @SelectStatement=@SelectStatement + @WhereStatement;
  print @SelectStatement;
  EXECUTE sp_executesql @SelectStatement;
  END

Open in new window


The code is for SP, make some syntax changes(if required) as i don't have SSMS here...
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37746903
Try this

--sp_selData 'demo','valutec','mercury'
CREATE Proc sp_selData
        @Val1 nvarchar(50)='',
        @Val2 nvarchar(50)='',
        @Val3 nvarchar(50)=''
as
     select * from <TableName>
     where <Column1> like '%'+ @Val1 +'%'
     or <Column2> like '%'+ @Val2 +'%'
     or <Column3> like '%'+ @Val3 +'%'
Go

Open in new window


TableName = your table name
Column1,2,3 = your filter columns
0
 

Author Closing Comment

by:shah36
ID: 37860171
Thank you very much all it helped a lot.
regards
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

730 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