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.
| Author |
Topic |
|
IdRatherBeProgramming
Starting Member
19 Posts |
 Posted - 2008-11-20 : 12:15:51
|
OK, I've always been told that if you want to have an optional parameter in SQL, you can use SQL as follows:CustomerNameIndex table has approx 1 million rowsCustomers table has about 600,000 rowsdeclare @PrimaryPhone varchar(20); set @PrimaryPhone = null;declare @TaxId varchar(20); set @TaxId = null;declare @Data0 varchar(20); set @Data0 = 'John';declare @Data1 varchar(20); set @Data1 = 'Michael';declare @Data2 varchar(20); set @Data2 = 'Smith';SELECT TOP 1000 * FROM Customers WHERE (C_PrimaryPhone = @PrimaryPhone or @PrimaryPhone is null) AND (C_TaxId = @TaxId or @TaxId is null) AND C_ID IN ( SELECT CNI_CustomerId FROM CustomerNameIndex WHERE CNI_NamePart = @Data0 ) AND C_ID IN ( SELECT CNI_CustomerId FROM CustomerNameIndex WHERE CNI_NamePart = @Data1 ) AND C_ID IN ( SELECT CNI_CustomerId FROM CustomerNameIndex WHERE CNI_NamePart = @Data2 ) * If all of the parameters are passed in with values - I get the results I want, VERY QUICKLY, almost non existant wait time. * But - if any of the parameters are NULL, the query takes a long time to come back (8-20 seconds), but eventually returns the correct data...I've been told that when you look for a column that can contain NULL, SQL Server can't use the index... Is there a way around this? If not, how do you do "optional" SQL parameters?PS. I'm doing these test in Query Analyzer. Might it be different in a stored procedure? I would assume Query Analyzer just executes the SQL as is, but the stored procedure might remove part of the where clause if the data is null... Any thoughts? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-20 : 12:20:53
|
why you repeat last condition>? isnt this enough?SELECT TOP 1000 * FROM Customers WHERE (C_PrimaryPhone = @PrimaryPhone or @PrimaryPhone is null) AND (C_TaxId = @TaxId or @TaxId is null) AND C_ID IN ( SELECT CNI_CustomerId FROM CustomerNameIndex WHERE CNI_NamePart IN( @Data0,@Data1,@Data2) ) |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-20 : 12:22:24
|
| also have you had a chance to see if query is ignoring index while passing null value for parameter by running query with show actual execution plan on and analysing it? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-11-20 : 13:23:00
|
| It could be the NULL or the OR (or both). Depending on your idexes, you might be able to supply an index hint. |
|
|
|
|
|
|
|
|
Misunderstanding with "C_ID = @ID OR @ID is null "