searching the database sql server dynamically

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
Ali ShahSQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brad2575Commented:
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)
BuggyCoderCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ali ShahSQL DeveloperAuthor Commented:
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 ?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

BuggyCoderCommented:
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...
sachinpatil10dCommented:
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
Ali ShahSQL DeveloperAuthor Commented:
Thank you very much all it helped a lot.
regards
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.