Stored Procedure Performance

Stored Procedure Performance

• gafrank

  I tried this query, but returns an error:

  CREATE PROCEDURE [dbo].[GD_SP_GET_AplicacaoByNome2]

  @prmAplic_Nome as nvarchar(100)

  AS

  DECLARE @strQuery nvarchar(100)

  DECLARE @strSQL nvarchar(500)

  SET @strQuery=''

  IF @prmAplic_Nome='' OR @prmAplic_Nome='*'

  SET @strQuery= 'AND Aplicacao LIKE '%' + @prmAplic_Nome + '%''

  SELECT AplicID, Aplicacao, NrUsers, Descontinuada, PacoteXP, Certificada, InstManual, SoShortcut, Custo

  FROM dbo.Aplicacao

  WHERE (Descontinuada = 0) "' + @strQuery + '"

  ERROR:

  Msg 102, Level 15, State 1, Procedure GD_SP_GET_AplicacaoByNome2, Line 15

  Incorrect syntax near '' + @strQuery + ''.

  
  
  

• nitesh_garg

  Pedro:

  The following query might work for you:

  SELECT AplicID,
  Aplicacao,
  NrUsers,
  Descontinuada,
  PacoteXP,
  Certificada,
  InstManual,
  SoShortcut,
  Custo
  FROM dbo.Aplicacao
  WHERE Descontinuada = 0
  AND ( @prmAplic_Nome='' OR
  @prmAplic_Nome='*' OR
  Aplicacao like '%' + @prmAplic_Nome + '%'
  )

  However, this is not going to be an "efficient" query because of the "middle search" for "Aplicacao". This means that the search will always be done by a table scan. If the "Aplicacao" table will never contain more than several hundred rows this should be sufficient. The other potential pickup would be if the number of rows that have "Descontinuado" equal to zero would be small; however, my guess is that this is not the case.

  Now, if the number of rows in this table is going to be more than say 40,000 then you might well need to worry about efficiency. In that case, you might want to look into full text indexes.

  
  Dave

  
  

• MMS2006

  HI Dave!

  It works but I dont understood the AND clause! :-)

  Thanks!!

  
  
  

• yeos_lee

  Pedro:

  In your first query you had an if condition based on the first two OR parts of that AND clause. Your query after IF condition did no elimination of records whenever either of these two conditions were true. If you remove the last OR condition you will find that this query runs the same as your first query within the IF portion. The last OR condition accounts for the situation in which you pass in a search argument. If you take out the first two OR conditions you will find that the select statement now acts like the ELSE select statement in your first post.

  
  Dave

  
  
• Stored Procedure Performance