Selecting records based on string search

• Minnu

  SSCertifiable

  Points: 6451

  More actions

  August 28, 2012 at 8:30 am

  #260703

  i've below query to display the project_name column based on the search.

  here i am passing parameters manually,

  i want to develop a stored procedure using below query and pass parameter dynamically,

  can any one help me....plz

  Eg: Catalog

  column displays records having (catalog groups1, zet catalog, business catalog)

  eg : 20*catalog

  column displays records having (2012 catalog, 2012 year passout catalog)

  declare @sstrg varchar(100) = '20*Catalog',

  --declare @strg varchar(100) = 'Catalog',

  @sstring varchar(100),

  @sstrg1 varchar(100),

  @sstrg2 varchar(100)

  begin

  set @sstring = @sstrg

  if @sstring like '%*%'

  begin

  set @sstrg1= (SELECT PARSENAME(REPLACE(@sstring, '*', '.'), 1))

  set @sstrg2= (SELECT PARSENAME(REPLACE(@sstring, '*', '.'), 2))

  ----print @strg2

  ----print @strg1

  SELECT project_name FROM groups WHERE project_name like '%'+@sstrg2+'%'+@sstrg1+'%'

  end

  else

  begin

  SELECT project_name FROM groups WHERE project_name like '%'+@sstring+'%'

  end

  end

  I WANT TO PASS LIKE

  EXEC STORED_PROC_NAME 'catalog'

  EXEC STORED_PROC_NAME '20*catalog'

  Please help me....

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  August 28, 2012 at 8:40 am

  #1530854

  The layout for a store procedure is like this:

  create procedure dbo.STORED_PROC_NAME

  (

  @sstrg varchar(100)

  )

  as

  begin

  set nocount on;

  --declare @strg varchar(100) = 'Catalog',

  declare

  @sstring varchar(100),

  @sstrg1 varchar(100),

  @sstrg2 varchar(100)

  begin

  set @sstring = @sstrg

  if @sstring like '%*%'

  begin

  set @sstrg1= (SELECT PARSENAME(REPLACE(@sstring, '*', '.'), 1))

  set @sstrg2= (SELECT PARSENAME(REPLACE(@sstring, '*', '.'), 2))

  ----print @strg2

  ----print @strg1

  SELECT project_name FROM groups WHERE project_name like '%'+@sstrg2+'%'+@sstrg1+'%'

  end

  else

  begin

  SELECT project_name FROM groups WHERE project_name like '%'+@sstring+'%'

  end

  end

  end

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 28, 2012 at 9:18 am

  #1530883

  Too complicated. Keep it simple.

  create procedure dbo.STORED_PROC_NAME

  (

  @sstrg varchar(100)

  )

  as

  begin

  set nocount on;

  --declare @strg varchar(100) = 'Catalog',

  set @sstrg = '%' + REPLACE( @sstrg, '*', '%') + '%'

  SELECT project_name

  FROM groups

  WHERE project_name like @sstrg

  end

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  August 28, 2012 at 9:48 am

  #1530913

  Luis Cazares (8/28/2012)

  ---

  Too complicated. Keep it simple.

  create procedure dbo.STORED_PROC_NAME

  (

  @sstrg varchar(100)

  )

  as

  begin

  set nocount on;

  --declare @strg varchar(100) = 'Catalog',

  set @sstrg = '%' + REPLACE( @sstrg, '*', '%') + '%'

  SELECT project_name

  FROM groups

  WHERE project_name like @sstrg

  end

  Or even simpler:

  create procedure dbo.STORED_PROC_NAME

  (

  @sstrg varchar(100)

  )

  as

  begin

  set nocount on;

  SELECT project_name

  FROM groups

  WHERE project_name like '%' + REPLACE(@sstrg, '*', '%') + '%'

  end

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]