July 20, 2003 at 6:38 am
Hi gurus!
I found this way to make optional parameters to a SELECT statement:
SELECT * FROM sometable WHERE (somefield = @param OR @param IS NULL)
There's only one problem. It doesn't take advantage of possible indices i.e. it does an index scan when it should do an index seek. So, is there a better way?
Thanks for any help,
/Tomi
July 21, 2003 at 12:30 am
Hi Tomi,
quote:
There's only one problem. It doesn't take advantage of possible indices i.e. it does an index scan when it should do an index seek. So, is there a better way?
if you're doing this within a stored procedure, you can specify a default value for the parameter. So the SP takes the default value if you haven't specified some other.
Take a look at BOL for 'optional parameters'
HTH
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 21, 2003 at 4:09 am
Thanks, Frank!
Yes, I know I can specify a default value to parameters in a SP. That's what I've done with my example:
CREATE PROCEDURE whatever
@param varchar(50) = NULL
AS
SELECT * FROM sometable WHERE (somefield = @param OR @param IS NULL)
But the default value doesn't help alot because there's still an OR operator in the WHERE clause and therefore it doesn't use indices.
/Tomi
July 21, 2003 at 4:22 am
quote:
Thanks, Frank!Yes, I know I can specify a default value to parameters in a SP. That's what I've done with my example:
CREATE PROCEDURE whatever
@param varchar(50) = NULL
AS
SELECT * FROM sometable WHERE (somefield = @param OR @param IS NULL)But the default value doesn't help alot because there's still an OR operator in the WHERE clause and therefore it doesn't use indices.
what about validating the input before querying?
CREATE PROCEDURE get_sales_for_title
@title varchar(80) = NULL, -- NULL default value
@ytd_sales int OUTPUT
AS
-- Validate the @title parameter.
IF @title IS NULL
BEGIN
PRINT 'ERROR: You must specify a title value.'
RETURN
END
-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title
RETURN
GO
if it's missing you can decide, whether to raise an error, assign something at least valid, so that you don't get an error, or what ever you like...
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 21, 2003 at 4:50 am
Thanks again, Frank!
quote:
...assign something at least valid, ...
Now, this is where my problem is. I can't assign anything valid to the parameter!
E.g.
DECLARE @id int
IF (@id IS NULL)
SET @id = 23
SELECT * FROM sysobjects WHERE ID = @id
Now, the code pulls out one record when I want it to pull out ALL the records. This would do the trick:
DECLARE @id int
SELECT * FROM sysobjects WHERE ID = @id OR @id IS NULL
But when you look at the execution plan it says "Clustered Index Scan" which is not good.
/Tomi
July 21, 2003 at 5:22 am
Actually a Clustered index scan is fine for all as that is what would happen. But to get the seek I would do like so
DECLARE @id int
SET @id = 23 -- COmment out to get all
IF (@id IS NULL)
SELECT * FROM sysobjects
ELSE
SELECT * FROM sysobjects WHERE ID = @id
However if this is done in an SP it may remember the execution plan of one or the other cauing a performance hit there unless you use WITH RECOMPILE in the SP.
Or better create an SP for each possiblity and create a main that decides which to choose like so.
/* Main SP is denoted by ;1 and can be called by name without the number in the group as ;1 is the default.*/
CREATE PROC ip_GetSysobjects;1
@id int = NULL -- Null is the default value.
AS
SET NOCOUNT ON
IF (@id IS NULL)
EXEC ip_GetSysobjects;2
ELSE
EXEC ip_GetSysobjects;3 @id
GO
/* This procedure covers the choice of all.*/
CREATE PROC ip_GetSysobjects;2
AS
SET NOCOUNT ON
SELECT * FROM sysobjects
GO
/* This covers if ID choice made. */
CREATE PROC ip_GetSysobjects;3
@id int -- Should no be null if get's here.
AS
SET NOCOUNT ON
SELECT * FROM sysobjects WHERE ID = @id
GO
in the above sp is named by
CREATE PROC name;groupidval
as I like goruping functions, but there are some issues you can get into if you do not learn to properly use them such as
DROP PROC ip_GetSysobjects
will drop all but
DROP PROC ip_GetSysobjects;2
will only drop the second one.
To alter the third you have to specify the groupidval
ALTER PROC ip_GetSysobjects;3
as
ALTER PROC ip_GetSysobjects
will be handled as though you did
ALTER PROC ip_GetSysobjects;1
if you prefer you can use seperate names instead. It is all a matter of preference.
July 21, 2003 at 5:45 am
Thanks, Antares!
So multiple SPs is the way to go? I thought there'd be a simpler way...
Not that it's any easier but would sp_executeSQL also be a good alternative? I mean, to build up the query dynamically. The performance would be about same, right?
/Tomi
July 21, 2003 at 6:22 am
Hi Tomi,
quote:
Not that it's any easier but would sp_executeSQL also be a good alternative? I mean, to build up the query dynamically. The performance would be about same, right?
why can't you assign the 0 as default value? It won't return any records, but also no errors.
Don't know what happens to performance, but
SELECT blabla FROM table WHERE id LIKE '%' should also return all records in that table
Executing the query dynamically with sp_executeSQL is also a way.
Maybe it would help if you post what you want to do?
Cheers,
Frank
Edited by - a5xo3z1 on 07/21/2003 06:23:00 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 21, 2003 at 6:56 am
Hi Frank!
Ok, here's what I want to do:
I have a search page on my website where people can enter lots of search conditions. They can also leave all the conditions blank. They can enter stuff like "created date", "last modified date" and more. Now, when they leave everything blank I want to pull out all the records.
So I guess the easiest way would be multiple stored procs as Antares suggested or sp_executeSQL.
/Tomi
July 21, 2003 at 7:15 am
Hi Tomi,
quote:
I have a search page on my website where people can enter lots of search conditions. They can also leave all the conditions blank. They can enter stuff like "created date", "last modified date" and more. Now, when they leave everything blank I want to pull out all the records.
Aha, now I see a bit clearer.
You want to offer the user a maximum in flexibility regarding queries. Certainly a good feature.
quote:
So I guess the easiest way would be multiple stored procs as Antares suggested or sp_executeSQL.
Multiple Stored procedures.
/Tomi
[/quote]
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply