I need help with multiple selection with SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I need help with multiple selection with SQL

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-02-09 : 12:21:28

Hi Group:

I am having a problem and I need your help. I created a GridView that uses a SQLDatasource1.
The problem is that I have a Select statement

SELECT TOP (100) PERCENT manager AS Expr1, managerName AS Expr2, title AS Expr3, facsimileTelephoneNumber AS Expr4, st AS Expr5, c AS Expr6, l AS Expr7, mobile AS Expr8, telephoneNumber AS Expr9, sn AS Expr10, givenName AS Expr11, Initials AS Expr12, displayName AS Expr13,physicalDeliveryOfficeName AS Expr14, mail AS Expr15, name AS Expr16, cn AS Expr17, ADsPath AS Expr18, zlocation AS Expr19, manager, managerName, title, facsimileTelephoneNumber, st, c, l, mobile, telephoneNumber, sn, givenName,Initials, displayName, LocCode, mail, name, cn, ADsPath, zlocation FROM ldap_data1_phonebook_view1 WHERE (sn LIKE @sn + '%') AND (givenName LIKE @givenName + '%') AND (telephoneNumber LIKE '%' + @telephoneNumber + '%') AND (physicalDeliveryOfficeName LIKE '%' + @LocCode + '%') ORDER BY Expr10, Expr11

As you can see everytime I will populate the Gridview the select statement queries the Parameter of @GivenName etc.....
What I need is a way that I can built an If statement that if the parameter is blanck for all the fields to select * all records in the Table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-09 : 12:30:32

SELECT TOP (100) PERCENT manager AS Expr1, managerName AS Expr2, title AS Expr3, facsimileTelephoneNumber AS Expr4,
st AS Expr5, c AS Expr6, l AS Expr7, mobile AS Expr8, telephoneNumber AS Expr9,
sn AS Expr10, givenName AS Expr11, Initials AS Expr12, displayName AS Expr13,
physicalDeliveryOfficeName AS Expr14, mail AS Expr15, name AS Expr16, cn AS Expr17,
ADsPath AS Expr18, zlocation AS Expr19, manager, managerName, title, facsimileTelephoneNumber, st, c, l, mobile, telephoneNumber, sn, givenName,
Initials, displayName, LocCode, mail, name, cn, ADsPath, zlocation
FROM ldap_data1_phonebook_view1
WHERE (sn LIKE @sn + '%' OR @sn IS NULL)
AND (givenName LIKE @givenName + '%' or @givenname is null)
AND (telephoneNumber LIKE '%' + @telephoneNumber + '%' or @telephoneNumber is null)
AND (physicalDeliveryOfficeName LIKE '%' + @LocCode + '%' or @LocCode is null)
ORDER BY sn, givenName

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 12:31:43

[code]
SELECT TOP (100) PERCENT manager AS Expr1,
managerName AS Expr2,
title AS Expr3,
facsimileTelephoneNumber AS Expr4,
st AS Expr5, c AS Expr6,
l AS Expr7,
mobile AS Expr8,
telephoneNumber AS Expr9,
sn AS Expr10,
givenName AS Expr11,
Initials AS Expr12,
displayName AS Expr13,physicalDeliveryOfficeName AS Expr14, mail AS Expr15, name AS Expr16, cn AS Expr17,
ADsPath AS Expr18,
zlocation AS Expr19,
manager,
managerName,
title,
facsimileTelephoneNumber,
st,
c,
l,
mobile,
telephoneNumber,
sn,
givenName,
Initials,
displayName,
LocCode,
mail,
name,
cn,
ADsPath,
zlocation
FROM ldap_data1_phonebook_view1
WHERE ((sn LIKE @sn + '%') OR @sn='')
AND ((givenName LIKE @givenName + '%') OR ISNULL(@givenName,'')='')
AND ((telephoneNumber LIKE '%' + @telephoneNumber + '%') OR ISNULL(@telephoneNumber,'')='')
AND ((physicalDeliveryOfficeName LIKE '%' + @LocCode + '%') OR ISNULL(@LocCode,'') ='')
ORDER BY Expr10, Expr11
[/code]

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-02-09 : 13:40:03

Thank You very much - You guys are amazing... I appreciate all your help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-09 : 14:44:52

I don't understand the use of ISNULL here.
If parameter already is empty space, the wildcard search will return all records.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources