Search stored procedure
-
Hi guys,
I'm trying to create a user interactive stored procedure, which means every time the procedure is executed, the number of parameters get changed. What I'm leaning towards is to embed all the conditions (the WHERE clause) in a variable, and run the search quesry based on the variable.
the SP is similar to what you might see on online retailer sites, you can search for a book by title, author, ISBN....
Thanks in advance.
-
There are two ways to do this. 1 is to create a single sp using dynamic SQL inside to be able to alter between them.
Ex.
CREATE PROC ip_search
@keyword varchar(10),
@opt int -- or do a varchar and supply column name when submitted.
AS
SET NOCOUNT ON
DECLARE @col varchar(20)
SELECT @col = case @opt
when 1 then 'author'
when 2 then 'ISBN'
EXECUTE ('select * from tbl_books where [' + @col + '] = ' + @keyword)
or another option which can provide a better performance due to can retain execution plans is to create several procs with a central decision proc.
Ex.
CREATE PROC ip_search;1 --I like grouping common procs but you don't have to.
-- There are also things you have to be carefull of when using grouping, but it has advanatges.
@keyword varchar(10),
@opt int -- or do a varchar and supply column name when submitted.
AS
SET NOCOUNT ON
if @opt = 1
BEGIN
EXEC ip_search;2 @keyword
END
if @opt = 2
BEGIN
EXEC ip_search;3 @keyword
END
GO
CREATE PROC ip_search;2 --By author
@keyword varchar(10)
AS
SET NOCOUNT ON
SELECT * FROM tbl_books WHERE author = @keyword
GO
CREATE PROC ip_search;3 --By ISBN
@keyword varchar(10)
AS
SET NOCOUNT ON
SELECT * FROM tbl_books WHERE ISBN = @keyword
GO
..........
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Thanks, it did help
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply