Stores Procedure with Conditions based on a parameter

• geoff 52196

  SSC Enthusiast

  Points: 104

  More actions

  November 1, 2010 at 7:17 pm

  #228699

  I'm trying to have a condition like

  if @Type = 'TWO' then

  ColumnA LIKE N'%' + ' 20-' + N'%'

  else

  ColumnA LIKE N'%' + ' 30-' + N'%' OR ColumnA LIKE N'%' + ' 40-' + N'%'

  end if

  I'm not sure how to manage the syntax of such a thing in a stored procedure.

  This has been my approach so far.

  CREATE PROCEDURE dbo.Test

  @Type nvarchar(50) = 'TWO'

  AS

  SELECT ......

  FROM ....

  WHERE

  ColumnA LIKE

  CASE

  WHEN @Type = 'TWO' THEN

  N'%' + ' 20-' + N'%' ................ this part works

  ELSE

  ** I want this to be equiv to

  ColumnA LIKE N'%' + ' 30-' + N'%' OR ColumnA LIKE N'%' + ' 40-' + N'%'

  END

  I hope someone can assist.

  Thanks, Geoff

• Ken McKelvey

  SSCoach

  Points: 18939

  More actions

  November 2, 2010 at 10:38 am

  #1245534

  WHERE ColumnA LIKE

  CASE

  WHEN @Type = 'TWO' THEN N'%' + N' 20-' + N'%'

  ELSE N'%' + N' [3-4]0-' + N'%'

  END

• geoff 52196

  SSC Enthusiast

  Points: 104

  More actions

  November 2, 2010 at 2:32 pm

  #1245737

  Thanks so much!

• geoff 52196

  SSC Enthusiast

  Points: 104

  More actions

  November 3, 2010 at 10:51 pm

  #1246377

  How do I say something equivalent to

  .....

  WHERE

  ColumnA LIKE

  CASE

  WHEN @Type = 'TWO' THEN N'%' + ' 20-' + N'%'

  WHEN @Type = 'THREE_FOUR' THEN N'%' + N' [3-4]0-' + N'%'

  WHEN @Type = 'OTHER' THEN ....NOT (N'%' + ' 20-' + N'%') OR (N'%' + N' [3-4]0-' + N'%')

  (ie: all others except those defined by the previous two parameters)

  END

  Any assistance most appreciated.