CODEWITHSUNDEEP
sqlsql-server-2008search
ThallForms
ThallForms

Reputation: 87

Optimizing search request in sql server

I am creating a search feature that takes a keyword and look in every specified columns in 3 tables for my database.

Table 1 is Utilisateurs

Table 2 is ReserveLocaux

Table 3 is called Utilisateurs_ReserveLocaux and it is the table that links Users and Reservations with their primary key.

Here is what I came up with so far 

ALTER PROCEDURE Search_ReservLocaux
-- Add the parameters for the stored procedure here
 @keyword as varchar(100),
 @noUsager as int
AS

declare @searchTerm as varchar(105) = cast('%' + @keyword  + '%' as     varchar(105))

--Select all reservations information where allowed field include search term
--where the user making the request participates in
--where additional user are part of the same reservation
select distinct  a.NoReservation,a.ApprobationProf,a.Commentaire,a.Date,a.HeureDébut,a.HeureFin,a     .NoLocal,a.NoReservation,a.PersonnesExt from ReserveLocaux a 
join Utilisateurs_ReserveLocaux b on a.NoReservation = b.NoReservation
join Utilisateurs c on c.id= b.NoUsager 
where(
     a.[Date] like @searchTerm or
     a.[HeureDébut] like @searchTerm or
     a.[HeureFin] like @searchTerm or
     a.[Commentaire] like @searchTerm or
     a.[CommentaireGardien] like @searchTerm or
     a.[ApprobationProf] like @searchTerm or
     a.[NoLocal] like @searchTerm or
     a.[Actif] like @searchTerm or
     a.[PersonnesExt] like @searchTerm or
     b.[NoUsager] like @searchTerm or
     b.[NoReservation] like @searchTerm or
     c.[NomUsager]  like @searchTerm or
     c.[PrenomUsager] like @searchTerm 
  )
  and(a.Actif is null or a.Actif = 1)

  and b.NoUsager = @noUsager 
Return
GO

exec Search_ReservLocaux 'saoindoaisn',3
go

Basically, with this search I get most of the informations I need. The problem is that I also require to look up every users that have the same reservation number found in the Utilisateurs_ReserveLocaux table which goes as is :

|Id_User|Id_Reservation|
   3         45
   4         45
   5         32

I need to access user #3(the one who is doing the search) and user #4 which is called "saoindoaisn".

I know the problem is in the last line of the where statement

b.NoUsager = @noUsager

but if I remove it, I lose security on my data because the user doing the search could access everything that he isn't participating in and if I keep it, I can't access the required data.

Can someone help me find an alternative to this line?

PS: Sorry if it is not 100% clear.

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Are you looking for this?

b.Id_Reservation in (select id_reservation 
                     from Utilisateurs_ReserveLocaux url2
                     where url2.NoUsager = @noUsager 
                    )

It is a bit hard to tell if your overall query logic could be simplified, but I think this does what you want for the outer where clause.

Upvotes: 2

Related Questions